turbot/azure_insights

Query: network_security_group_unrestricted_ingress_remote_access

Usage

powerpipe query azure_insights.query.network_security_group_unrestricted_ingress_remote_access

SQL

with unrestricted_inbound as (
select
name sg_name
from
azure_network_security_group nsg,
jsonb_array_elements(security_rules || default_security_rules) sg,
jsonb_array_elements_text(
case
when jsonb_array_length(sg -> 'properties' -> 'destinationPortRanges') > 0 then (sg -> 'properties' -> 'destinationPortRanges')
else jsonb_build_array(sg -> 'properties' -> 'destinationPortRange')
end
) as dport,
jsonb_array_elements_text(
case
when jsonb_array_length(sg -> 'properties' -> 'sourceAddressPrefixes') > 0 then (sg -> 'properties' -> 'sourceAddressPrefixes')
else jsonb_build_array(sg -> 'properties' -> 'sourceAddressPrefix')
end
) as sip
where
sg -> 'properties' ->> 'access' = 'Allow'
and sg -> 'properties' ->> 'direction' = 'Inbound'
and sg -> 'properties' ->> 'protocol' <> 'ICMP'
and sip in (
'*',
'0.0.0.0',
'0.0.0.0/0',
'Internet',
'any',
'<nw>/0',
'/0'
)
and (
dport = '*'
or (
dport like '%-%'
and (
split_part(dport, '-', 1) :: integer = 0
and split_part(dport, '-', 2) :: integer = 65535
)
)
)
and lower(nsg.id) = $1
)
select
'Unrestricted Ingress (Excludes ICMP)' as label,
count(*) as value,
case
count(*)
when 0 then 'ok'
else 'alert'
end as type
from
unrestricted_inbound

Dashboards

The query is used in the dashboards: