Query: vcn_network_security_group_restrict_ingress_ssh_all

with all_sg_security_rule as (
  select
    *
  from
    terraform_resource
  where
    type = 'oci_core_network_security_group_security_rule'
), all_sg as (
  select
    *
  from
    terraform_resource
  where
    type = 'oci_core_network_security_group'
), non_complaint as (
  select
    attributes_std ->> 'network_security_group_id' as nsg_id,
    count(*) as count
  from
    all_sg_security_rule
  where
    attributes_std ->> 'direction' = 'INGRESS'
    and attributes_std ->> 'source_type' = 'CIDR_BLOCK'
    and attributes_std ->> 'source' = '0.0.0.0/0'
    and (
      attributes_std ->> 'protocol' = 'all'
      or (
        (attributes_std -> 'tcp_options' -> 'destination_port_range' ->> 'min')::integer <= 22
        and (attributes_std -> 'tcp_options' -> 'destination_port_range' ->> 'max')::integer >= 22
      )
    )
  group by nsg_id
)
select
  a.address as resource,
  case
    when (split_part(b.nsg_id , '.', 2)) is null then 'ok'
    else 'alarm'
  end as status,
  split_part(a.address, '.', 2) || case
    when (split_part(b.nsg_id , '.', 2))  is null then ' ingress restricted for SSH from 0.0.0.0/0'
    else ' ingress rule(s) allowing SSH from 0.0.0.0/0'
  end || '.' reason
  
  , path || ':' || start_line
from
  all_sg as a
  left join non_complaint as b on a.name = (split_part(b.nsg_id , '.', 2));