Query: oci_vcn_security_group_unrestricted_ingress_rdp_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 RDP' as label,
  case count(*) when 0 then 'ok' else 'alert' end as type
from
  sg_list
where not restricted;