Control: Network load balancers having no targets attached should be deleted

powerpipe control run aws_thrifty.control.ec2_network_lb_unused

powerpipe login
powerpipe control run aws_thrifty.control.ec2_network_lb_unused --share

with target_resource as (
  select
    load_balancer_arn,
    target_health_descriptions,
    target_type
  from
    aws_ec2_target_group,
    jsonb_array_elements_text(load_balancer_arns) as load_balancer_arn
)
select
  a.arn as resource,
  case
    when jsonb_array_length(b.target_health_descriptions) = 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when jsonb_array_length(b.target_health_descriptions) = 0 then a.title || ' has no target registered.'
    else a.title || ' has registered target of type' || ' ' || b.target_type || '.'
  end as reason
  
  , a.region, a.account_id
from
  aws_ec2_network_load_balancer a
  left join target_resource b on a.arn = b.load_balancer_arn;