Control: Block storage volumes attached to stopped droplets should be reviewed

powerpipe control run digitalocean_thrifty.control.block_storage_volume_inactive_and_unused

powerpipe login
powerpipe control run digitalocean_thrifty.control.block_storage_volume_inactive_and_unused --share

select
  v.urn as resource,
  case
    when d.id is null then 'alarm'
    when d.status <> 'active' then 'alarm'
    else 'ok'
  end as status,
  case
    when d.id is null then v.title || ' unattached.'
    when d.status = 'active' then  v.title || ' associated with droplet(s) ' || v.droplet_ids
    when d.status <> 'active' then v.title || ' associated with a stopped droplet.'
    else v.title || ' in use.'
  end as reason
  , r.name as region
  
from
  digitalocean_volume as v
  left join digitalocean_droplet as d on v.droplet_ids @> ('['||d.id||']'):: jsonb
  left join digitalocean_region as r on r.slug = v.region_slug;