Query: oci_vcn_security_group_unrestricted_ingress_ssh_count

with non_compliant_rules as (
   select
     id,
     count(*) as num_noncompliant_rules
   from
     oci_core_network_security_group,
     jsonb_array_elements(rules) as r
   where
     r ->> 'direction' = 'INGRESS'
     and r ->> 'sourceType' = 'CIDR_BLOCK'
     and r ->> 'source' = '0.0.0.0/0'
     and (
     r ->> 'protocol' = 'all'
     or (
     (r -> 'tcpOptions' -> 'destinationPortRange' ->> 'min')::integer <= 22
     and (r -> 'tcpOptions' -> 'destinationPortRange' ->> 'max')::integer >= 22
     )
   )
   and lifecycle_state <> 'TERMINATED'
   group by id
 ),
 sg_list as (
   select
     nsg.id,
     case
       when non_compliant_rules.id is null then true
       else false
     end as restricted
   from
     oci_core_network_security_group as nsg
     left join non_compliant_rules on non_compliant_rules.id = nsg.id
     left join oci_identity_compartment c on c.id = nsg.compartment_id
   where
     nsg.lifecycle_state <> 'TERMINATED'
 )
 select
   count(*) as value,
   'Unrestricted Ingress SSH' as label,
   case count(*) when 0 then 'ok' else 'alert' end as type
 from
   sg_list
 where not restricted;