Control: Compute disks with low usage should be reviewed

powerpipe control run azure_thrifty.control.compute_disk_low_usage

powerpipe login
powerpipe control run azure_thrifty.control.compute_disk_low_usage --share

with disk_usage as (
  select
    name,
    resource_group
    subscription_id,
    round(avg(max)) as avg_max,
    count(max) as days
  from
    (
      select
        name,
        resource_group,
        subscription_id,
        cast(maximum as numeric) as max
      from
        azure_compute_disk_metric_read_ops_daily
      where
        date_part('day', now() - timestamp) <= 30
      union all
      select
        name,
        resource_group,
        subscription_id,
        cast(maximum as numeric) as max
      from
        azure_compute_disk_metric_write_ops_daily
      where
        date_part('day', now() - timestamp) <= 30
    ) as read_and_write_ops
  group by
    name,
    resource_group,
    subscription_id
)
select
  d.id as resource,
  case
    when avg_max <= $1 then 'alarm'
    when avg_max <= $2 then 'info'
    else 'ok'
  end as status,
  d.name || ' averaging ' || avg_max || ' read and write ops over the last ' || days / 2 || ' days.' as reason
  
  , d.resource_group
  , display_name as subscription
from
  disk_usage as u left join azure_compute_disk as d on u.name = d.name
  left join azure_subscription as sub on sub.subscription_id = d.subscription_id;