Query: compute_virtual_machine_by_attachment_to_network

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
),
vm_with_appoved_networks as (
  select
    vn.vm_id as vm_id,
    vn.network_id as network_id,
    t.title as title
  from
    vm_with_network_interfaces as vn
    left join azure_network_interface as t on t.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 not null
  )
)
select
  attachment,
  count(*)
from (
  select b.vm_id,
    case when b.vm_id is null then
      'unattached'
    else
      'attached'
    end attachment
  from
    azure_compute_virtual_machine as a
    left join vm_with_appoved_networks as b on a.id = b.vm_id) as vm
group by
  attachment
order by
  attachment;