turbot/alicloud_insights

Query: vpc_ingress_nacl_sankey

Usage

powerpipe query alicloud_insights.query.vpc_ingress_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
)
-- CIDR Nodes
select
distinct cidr_block as id,
cidr_block as title,
'cidr_block' as category,
null as from_id,
null as to_id
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
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
from aces
-- vswitch node
union select
distinct associated_id as id,
associated_id as title,
'vswitch' as category,
null as from_id,
null as to_id
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
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
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
from aces

Dashboards

The query is used in the dashboards: