turbot/aws_compliance

Query: elb_classic_lb_use_ssl_certificate

Usage

powerpipe query aws_compliance.query.elb_classic_lb_use_ssl_certificate

SQL

with detailed_classic_listeners as (
select
name
from
aws_ec2_classic_load_balancer,
jsonb_array_elements(listener_descriptions) as listener_description
where
listener_description -> 'Listener' ->> 'Protocol' in ('HTTPS', 'SSL', 'TLS')
and listener_description -> 'Listener' ->> 'SSLCertificateId' like 'arn:aws:acm%'
)
select
'arn:' || a.partition || ':elasticloadbalancing:' || a.region || ':' || a.account_id || ':loadbalancer/' || a.name as resource,
case
when a.listener_descriptions is null then 'skip'
when b.name is not null then 'alarm'
else 'ok'
end as status,
case
when a.listener_descriptions is null then a.title || ' has no listener.'
when b.name is not null then a.title || ' does not use certificates provided by ACM.'
else a.title || ' uses certificates provided by ACM.'
end as reason
, region, account_id
from
aws_ec2_classic_load_balancer as a
left join detailed_classic_listeners as b on a.name = b.name;

Controls

The query is being used by the following controls: