turbot/aws_compliance

Query: elb_application_network_lb_use_ssl_certificate

Usage

powerpipe query aws_compliance.query.elb_application_network_lb_use_ssl_certificate

SQL

with listeners_without_certificate as (
select
load_balancer_arn,
count(*) as count
from
aws_ec2_load_balancer_listener
where arn not in
( select arn from aws_ec2_load_balancer_listener, jsonb_array_elements(certificates) as c
where c ->> 'CertificateArn' like 'arn:aws:acm%' )
group by load_balancer_arn
),
all_application_network_load_balacer as (
select
arn,
account_id,
region,
title,
_ctx
from
aws_ec2_application_load_balancer
union
select
arn,
account_id,
region,
title,
_ctx
from
aws_ec2_network_load_balancer
)
select
a.arn as resource,
case
when b.load_balancer_arn is null then 'ok'
else 'alarm'
end as status,
case
when b.load_balancer_arn is null then a.title || ' uses certificates provided by ACM.'
else a.title || ' has ' || b.count || ' listeners which do not use certificates provided by ACM.'
end as reason
, a.region, a.account_id
from
all_application_network_load_balacer as a
left join listeners_without_certificate as b on a.arn = b.load_balancer_arn;

Controls

The query is being used by the following controls: