Query: ebs_volume_snapshot_exists

with volume_with_snapshots as (
  select
    volume_id,
    count(*) as snap_count
  from
    aws_ebs_snapshot
  group by
    volume_id
)
select
  v.arn as resource,
  case
    when s.volume_id is not null then 'ok'
    else 'alarm'
  end as status,
  case
    when s.volume_id is not null then v.title || ' has ' || s.snap_count || ' snapshot(s).'
    else v.title || ' does not have snapshot.'
  end as reason,
  region,
  account_id
from
  aws_ebs_volume as v
  left join volume_with_snapshots as s on s.volume_id = v.volume_id;