Detect & correct SQL DB instances with low connection count

Overview

SQL instances can be costly to run, especially if they're rarely used, instances with low average connection counts per day should be reviewed to determine if they're still required.

This query trigger detects SQL instances with low average daily connections 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

sql_db_instances_with_low_connection_count_trigger_enabled should be set to true as the default is false.
sql_db_instances_with_low_connection_count_trigger_schedule should be set to your desired running schedule
sql_db_instances_with_low_connection_count_default_action should be set to your desired action (i.e. "notify" for notifications or "delete_instance" to delete the instance).

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 SQL DB instances with low connection count

Query

with sql_db_instance_usage as (
  select
    instance_id,
    round(sum(maximum) / count(maximum)) as avg_max,
    count(maximum) as days
  from
    gcp_sql_database_instance_metric_connections_daily
  where
    date_part('day', now() - timestamp) <= 30
  group by
    instance_id
)
select
  concat(i.name, ' [', i.location, '/', i.project, ']') as title,
  i.name as instance_name,
  i.project as project,
  i._ctx ->> 'connection_name' as cred
from
  gcp_sql_database_instance as i
  left join sql_db_instance_usage as u on i.project || ':' || i.name = u.instance_id
where
  u.avg_max = 0;

Schedule

15m