Control: Compute virtual machines with low CPU utilization should be reviewed

powerpipe control run azure_thrifty.control.compute_virtual_machine_low_utilization

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

with compute_virtual_machine_utilization as (
  select
    name,
    round(cast(sum(maximum)/count(maximum) as numeric), 1) as avg_max,
    count(maximum) as days
  from
    azure_compute_virtual_machine_metric_cpu_utilization_daily
  where
    date_part('day', now() - timestamp) <=30
  group by
    name
)
select
  v.id as resource,
  case
    when avg_max is null then 'error'
    when avg_max < $1 then 'alarm'
    when avg_max < $2 then 'info'
    else 'ok'
  end as status,
  case
    when avg_max is null then 'Monitor metrics not available for ' || v.title || '.'
    else v.title || ' averaging ' || avg_max || '% max utilization over the last ' || days || ' days.'
  end as reason
  
  , v.resource_group
  , display_name as subscription
from
  azure_compute_virtual_machine as v
  left join compute_virtual_machine_utilization as u on u.name = v.name
  left join azure_subscription as sub on sub.subscription_id = v.subscription_id;