Control: SQL DB instances with a low number connections per day should be reviewed

powerpipe control run gcp_thrifty.control.sql_db_instance_low_connection_count

powerpipe login
powerpipe control run gcp_thrifty.control.sql_db_instance_low_connection_count --share

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
  i.self_link as resource,
  case
    when avg_max is null then 'error'
    when avg_max = 0 then 'alarm'
    when avg_max < $1 then 'info'
    else 'ok'
  end as status,
  case
    when avg_max is null then 'Logging metrics not available for ' || title || '.'
    when avg_max = 0 then title || ' has not been connected to in the last ' || days || ' days.'
    else title || ' is averaging ' || avg_max || ' max connections/day in the last ' || days || ' days.'
  end as reason
  
  , location, project
from
  gcp_sql_database_instance as i
  left join sql_db_instance_usage as u on i.project || ':' || i.name = u.instance_id;