Control: Backup report plan should exist in a region where backup plan is enabled

powerpipe control run aws_compliance.control.backup_report_plan_configured

powerpipe login
powerpipe control run aws_compliance.control.backup_report_plan_configured --share

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;