Query: elb_network_lb_tls_listener_security_policy_configured

with tls_listeners as (
  select
    distinct load_balancer_arn
  from
    aws_ec2_load_balancer_listener
  where
    protocol = 'TLS'
    and ssl_policy not in ('ELBSecurityPolicy-2016-08', 'ELBSecurityPolicy-FS-2018-0', 'ELBSecurityPolicy-TLS13-1-2-Ext1-2021-06', 'ELBSecurityPolicy-TLS13-1-2-2021-06')
  group by
    load_balancer_arn
), nwl_without_tls_listener as (
    select
      load_balancer_arn,
      count(*)
    from
      aws_ec2_load_balancer_listener
    where
      protocol = 'TLS'
    group by
      load_balancer_arn
)
select
  lb.arn as resource,
  case
    when l.load_balancer_arn is not null and lb.arn in (select load_balancer_arn from tls_listeners) then 'alarm'
    when l.load_balancer_arn is not null then 'ok'
    else 'info'
  end as status,
  case
    when l.load_balancer_arn is not null and lb.arn in (select load_balancer_arn from tls_listeners) then lb.title || ' TLS listener security policy not updated.'
    when l.load_balancer_arn is not null then lb.title || ' TLS listener security policy updated.'
    else lb.title || ' does not use TLS listener.'
  end as reason
  
  , lb.region, lb.account_id
from
  aws_ec2_network_load_balancer as lb
  left join nwl_without_tls_listener as l on l.load_balancer_arn = lb.arn;