turbot/oci_insights

Query: oci_vcn_security_group_by_ingress_ssh

Usage

powerpipe query oci_insights.query.oci_vcn_security_group_by_ingress_ssh

SQL

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
case
when restricted then 'restricted'
else 'unrestricted'
end as restrict_ingress_ssh_status,
count(*)
from
sg_list
group by restricted;