turbot/aws_compliance

Query: cloudtrail_multi_region_trail_enabled

Usage

powerpipe query aws_compliance.query.cloudtrail_multi_region_trail_enabled

SQL

with multi_region_trails as (
select
account_id,
count(account_id) as num_multregion_trails
from
aws_cloudtrail_trail
where
is_multi_region_trail
and region = home_region
and is_logging
group by
account_id,
is_multi_region_trail
),
organization_trails as (
select
is_organization_trail,
is_logging,
is_multi_region_trail,
account_id
from
aws_cloudtrail_trail
where
is_organization_trail
)
select
distinct a.arn as resource,
case
when coalesce(num_multregion_trails, 0) >= 1 then 'ok'
when o.is_organization_trail
and o.is_logging
and o.is_multi_region_trail then 'ok'
when o.is_organization_trail
and o.is_multi_region_trail
and o.is_logging is null then 'info'
else 'alarm'
end as status,
case
when coalesce(num_multregion_trails, 0) >= 1 then a.title || ' has ' || coalesce(num_multregion_trails, 0) || ' multi-region trail(s).'
when o.is_organization_trail
and o.is_logging
and o.is_multi_region_trail then a.title || ' has multi-region trail(s).'
when o.is_organization_trail
and o.is_multi_region_trail
and o.is_logging is null then a.title || ' has organization trail, check organization account for cloudtrail logging status.'
else a.title || ' does not have multi-region trail(s).'
end as reason,
a.account_id
from
aws_account as a
left join multi_region_trails as b on a.account_id = b.account_id
left join organization_trails as o on a.account_id = o.account_id;

Controls

The query is being used by the following controls: