Query: vcn_network_security_group_restrict_ingress_rdp_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 <= 3389
        and (
          attributes_std -> 'tcp_options' -> 'destination_port_range' ->> 'max'
        ) :: integer >= 3389
      )
    )
  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 port 3389 from 0.0.0.0/0'
    else ' ingress rule(s) allowing port 3389 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));