turbot/aws_compliance

Query: vpc_vpn_gateway_per_region_less_then_4

Usage

powerpipe query aws_compliance.query.vpc_vpn_gateway_per_region_less_then_4

SQL

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;