Query: network_security_group_unrestricted_ingress_remote_access

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
    and subscription_id = split_part($1, '/', 3)
)
select
  'Unrestricted Ingress (Excludes ICMP)' as label,
  count(*) as value,
  case count(*) when 0 then 'ok' else 'alert' end as type
from
  unrestricted_inbound