turbot/aws_compliance

Query: log_metric_filter_organization

Usage

powerpipe query aws_compliance.query.log_metric_filter_organization

SQL

with trails as (
select
trail.account_id,
trail.name as trail_name,
trail.is_logging,
split_part(trail.log_group_arn, ':', 7) as log_group_name
from
aws_cloudtrail_trail as trail,
jsonb_array_elements(trail.event_selectors) as se
where
trail.is_multi_region_trail is true
and trail.is_logging
and se ->> 'ReadWriteType' = 'All'
and trail.log_group_arn is not null
order by
trail_name
),
alarms as (
select
metric_name,
action_arn as topic_arn
from
aws_cloudwatch_alarm,
jsonb_array_elements_text(aws_cloudwatch_alarm.alarm_actions) as action_arn
order by
metric_name
),
topic_subscriptions as (
select
subscription_arn,
topic_arn
from
aws_sns_topic_subscription
order by
subscription_arn
),
metric_filters as (
select
filter.name as filter_name,
filter_pattern,
log_group_name,
metric_transformation_name
from
aws_cloudwatch_log_metric_filter as filter
where
filter.filter_pattern ~ '\s*\$\.eventSource\s*=\s*organizations.amazonaws.com.+\$\.eventName\s*=\s*"?AcceptHandshake"?.+\$\.eventName\s*=\s*"?AttachPolicy"?.+\$\.eventName\s*=\s*"?CreateAccount"?.+\$\.eventName\s*=\s*"?CreateOrganizationalUnit"?.+\$\.eventName\s*=\s*"?CreatePolicy"?.+\$\.eventName\s*=\s*"?DeclineHandshake"?.+\$\.eventName\s*=\s*"?DeleteOrganization"?.+\$\.eventName\s*=\s*"?DeleteOrganizationalUnit"?.+\$\.eventName\s*=\s*"?DeletePolicy"?.+\$\.eventName\s*=\s*"?DetachPolicy"?.+\$\.eventName\s*=\s*"?DisablePolicyType"?.+\$\.eventName\s*=\s*"?EnablePolicyType"?.+\$\.eventName\s*=\s*"?InviteAccountToOrganization"?.+\$\.eventName\s*=\s*"?LeaveOrganization"?.+\$\.eventName\s*=\s*"?MoveAccount"?.+\$\.eventName\s*=\s*"?RemoveAccountFromOrganization"?.+\$\.eventName\s*=\s*"?UpdatePolicy"?.+\$\.eventName\s*=\s*"?UpdateOrganizationalUnit"?'
order by
filter_name
),
filter_data as (
select
t.account_id,
t.trail_name,
f.filter_name
from
trails as t
join
metric_filters as f on f.log_group_name = t.log_group_name
join
alarms as alarm on alarm.metric_name = f.metric_transformation_name
join
topic_subscriptions as subscription on subscription.topic_arn = alarm.topic_arn
)
select
distinct 'arn:' || a.partition || ':::' || a.account_id as resource,
case
when f.trail_name is null then 'alarm'
else 'ok'
end as status,
case
when f.trail_name is null then 'No log metric filter and alarm exists for AWS Organizations changes.'
else filter_name || ' forwards relevant events for AWS Organizations changes.'
end as reason
, a.account_id
from
aws_account as a
left join filter_data as f on a.account_id = f.account_id;

Controls

The query is being used by the following controls: