Query: compute_vm_tcp_udp_access_restricted_internet

with network_sg as (
  select
    distinct id as sg_id,
    subscription_id,
    network_interfaces
  from
    azure_network_security_group as nsg,
    jsonb_array_elements(security_rules) as sg,
    jsonb_array_elements_text(
      sg -> 'properties' -> 'destinationPortRanges' || (sg -> 'properties' -> 'destinationPortRange') :: jsonb
    ) as dport,
    jsonb_array_elements_text(
      sg -> 'properties' -> 'sourceAddressPrefixes' || (sg -> 'properties' -> 'sourceAddressPrefix') :: jsonb
    ) as sip
  where
    sg -> 'properties' ->> 'access' = 'Allow'
    and sg -> 'properties' ->> 'direction' = 'Inbound'
    and sg -> 'properties' ->> 'protocol' in ('TCP', 'UDP')
    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 (
          (
            53 between split_part(dport, '-', 1) :: integer
            and split_part(dport, '-', 2) :: integer
            or 123 between split_part(dport, '-', 1) :: integer
            and split_part(dport, '-', 2) :: integer
            or 161 between split_part(dport, '-', 1) :: integer
            and split_part(dport, '-', 2) :: integer
            or 389 between split_part(dport, '-', 1) :: integer
            and split_part(dport, '-', 2) :: integer
            or 1900 between split_part(dport, '-', 1) :: integer
            and split_part(dport, '-', 2) :: integer
          )
          or (
            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
          )
        )
      )
    )
),
network_security_group_subnets as (
  select
    nsg.id as nsg_id,
    sub ->> 'id' as subnet_id
  from
    azure_network_security_group as nsg,
    jsonb_array_elements(nsg.subnets) as sub
  where
    nsg.id in (
      select
        sg_id
      from
        network_sg
    )
),
virtual_machines_with_access as (
  select
    nic.virtual_machine_id as virtual_machine_id
  from
    azure_network_interface as nic,
    jsonb_array_elements(nic.ip_configurations) as config
    left join network_security_group_subnets as sub on config -> 'properties' -> 'subnet' ->> 'id' = sub.subnet_id
  where
    nic.virtual_machine_id is not null
    and sub.nsg_id is not null
  union
  select
    n.virtual_machine_id as virtual_machine_id
  from
    network_sg as nsg,
    jsonb_array_elements(network_interfaces) as vm_nic
    left join azure_network_interface as n on n.id = vm_nic ->> 'id'
)
select
  vm.id as resource,
  case
    when m.virtual_machine_id is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when m.virtual_machine_id is not null then vm.title || ' restricts remote access from internet.'
    else vm.title || ' allows remote access from internet.'
  end as reason,
  vm.resource_group as resource_group,
  sub.display_name as subscription,
  vm.resource_group as resource_group,
  sub.display_name as subscription
from
  azure_compute_virtual_machine as vm
  left join virtual_machines_with_access as m on lower(m.virtual_machine_id) = lower(vm.id)
  join azure_subscription as sub on sub.subscription_id = vm.subscription_id;