turbot/oci_insights

Query: oci_vcn_security_list_by_ingress_rdp

Usage

powerpipe query oci_insights.query.oci_vcn_security_list_by_ingress_rdp

SQL

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;

Dashboards

The query is used in the dashboards: