Query: compute_virtual_machine_unattached_with_network_count

with vm_with_network_interfaces as (
  select
    vm.id as vm_id,
    n ->> 'id' as network_id
  from
    azure_compute_virtual_machine as vm,
    jsonb_array_elements(network_interfaces) as n
)
select
  count(*) as value,
  'Unattached With Network' as label,
  case when count(*) = 0 then 'ok' else 'alert' end as type
from
  vm_with_network_interfaces as vn
  left join azure_network_interface as i on i.id = vn.network_id
where exists (
  select
    ip -> 'properties' -> 'subnet' ->> 'id' as ip
  from
    azure_network_interface,
    jsonb_array_elements(ip_configurations) as ip
  where
    ip -> 'properties' -> 'subnet' ->> 'id' is null
)