Control: Boot volumes with low usage should be reviewed

powerpipe control run oci_thrifty.control.boot_volume_low_usage

powerpipe login
powerpipe control run oci_thrifty.control.boot_volume_low_usage --share

with boot_volume_usage as (
  select
    compartment_id,
    id,
    round(avg(max)) as avg_max,
    count(max) as days
  from
    (
      select
        compartment_id,
        id,
        cast(maximum as numeric) as max
      from
        oci_core_boot_volume_metric_read_ops_daily
      where
        date_part('day', now() - timestamp) <= 30
      union
      select
        compartment_id,
        id,
        cast(maximum as numeric) as max
      from
        oci_core_boot_volume_metric_write_ops_daily
      where
        date_part('day', now() - timestamp) <= 30
    ) as read_and_write_ops
  group by
    1,
    2
)
select
  b.id as resource,
  case
    when b.avg_max <= $1 then 'alarm'
    when b.avg_max <= $2 then 'info'
    else 'ok'
  end as status,
  v.display_name || ' averaging ' || b.avg_max || ' read and write ops over the last ' || b.days || ' days.' as reason,
  coalesce(c.name, 'root') as compartment,
  v.region
from
  boot_volume_usage as b
  left join oci_core_boot_volume as v on b.id = v.id
  left join oci_identity_compartment as c on c.id = b.compartment_id;