Control: VPCs subnets should exist in multiple availability zones

powerpipe control run aws_compliance.control.vpc_subnet_multi_az_enabled

powerpipe login
powerpipe control run aws_compliance.control.vpc_subnet_multi_az_enabled --share

with subnet_list as (
  select
    distinct availability_zone,
    vpc_id,
    count(*)
  from
    aws_vpc_subnet
  group by
    vpc_id, availability_zone
), zone_list as (
  select
    vpc_id,
    count(*) as num
  from
    subnet_list
  group by
    vpc_id
)
select
  arn as resource,
  case
    when l.num is null then 'alarm'
    when l.num > 1 then 'ok'
    else 'alarm'
  end as status,
  case
    when l.num is null then v.title || ' no subnet exists.'
    when l.num > 1 then v.title || ' subnets exist in ' || num || ' availability zones.'
    else v.title || ' subnet(s) exist in single availability zone.'
  end as reason
  
  , region, account_id
from
  aws_vpc as v
  left join zone_list as l on l.vpc_id = v.vpc_id;