turbot/alicloud_insights

Query: vpc_egress_nacl_sankey

Usage

powerpipe query alicloud_insights.query.vpc_egress_nacl_sankey

Steampipe Tables

SQL

with aces as (
select
title,
network_acl_id,
e ->> 'SourceCidrIp' as cidr_block,
SPLIT_PART(e ->> 'Port', '/', 2) as to_port,
SPLIT_PART(e ->> 'Port', '/', 1) as from_port,
e ->> 'Policy' as rule_action,
e ->> 'NetworkAclEntryName' as rule_name,
e ->> 'Protocol' as protocol,
case
when e ->> 'Policy' = 'accept' then 'Accept '
else 'Drop '
end || case
when e ->> 'Protocol' = 'all' then 'All Traffic'
when e ->> 'Protocol' = 'icmp' then 'All ICMP'
when e ->> 'Protocol' = 'tcp'
and (
e ->> 'Port' = '1/1'
or e ->> 'Port' = '1/65535'
) then 'All TCP'
when e ->> 'Protocol' = 'udp'
and (
e ->> 'Port' = '1/1'
or e ->> 'Port' = '1/65535'
) then 'All UDP'
when e ->> 'Protocol' = 'tcp'
and SPLIT_PART(e ->> 'Port', '/', 2) = SPLIT_PART(e ->> 'Port', '/', 1) then concat(SPLIT_PART(e ->> 'Port', '/', 2), '/TCP')
when e ->> 'Protocol' = 'udp'
and SPLIT_PART(e ->> 'Port', '/', 2) = SPLIT_PART(e ->> 'Port', '/', 1) then concat(SPLIT_PART(e ->> 'Port', '/', 2), '/UDP')
when e ->> 'Protocol' = 'tcp'
and SPLIT_PART(e ->> 'Port', '/', 2) <> SPLIT_PART(e ->> 'Port', '/', 1) then concat(
SPLIT_PART(e ->> 'Port', '/', 2),
'-',
SPLIT_PART(e ->> 'Port', '/', 1),
'/TCP'
)
when e ->> 'Protocol' = 'udp'
and SPLIT_PART(e ->> 'Port', '/', 2) <> SPLIT_PART(e ->> 'Port', '/', 1) then concat(
SPLIT_PART(e ->> 'Port', '/', 2),
'-',
SPLIT_PART(e ->> 'Port', '/', 1),
'/UDP'
)
else concat('Procotol: ', e ->> 'Protocol')
end as rule_description,
r ->> 'ResourceId' as associated_id
from
alicloud_vpc_network_acl,
jsonb_array_elements(ingress_acl_entries) as e,
jsonb_array_elements(resources) as r
where
vpc_id = $1
) -- Subnet Nodes
select
distinct associated_id as id,
associated_id as title,
'vswitch' as category,
null as from_id,
null as to_id,
0 as depth
from
aces -- ACL Nodes
union
select
distinct network_acl_id as id,
network_acl_id as title,
'nacl' as category,
null as from_id,
null as to_id,
1 as depth
from
aces -- Rule Nodes
union
select
concat(network_acl_id, '_', rule_name) as id,
rule_description as title,
'rule' as category,
null as from_id,
null as to_id,
2 as depth
from
aces -- CIDR Nodes
union
select
distinct cidr_block as id,
cidr_block as title,
'cidr_block' as category,
null as from_id,
null as to_id,
3 as depth
from
aces -- nacl -> vswitch edge
union
select
null as id,
null as title,
'attached' as category,
network_acl_id as from_id,
associated_id as to_id,
null as depth
from
aces -- rule -> NACL edge
union
select
null as id,
null as title,
rule_action as category,
concat(network_acl_id, '_', rule_name) as from_id,
network_acl_id as to_id,
null as depth
from
aces -- ip -> rule edge
union
select
null as id,
null as title,
rule_action as category,
cidr_block as from_id,
concat(network_acl_id, '_', rule_name) as to_id,
null as depth
from
aces

Dashboards

The query is used in the dashboards: