turbot/azure_insights

Query: virtual_network_nsg

Usage

powerpipe query azure_insights.query.virtual_network_nsg

SQL

with all_nsg as (
select
(s ->> 'name') as subnet_name,
(s ->> 'id') as subnet_id,
(s -> 'properties' -> 'networkSecurityGroup' ->> 'id') as nsg_id
from
azure_virtual_network,
jsonb_array_elements(subnets) as s
where
(s -> 'properties' -> 'networkSecurityGroup' -> 'id') is not null
and lower(id) = $1
)
select
nsg.name as "Name",
n.subnet_name as "Subnet Name",
provisioning_state as "Provisioning State",
lower(nsg_id) as "Network Security Group ID",
n.subnet_id as "Subnet ID"
from
all_nsg as n
left join azure_network_security_group as nsg on lower(nsg.id) = lower(n.nsg_id)

Dashboards

The query is used in the dashboards: