turbot/oci_insights

Query: vcn_gateway_sankey

Usage

powerpipe query oci_insights.query.vcn_gateway_sankey

SQL

with routes as (
select
r.id as route_table_id,
r.vcn_id,
rule ->> 'networkEntityId' as gateway,
rule ->> 'destination' as destination_cidr,
s.display_name as associated_to
from
oci_core_subnet as s
left join oci_core_route_table as r on s.route_table_id = r.id,
jsonb_array_elements(route_rules) as rule
where
s.vcn_id = $1
),
gateway as (
select
associated_to,
gateway,
destination_cidr,
case
when i.id is not null then i.display_name
when l.id is not null then l.name
when n.id is not null then n.display_name
when s.id is not null then s.display_name
end as gateway_name
from
routes as r
left join oci_core_internet_gateway as i on i.id = gateway
left join oci_core_local_peering_gateway as l on l.id = gateway
left join oci_core_nat_gateway as n on n.id = gateway
left join oci_core_service_gateway as s on s.id = gateway
)
select
null as from_id,
associated_to as id,
associated_to as title,
'route_table' as category,
0 as depth
from
gateway
union
select
associated_to as from_id,
destination_cidr as id,
destination_cidr as title,
'subnet' as category,
1 as depth
from
gateway
union
select
destination_cidr as from_id,
gateway_name as id,
gateway_name as title,
'gateway' as category,
2 as depth
from
gateway

Dashboards

The query is used in the dashboards: