Control: MySQL DB systems with a low number connections per day should be reviewed

Usage

Run the control in your terminal:

steampipe check oci_thrifty.control.mysql_db_system_low_connection_count

Snapshot and share results via Steampipe Cloud:

steampipe login
steampipe check --share oci_thrifty.control.mysql_db_system_low_connection_count

oci_identity_compartment

oci_mysql_db_system

oci_mysql_db_system_metric_connections_daily

Params

2

SQL

with mysql_db_usage as (
  select
    id,
    round(sum(maximum) / count(maximum)) as avg_max,
    count(maximum) as days
  from
    oci_mysql_db_system_metric_connections_daily
  where
    metric_name = 'CurrentConnections'
    and date_part('day', now() - timestamp) <= 30
  group by
    id
)
select
  m.id as resource,
  case
    when u.avg_max is null then 'error'
    when u.avg_max = 0 then 'alarm'
    when u.avg_max < $1 then 'info'
    else 'ok'
  end as status,
  case
    when u.avg_max is null then 'Monitoring metrics not available for ' || m.title || '.'
    when u.avg_max = 0 then m.title || ' has not been connected to in the last ' || days || ' day(s).'
    else m.title || ' is averaging ' || u.avg_max || ' max connections/day in the last ' || days || ' day(s).'
  end as reason,
  coalesce(c.name, 'root') as compartment,
  m.region
from
  oci_mysql_db_system as m
  left join mysql_db_usage as u on u.id = m.id
  left join oci_identity_compartment as c on c.id = m.compartment_id
where
  m.lifecycle_state <> 'DELETED';

Tags