Query: elb_application_network_listener_uses_secure_protocol

with lbs as (
  select
    arn,
    title,
    region,
    account_id,
    tags,
    _ctx,
    'application'::text as lb_type
  from
    aws_ec2_application_load_balancer
  union all
  select
    arn,
    title,
    region,
    account_id,
    tags,
    _ctx,
    'network'::text as lb_type
  from
    aws_ec2_network_load_balancer
), lst as (
   select
    l.arn as listener_arn,
    l.load_balancer_arn as lb_arn,
    l.protocol,
    l.port
  from
    aws_ec2_load_balancer_listener l
), joined as (
  select
    lb.arn as lb_arn,
    lb.title,
    lb.lb_type,
    lb.region,
    lb.account_id,
    lb.tags,
    lb._ctx,
    lst.listener_arn,
    lst.protocol,
    lst.port
  from
    lst join lbs lb on lb.arn = lst.lb_arn
)
select
  listener_arn as resource,
  case
    when lb_type = 'application' and protocol = 'HTTPS' then 'ok'
    when lb_type = 'network' and protocol = 'TLS'   then 'ok'
    else 'alarm'
  end as status,
  case
    when lb_type = 'application' and protocol = 'HTTPS' then title || ' listener ' || port || ' uses HTTPS.'
    when lb_type = 'network' and protocol = 'TLS' then title || ' listener ' || port || ' uses TLS.'
    when lb_type = 'application' then title || ' listener ' || port || ' uses ' || lower(protocol) || ' (expected HTTPS).'
    when lb_type = 'network' then title || ' listener ' || port || ' uses ' || lower(protocol) || ' (expected TLS).'
  end as reason
  
  , region, account_id
from
  joined;