Control: Disks attached to stopped virtual machines should be reviewed

powerpipe control run azure_thrifty.control.compute_disk_attached_stopped_virtual_machine

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

with attached_disk_with_vm as (
  select
    power_state as virtual_machine_state,
    os_disk_name,
    jsonb_agg(data_disk ->> 'name') as data_disk_names
  from
    azure_compute_virtual_machine
    left join jsonb_array_elements(data_disks) as data_disk on true
  group by name, os_disk_name, power_state
)
select
  d.id as resource,
  case
    when d.disk_state = 'Unattached' then 'skip'
    when m.virtual_machine_state = 'running' then 'ok'
    else 'alarm'
  end as status,
  case
    when d.disk_state = 'Unattached' then d.name || ' not attached to virtual machine.'
    when m.virtual_machine_state = 'running' then d.name || ' attached to running virtual machine.'
    else d.name || ' not attached to running virtual machine.'
  end as reason
  
  , d.resource_group
  , display_name as subscription
from
  azure_compute_disk as d
  left join attached_disk_with_vm as m on (d.name = m.os_disk_name or m.data_disk_names ?| array[d.name])
  left join azure_subscription as sub on sub.subscription_id = d.subscription_id;