turbot/azure_insights

Query: compute_virtual_machine_unattached_with_network_count

Usage

powerpipe query azure_insights.query.compute_virtual_machine_unattached_with_network_count

SQL

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
)

Dashboards

The query is used in the dashboards: