Query: network_network_peering_connected

with disconnected_network_peering as (
  select
    distinct id as vn_id
  from
    azure_virtual_network as n,
    jsonb_array_elements(network_peerings) as p
  where
    p -> 'properties' ->> 'peeringState' = 'Disconnected'
)
select
  n.id as resource,
  case
    when jsonb_array_length(network_peerings) = 0 then 'ok'
    when p.vn_id is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when jsonb_array_length(network_peerings) = 0 then n.title || ' has no network peering.'
    when p.vn_id is not null then n.title || ' has network peering in disconnected state.'
    else n.title || ' has network peering in connected state.'
  end as reason
  
  , n.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_virtual_network as n
  left join disconnected_network_peering as p on p.vn_id = n.id
  join azure_subscription sub on sub.subscription_id = n.subscription_id;