Control: Block and Boot volumes attached to stopped instances should be reviewed

powerpipe control run oci_thrifty.control.boot_and_block_volume_attached_stopped_instance

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

-- Listing core boot volumes and block volumes associated with running instances
with vols_with_instances as (
  select
    v.instance_id,
    v.volume_id as volume_id
  from
    oci_core_volume_attachment as v
    inner join oci_core_instance as i on i.id = v.instance_id
  where i.lifecycle_state = 'RUNNING'
  union
  select
    b.instance_id,
    b.boot_volume_id as volume_id
  from
    oci_core_boot_volume_attachment as b
    inner join oci_core_instance as i on i.id = b.instance_id
  where i.lifecycle_state = 'RUNNING'
),
-- Listing all volumes of both boot volumes and block volumes
all_volumes as (
  select
    id,
    compartment_id,
    _ctx,
    tenant_id,
    region,
    display_name
  from
    oci_core_volume
  union
  select
    id,
    compartment_id,
    _ctx,
    tenant_id,
    region,
    display_name
  from
    oci_core_boot_volume
)
-- Listing the volumes based on associations
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 || ' not associated with running instance.'
    else a.display_name || ' associated with running 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;