turbot/aws_compliance

Query: dynamodb_table_in_backup_plan

Usage

powerpipe query aws_compliance.query.dynamodb_table_in_backup_plan

SQL

with mapped_with_id as (
select
jsonb_agg(elems) as mapped_ids
from
aws_backup_selection,
jsonb_array_elements(resources) as elems
group by
backup_plan_id
),
mapped_with_tags as (
select
jsonb_agg(elems ->> 'ConditionKey') as mapped_tags
from
aws_backup_selection,
jsonb_array_elements(list_of_tags) as elems
group by
backup_plan_id
),
backed_up_table as (
select
t.name
from
aws_dynamodb_table as t
join mapped_with_id as m on m.mapped_ids ?| array [ t.arn ]
union
select
t.name
from
aws_dynamodb_table as t
join mapped_with_tags as m on m.mapped_tags ?| array(
select
jsonb_object_keys(tags)
)
)
select
t.arn as resource,
case
when b.name is null then 'alarm'
else 'ok'
end as status,
case
when b.name is null then t.title || ' not in backup plan.'
else t.title || ' in backup plan.'
end as reason,
t.region,
t.account_id
from
aws_dynamodb_table as t
left join backed_up_table as b on t.name = b.name;

Controls

The query is being used by the following controls: