turbot/aws_compliance

Query: elb_classic_lb_with_inbound_rule

Usage

powerpipe query aws_compliance.query.elb_classic_lb_with_inbound_rule

SQL

with sg_with_inbound as (
select
arn,
sg
from
aws_ec2_classic_load_balancer,
jsonb_array_elements_text(security_groups) as sg
left join aws_vpc_security_group_rule as sgr on sg = sgr.group_id
where
sgr.type = 'ingress'
group by
sg, arn
), classic_lb_without_inbound as (
select
distinct arn
from
aws_ec2_classic_load_balancer,
jsonb_array_elements_text(security_groups) as s
where
s not in ( select sg from sg_with_inbound)
)
select
distinct c.arn as resource,
case
when c.security_groups is null then 'alarm'
when i.arn is not null then 'alarm'
else 'ok'
end as status,
case
when c.security_groups is null then c.title || ' does not have security group attached.'
when i.arn is not null then c.title || ' all attached security groups do not have inbound rule(s).'
else c.title || ' all attached security groups have inbound rule(s).'
end as reason
, c.region, c.account_id
from
aws_ec2_classic_load_balancer as c
left join classic_lb_without_inbound as i on c.arn = i.arn;

Controls

The query is being used by the following controls: