turbot/azure_compliance

Query: network_network_peering_connected

Usage

powerpipe query azure_compliance.query.network_network_peering_connected

SQL

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;

Controls

The query is being used by the following controls: