Query: vpc_vpn_gateway_per_region_less_then_4

with vpn_gateway_per_region as (
  select
    count(*),
    region,
    account_id
  from
    aws_vpc_vpn_gateway
  group by
    region,
    account_id
)
select
  'arn:' || r.partition || '::' || r.region || ':' || r.account_id as resource,
  case
    when v.count > 3 then 'alarm'
    else 'ok'
  end as status,
  r.region || ' region has ' || coalesce(v.count, 0) || ' VPN gateway(s).' as reason,
  r.region,
  r.account_id
from
  aws_region as r
  left join vpn_gateway_per_region as v on r.account_id = v.account_id
  and r.region = v.region;