Query: network_security_rule_ssh_access_restricted

with nsg_rule as (
  select
    distinct address as name
  from
    terraform_resource,
    jsonb_array_elements_text(
      case
        when ((attributes_std -> 'destination_port_ranges') != 'null') and jsonb_array_length(attributes_std -> 'destination_port_ranges') > 0 then (attributes_std -> 'destination_port_ranges')
        else jsonb_build_array(attributes_std -> 'destination_port_range')
      end ) as dport,
    jsonb_array_elements_text(
      case
        when ((attributes_std -> 'source_address_prefixes') != 'null') and jsonb_array_length(attributes_std -> 'source_address_prefixes') > 0 then (attributes_std -> 'source_address_prefixes')
        else jsonb_build_array(attributes_std -> 'source_address_prefix')
      end) as sip
  where
    type = 'azurerm_network_security_rule'
    and lower(attributes_std ->> 'access') = 'allow'
    and lower(attributes_std ->> 'direction') = 'inbound'
    and (lower(attributes_std ->> 'protocol') ilike 'TCP' or lower(attributes_std ->> 'protocol') = '*')
    and lower(sip) in ('*', '0.0.0.0', '0.0.0.0/0', 'internet', 'any', '<nw>/0', '/0')
    and (
      dport in ('22', '*')
      or (
        dport like '%-%'
        and split_part(dport, '-', 1) :: integer <= 22
        and split_part(dport, '-', 2) :: integer >= 22
      )
    )
)
select
  r.address as resource,
  case
    when rule.name is null then 'ok'
    else 'alarm'
  end as status,
  split_part(r.address, '.', 2) || case
    when rule.name is null then ' restricts SSH access from internet'
    else ' allows SSH access from internet'
  end || '.' reason
  , path || ':' || start_line
from
  terraform_resource as r
  left join nsg_rule as rule on rule.name = r.name
where
  type = 'azurerm_network_security_rule';