Detect & correct SQL DB instances with low cpu utilization

Overview

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

This pipeline detects SQL instances with low average daily CPU utilization 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_cpu_utilization_trigger_enabled should be set to true as the default is false.
sql_db_instances_with_low_cpu_utilization_trigger_schedule should be set to your desired running schedule
sql_db_instances_with_low_cpu_utilization_default_action should be set to your desired action (i.e. "notify" for notifications or "delete_instance" to delete the instance or "stop_sql_instance" to stop 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 cpu utilization

Query

with sql_db_instance_usage as (
  select
    instance_id,
    round(cast(sum(maximum) / count(maximum) as numeric), 1) as avg_max,
    count(maximum) as days
  from
    gcp_sql_database_instance_metric_cpu_utilization_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
  avg_max <= 25;

Schedule

15m