Query: virtual_network_nsg

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
  and subscription_id = split_part($1, '/', 3)
)
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)