turbot/alicloud_insights

Query: ecs_security_group_egress_rule_sankey

Usage

powerpipe query alicloud_insights.query.ecs_security_group_egress_rule_sankey

SQL

with associations as (
select
title,
arn,
sg as security_group_id
from
alicloud_ecs_instance,
jsonb_array_elements_text(security_group_ids) as sg
where
sg = $1
),
rules as (
select
concat(text(p ->> 'DestCidrIp'), text(p ->> 'Ipv6DestCidrIp'), text(p ->> 'DestGroupId'),text(p ->> 'DestPrefixListId')) as "source",
case
when p ->> 'IpProtocol' = 'ALL' then 'All Traffic'
when p ->> 'IpProtocol' = 'ICMP' then 'All ICMP'
when p ->> 'PortRange' = '-1/-1' or p ->> 'PortRange' = '1/65535' then concat('All ',p ->> 'IpProtocol')
when SPLIT_PART(p ->> 'PortRange','/',2) = SPLIT_PART(p ->> 'PortRange','/',1)
then concat(SPLIT_PART(p ->> 'PortRange','/',2),'/',p ->> 'IpProtocol')
else concat(
SPLIT_PART(p ->> 'PortRange','/',1),
'-',
SPLIT_PART(p ->> 'PortRange','/',2),
'/',
p ->> 'IpProtocol'
)
end as port_proto,
case
when ( text(p ->> 'DestCidrIp') = '0.0.0.0/0' or text(p ->> 'Ipv6DestCidrIp') = '::/0')
and p ->> 'IpProtocol' <> 'ICMP'
and (
p ->> 'PortRange' = '-1/-1'
or p ->> 'PortRange' = '1/65535'
) then 'alert'
else 'ok'
end as category,
security_group_id
from
alicloud_ecs_security_group,
jsonb_array_elements(permissions) as p
where
security_group_id = $1
and p ->> 'Direction' = 'egress'
)
-- Nodes ---------
select
distinct concat('src_',source) as id,
source as title,
3 as depth,
'source' as category,
null as from_id,
null as to_id
from
rules
union
select
distinct port_proto as id,
port_proto as title,
2 as depth,
'port_proto' as category,
null as from_id,
null as to_id
from
rules
union
select
distinct sg.security_group_id as id,
name as title,
1 as depth,
'security_group' as category,
null as from_id,
null as to_id
from
alicloud_ecs_security_group sg
inner join rules sgr on sg.security_group_id = sgr.security_group_id
union
select
distinct arn as id,
title as title,
0 as depth,
title,
security_group_id as from_id,
null as to_id
from
associations
-- Edges ---------
union select
null as id,
null as title,
null as depth,
category,
concat('src_',source) as from_id,
port_proto as to_id
from
rules
union select
null as id,
null as title,
null as depth,
category,
port_proto as from_id,
security_group_id as to_id
from
rules

Dashboards

The query is used in the dashboards: