turbot/oci_insights

Query: vcn_nsl_egress_rule_sankey

Usage

powerpipe query oci_insights.query.vcn_nsl_egress_rule_sankey

SQL

with subnets as (
select
display_name as subnet_name,
id as subnet_id,
sl as sl_id
from
oci_core_subnet as s,
jsonb_array_elements_text(security_list_ids) as sl
where
vcn_id = $1
),
securityList as (
select
subnet_name,
subnet_id,
display_name as sl_name,
l.id as sl_id,
egress_security_rules as rules
from
oci_core_security_list as l
left join subnets as s on s.sl_id = l.id
where
vcn_id = $1
),
rule as (
select
subnet_name,
subnet_id,
sl_id,
sl_name,
r ->> 'source' as source,
case
when r ->> 'protocol' = 'all' then 'Allow All Traffic'
when r ->> 'protocol' = '6'
and r ->> 'tcpOptions' is null then 'Allow All TCP'
when r ->> 'protocol' = '17'
and r ->> 'udpOptions' is null then 'Allow All UDP'
when r ->> 'protocol' = '1'
and r ->> 'icmpOptions' is null then 'Allow All ICMP'
when r ->> 'protocol' = '6'
and (
r -> 'tcpOptions' -> 'sourcePortRange' ->> 'min' = '1'
or r -> 'tcpOptions' -> 'sourcePortRange' ->> 'min' is null
)
and (
r -> 'tcpOptions' -> 'destinationPortRange' ->> 'max' = '65535'
or r -> 'tcpOptions' -> 'destinationPortRange' ->> 'max' is null
) then 'Allow All TCP'
when r ->> 'protocol' = '17'
and (
r -> 'udpOptions' -> 'sourcePortRange' ->> 'min' = '1'
or r -> 'udpOptions' -> 'sourcePortRange' ->> 'min' is null
)
and (
r -> 'udpOptions' -> 'destinationPortRange' ->> 'max' = '65535'
or r -> 'udpOptions' -> 'destinationPortRange' ->> 'max' is null
) then 'Allow All UDP'
when r ->> 'protocol' = '1'
and r -> 'icmpOptions' ->> 'code' is not null
and r -> 'icmpOptions' ->> 'type' is not null then concat(
'ICMP Type ',
r -> 'icmpOptions' ->> 'type',
', Code ',
r -> 'icmpOptions' ->> 'code'
)
when r ->> 'protocol' = '1'
and r -> 'icmpOptions' ->> 'code' is not null then concat('ICMP Code ', r -> 'icmpOptions' ->> 'code')
when r ->> 'protocol' = '1'
and r -> 'icmpOptions' ->> 'type' is not null then concat('ICMP Type ', r -> 'icmpOptions' ->> 'type')
when r ->> 'protocol' = '6'
and r -> 'tcpOptions' -> 'sourcePortRange' ->> 'min' = r -> 'tcpOptions' -> 'destinationPortRange' ->> 'max' then concat(
r -> 'tcpOptions' -> 'sourcePortRange' ->> 'min',
'/TCP'
)
when r ->> 'protocol' = '17'
and r -> 'udpOptions' -> 'sourcePortRange' ->> 'min' = r -> 'udpOptions' -> 'destinationPortRange' ->> 'max' then concat(
r -> 'udpOptions' -> 'sourcePortRange' ->> 'min',
'/UDP'
)
when r ->> 'protocol' = '6'
and COALESCE(r -> 'tcpOptions' -> 'sourcePortRange' ->> 'min', '1') <> COALESCE(
r -> 'tcpOptions' -> 'destinationPortRange' ->> 'max',
'65535'
) then concat(
COALESCE(r -> 'tcpOptions' -> 'sourcePortRange' ->> 'min', '1'),
'-',
COALESCE(
r -> 'tcpOptions' -> 'destinationPortRange' ->> 'max',
'65535'
),
'/TCP'
)
when r ->> 'protocol' = '17'
and COALESCE(r -> 'udpOptions' -> 'sourcePortRange' ->> 'min', '1') <> COALESCE(
r -> 'udpOptions' -> 'destinationPortRange' ->> 'max',
'65535'
) then concat(
COALESCE(r -> 'udpOptions' -> 'sourcePortRange' ->> 'min', '1'),
'-',
COALESCE(
r -> 'udpOptions' -> 'destinationPortRange' ->> 'max',
'65535'
),
'/UDP'
)
else concat('Protocol: ', r ->> 'protocol')
end as rule_description
from
securityList,
jsonb_array_elements(rules) as r
) -- Subnet node
select
distinct subnet_name as id,
subnet_name as title,
'subnet' as category,
null as from_id,
null as to_id,
0 as depth
from
rule -- SL Nodes
union
select
distinct sl_name as id,
sl_name as title,
'sl' as category,
null as from_id,
null as to_id,
1 as depth
from
rule -- Rule Nodes
union
select
distinct rule_description as id,
rule_description as title,
'rule' as category,
null as from_id,
null as to_id,
2 as depth
from
rule -- CIDR Nodes
union
select
distinct source as id,
source as title,
'source' as category,
null as from_id,
null as to_id,
3 as depth
from
rule -- sl -> subnet edge
union
select
null as id,
null as title,
'attached' as category,
sl_name as from_id,
subnet_name as to_id,
null as depth
from
rule -- rule -> SL edge
union
select
null as id,
null as title,
'rule' as category,
rule_description as from_id,
sl_name as to_id,
null as depth
from
rule -- ip -> rule edge
union
select
null as id,
null as title,
'rule' as category,
source as from_id,
rule_description as to_id,
null as depth
from
rule

Dashboards

The query is used in the dashboards: