Query: backup_report_plan_configured

with backup_plan_configured_regions as (
  select
    distinct region,
    account_id
  from
    aws_backup_plan
  group by
    region,
    account_id
),
backup_report_plan_configured as (
  select
    distinct region,
    account_id
  from
    aws_backup_report_plan
  group by
    region,
    account_id
)
select
  'arn:' || r.partition || '::' || r.region || ':' || r.account_id as resource,
  case
    when cp.region is not null
    and rp.region is not null then 'ok'
    when cp.region is not null
    and rp.region is null then 'alarm'
    else 'info'
  end as status,
  case
    when cp.region is not null
    and rp.region is not null then 'Backup report plan(s) exist in region ' || r.region || '.'
    when cp.region is not null
    and rp.region is null then 'No backup report plan(s) exist in region ' || r.region || '.'
    else 'No backup plan(s) configured in region ' || r.region || '.'
  end as reason,
  r.region,
  r.account_id
from
  aws_region as r
  left join backup_plan_configured_regions as cp on r.account_id = cp.account_id
  and r.region = cp.region
  left join backup_report_plan_configured as rp on r.account_id = rp.account_id
  and r.region = rp.region;