Query: oci_vcn_security_list_by_ingress_rdp

with non_compliant_rules as (
  select
    id,
    count(*) as num_noncompliant_rules
  from
    oci_core_security_list,
    jsonb_array_elements(ingress_security_rules) as p
  where
    p ->> 'source' = '0.0.0.0/0'
    and (
    (
      p ->> 'protocol' = 'all'
      and (p -> 'tcpOptions' -> 'destinationPortRange' -> 'min') is null
    )
    or (
      p ->> 'protocol' = '6' and
      (p -> 'tcpOptions' -> 'destinationPortRange' ->> 'min')::integer <= 3389
      and (p -> 'tcpOptions' -> 'destinationPortRange' ->> 'max')::integer >= 3389
      )
    )
    and lifecycle_state <> 'TERMINATED'
    group by id
),
sl_list as (
  select
    sl.id,
    case
      when non_compliant_rules.id is null then true
      else false
    end as restricted
  from
    oci_core_security_list as sl
    left join non_compliant_rules on non_compliant_rules.id = sl.id
    left join oci_identity_compartment c on c.id = sl.compartment_id
  where
    sl.lifecycle_state <> 'TERMINATED'
)
select
  case
    when restricted then 'restricted'
    else 'unrestricted'
  end as restrict_ingress_rdp_status,
  count(*)
from
  sl_list
group by restricted;