Control: RDS snapshots without source DB instances should be reviewed

powerpipe control run aws_thrifty.control.rds_unused_snapshots

powerpipe login
powerpipe control run aws_thrifty.control.rds_unused_snapshots --share

with snapshot_age as (
  select
    db_snapshot_identifier,
    date_part('day', now() - create_time) as age_in_days,
    db_instance_identifier,
    create_time,
    status
  from
    aws_rds_db_snapshot
  where
    status = 'available'
),
instance_exists as (
  select
    db_instance_identifier
  from
    aws_rds_db_instance
)
select
  s.arn as resource,
  case
    when s.status != 'available' then 'skip'
    when i.db_instance_identifier is null
    and a.age_in_days > $1 then 'alarm'
    when i.db_instance_identifier is null then 'info'
    else 'ok'
  end as status,
  case
    when s.status != 'available' then s.title || ' is in ' || s.status || ' status.'
    when i.db_instance_identifier is null
    and a.age_in_days > $1 then s.title || ' is ' || a.age_in_days || ' days old and its source DB instance no longer exists.'
    when i.db_instance_identifier is null then s.title || ' source DB instance no longer exists but snapshot is only ' || a.age_in_days || ' days old.'
    else s.title || ' has an existing source DB instance.'
  end as reason,
  region,
  account_id
from
  aws_rds_db_snapshot s
  left join snapshot_age a on a.db_snapshot_identifier = s.db_snapshot_identifier
  left join instance_exists i on i.db_instance_identifier = a.db_instance_identifier;