Query: compute_virtual_machine_by_remote_access

with network_sg as (
  select
    distinct name as sg_name,
    network_interfaces
  from
    azure_network_security_group as nsg,
    jsonb_array_elements(security_rules) as 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' in ('TCP', '*')
    and sip in (
      '*',
      '0.0.0.0',
      '0.0.0.0/0',
      'Internet',
      'any',
      '<nw>/0',
      '/0'
    )
    and (
      dport in ('22', '3389', '*')
      or (
        dport like '%-%'
        and (
          (
            split_part(dport, '-', 1) :: integer <= 3389
            and split_part(dport, '-', 2) :: integer >= 3389
          )
          or (
            split_part(dport, '-', 1) :: integer <= 22
            and split_part(dport, '-', 2) :: integer >= 22
          )
        )
      )
    )
)
select
  status,
  count(*)
from
  (
    select
      sg.sg_name,
      case
        when sg.sg_name is null then 'restricted'
        else 'unrestricted'
      end status
    from
      azure_compute_virtual_machine as vm
      left join network_sg as sg on vm.network_interfaces @> sg.network_interfaces
  ) as vm
group by
  status
order by
  status;