Query: compute_vm_data_and_os_disk_uses_managed_disk

with data_disk_with_no_managed_disk as (
  select
    id as vm_id,
    count(*) as count
  from
    azure_compute_virtual_machine,
    jsonb_array_elements(data_disks) as d
  where
    d -> 'managedDisk' ->> 'id' is null
  group by
    id
)
select
  vm.id as resource,
  case
    when managed_disk_id is null and d.count > 0 then 'alarm'
    when managed_disk_id is null then 'alarm'
    when d.count > 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when managed_disk_id is null and d.count > 0 then vm.name || ' not utilizing managed disks for both data and OS disk.'
    when managed_disk_id is null then vm.name || ' not utilizing managed disks for OS disk.'
    when d.count > 0 then vm.name || ' not utilizing managed disks for data disk.'
    else vm.name || ' utilizing managed disks for both data and OS disk.'
  end as reason
  
  , vm.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_compute_virtual_machine as vm
  left join data_disk_with_no_managed_disk as d on d.vm_id = vm.id,
  azure_subscription as sub
where
  sub.subscription_id = vm.subscription_id;