Loading controls...
Control: Block and Boot volumes attached to stopped instances should be reviewed
Description
Instances that are stopped may no longer need any volumes attached.
Usage
Run the control in your terminal:
steampipe check oci_thrifty.control.boot_and_block_volume_attached_stopped_instance
Snapshot and share results via Steampipe Cloud:
steampipe loginsteampipe check --share oci_thrifty.control.boot_and_block_volume_attached_stopped_instance
Plugins & Tables
SQL
-- Listing core boot volumes and block volumes associated with running instanceswith 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 volumesall_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 associationsselect 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.regionfrom 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;