turbot/aws_compliance

Query: dlm_ebs_snapshot_lifecycle_policy_enabled

Usage

powerpipe query aws_compliance.query.dlm_ebs_snapshot_lifecycle_policy_enabled

SQL

with region_with_ebs_snapshots as(
select
distinct region,
partition,
account_id,
_ctx
from
aws_ebs_snapshot
), dlm_ebs_lifecycle_policy as (
select
region,
account_id,
count(*)
from
aws_dlm_lifecycle_policy
where
policy_details ->> 'PolicyType' like 'EBS_SNAPSHOT%'
group by
region,
account_id
)
select
'arn:' || r.partition || '::' || r.region || ':' || r.account_id as resource,
case
when p.region is not null then 'ok'
else 'alarm'
end as status,
case
when p.region is not null then 'EBS snapshot DLM policy exist in region ' || r.region || '.'
else 'EBS snapshots DLM policy does not exist in region ' || r.region || '.'
end as reason
, r.region, r.account_id
from
region_with_ebs_snapshots as r
left join dlm_ebs_lifecycle_policy as p on p.region = r.region and r.account_id = p.account_id;

Controls

The query is being used by the following controls: