Detect & correct VPC NAT gateways if unused

Overview

NAT gateways are charged per hour once they are provisioned and available, so unused gateways should be deleted to prevent costs.

This query trigger detects unused NAT gateways and then either sends a notification or attempts to perform a predefined corrective action.

Getting Started

By default, this trigger is disabled, however it can be configured by setting the below variables

vpc_nat_gateways_if_unused_trigger_enabled should be set to true as the default is false.
vpc_nat_gateways_if_unused_trigger_schedule should be set to your desired running schedule
vpc_nat_gateways_if_unused_default_action should be set to your desired action (i.e. "notify" for notifications or "delete" to delete the resource).

Then starting the server:

flowpipe server

or if you've set the variables in a .fpvars file:

flowpipe server --var-file=/path/to/your.fpvars

Pipeline

Correct VPC NAT gateways if unused

Query

select
  concat(
    nat.nat_gateway_id,
    ' [',
    nat.region,
    '/',
    nat.account_id,
    ']'
  ) as title,
  nat.nat_gateway_id,
  nat.region,
  nat._ctx ->> 'connection_name' as cred
from
  aws_vpc_nat_gateway as nat
  left join aws_vpc_nat_gateway_metric_bytes_out_to_destination as dest on nat.nat_gateway_id = dest.nat_gateway_id
where
  nat.state = 'available'
group by
  nat.nat_gateway_id,
  nat.region,
  nat.account_id,
  nat._ctx ->> 'connection_name'
having
  sum(coalesce(dest.average, 0)) = 0;

Schedule

15m