Control: Block and Boot volumes not attached any instances should be reviewed

powerpipe control run oci_thrifty.control.boot_and_block_volume_unattached

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

with vols_with_instances as (
  select
    v.volume_id as volume_id
  from
    oci_core_volume_attachment as v
  union
  select
    b.boot_volume_id as volume_id
  from
    oci_core_boot_volume_attachment as b
),
-- Listing all volumes of type boot and block volumes
all_volumes as (
  select
    id,
    compartment_id,
    _ctx,
    tenant_id,
    region,
    display_name,
    size_in_gbs
  from
    oci_core_volume
  union
  select
    id,
    compartment_id,
    _ctx,
    tenant_id,
    region,
    display_name,
    size_in_gbs
  from
    oci_core_boot_volume
)
-- Listing the volumes based on attachment
select
  a.id as resource,
  case
    when v.volume_id is null then 'alarm'
    else 'ok'
  end as status,
  case
    when v.volume_id is null then a.display_name || ' of size ' || a.size_in_gbs || 'gb not attached.'
    else a.display_name || ' of size ' || a.size_in_gbs || 'gb attached to instance.'
  end as reason,
  coalesce(c.name, 'root') as compartment
  
  , a.region
from
  all_volumes as a
  left join vols_with_instances as v on v.volume_id = a.id
  left join oci_identity_compartment as c on c.id = a.compartment_id;