Control: Unused NAT gateways should be deleted

Usage

steampipe check alicloud_thrifty.control.vpc_nat_gateway_unused

alicloud_ecs_instance

alicloud_vpc_nat_gateway

SQL

with instance_data as (
  select
    instance_id,
    vpc_attributes ->> 'VSwitchId' as vswitch_id,
    status
  from
    alicloud_ecs_instance
)
select
  -- Required Columns
  'acs:vpc:' || nat.region || ':' || nat.account_id || ':natgateway/' || nat_gateway_id as resource,
  case
    when nat.status <> 'Available' then 'alarm'
    when i.vswitch_id is null then 'alarm'
    when i.status <> 'Running' then 'alarm'
    else 'ok'
  end as status,
  case
    when nat.status <> 'Available' then nat.title || ' in ' || nat.status || ' state.'
    when i.vswitch_id is null then nat.title || ' not in-use.'
    when i.status <> 'Running' then nat.title || ' associated with ' || i.instance_id || ', which is in ' || lower(i.status) || ' state.'
    else nat.title || ' in-use.'
  end as reason,
  -- Additional Dimensions
  nat.region,
  nat.account_id
from
  alicloud_vpc_nat_gateway as nat
  left join instance_data as i on nat_gateway_private_info ->> 'VswitchId' = i.vswitch_id;

Tags