Query: blockstorage_block_volume_by_compartment

with compartments as (
  select
    id,
    'root [' || title || ']' as title
  from
    oci_identity_tenancy
  union (
  select
    c.id,
    c.title || ' [' || t.title || ']' as title
  from
    oci_identity_compartment c,
    oci_identity_tenancy t
  where
    c.tenant_id = t.id and c.lifecycle_state = 'ACTIVE'
  )
)
select
  c.title as "Title",
  count(v.*) as "Volumes"
from
  oci_core_volume as v,
  compartments as c
where
  c.id = v.compartment_id and v.lifecycle_state <> 'TERMINATED'
group by
  c.title
order by
  c.title;