Query: network_security_group_unrestricted_ingress

  with network_sg as (
    select
      distinct name sg_name
    from
      azure_network_security_group nsg,
      jsonb_array_elements(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
          )
        )
      )
  )
  select
    case when name in (select sg_name from network_sg ) then 'unrestricted' else 'restricted' end as status,
    count(*)
from
  azure_network_security_group
  group by
    status
  order by
    status;