An alert about an unexpectedly open security group led to a simple question: who opened it, and when? The change had happened about three months earlier in one of our 14 AWS accounts. That account had a trail writing to a bucket in the same account, with a lifecycle rule deleting logs after 90 days. The event we needed had been deleted a week before. Two other accounts had no trail at all, relying on the 90-day event history in the CloudTrail console.

This post describes the setup we moved to: one organization trail, a dedicated log archive account, and Athena for fast queries during investigations.

The target architecture

AWS Organization
  management account  -- creates the organization trail
  member accounts     -- all API activity captured automatically
        |
        v
  log archive account
    S3 bucket: org-cloudtrail-logs (object lock, versioning, KMS)
    KMS key for CloudTrail
  security tooling account
    Athena workgroup + Glue table over the log bucket (read-only)

Key properties:

  • One trail for all accounts, including accounts created later.
  • Logs stored outside the accounts that generate them, so an attacker with admin access to a workload account cannot delete the evidence.
  • Read access for investigations from a separate security account.

Step 1: the log archive bucket

In the log archive account, the bucket is protected against deletion and tampering:

resource "aws_s3_bucket" "cloudtrail" {
  bucket              = "acme-org-cloudtrail-logs"
  object_lock_enabled = true
}

resource "aws_s3_bucket_versioning" "cloudtrail" {
  bucket = aws_s3_bucket.cloudtrail.id
  versioning_configuration { status = "Enabled" }
}

resource "aws_s3_bucket_object_lock_configuration" "cloudtrail" {
  bucket = aws_s3_bucket.cloudtrail.id
  rule {
    default_retention {
      mode = "COMPLIANCE"
      days = 400
    }
  }
}

resource "aws_s3_bucket_public_access_block" "cloudtrail" {
  bucket                  = aws_s3_bucket.cloudtrail.id
  block_public_acls       = true
  block_public_policy     = true
  ignore_public_acls      = true
  restrict_public_buckets = true
}

Object lock in compliance mode means even the root user of the log archive account cannot delete log objects before the retention period ends. Choose the retention carefully; compliance mode cannot be shortened.

The bucket policy allows CloudTrail to write for the organization trail, conditioned on the trail's ARN:

{
  "Sid": "AWSCloudTrailWrite",
  "Effect": "Allow",
  "Principal": { "Service": "cloudtrail.amazonaws.com" },
  "Action": "s3:PutObject",
  "Resource": "arn:aws:s3:::acme-org-cloudtrail-logs/AWSLogs/*",
  "Condition": {
    "StringEquals": {
      "s3:x-amz-acl": "bucket-owner-full-control",
      "aws:SourceArn": "arn:aws:cloudtrail:eu-west-1:111111111111:trail/org-trail"
    }
  }
}

A matching statement allows s3:GetBucketAcl. Logs are encrypted with a KMS key in the log archive account whose policy allows CloudTrail to use it for this trail.

Step 2: the organization trail

In the management account (or a delegated administrator account for CloudTrail):

aws cloudtrail create-trail --name org-trail \
  --s3-bucket-name acme-org-cloudtrail-logs \
  --is-organization-trail --is-multi-region-trail \
  --enable-log-file-validation \
  --kms-key-id arn:aws:kms:eu-west-1:222222222222:key/5b1c...
aws cloudtrail start-logging --name org-trail
  • Multi-region: captures activity in every region, including regions you do not use, which is exactly where attackers like to start resources.
  • Log file validation: CloudTrail writes signed digest files, so you can prove logs were not modified.
  • Member accounts cannot modify or delete an organization trail.

By default the trail records management events. For buckets with sensitive data, add data events selectively; recording all S3 data events across an organization can be expensive.

Step 3: the Athena table with partition projection

CloudTrail writes logs under paths such as AWSLogs/o-abc123/111122223333/CloudTrail/eu-west-1/2026/07/01/. With partition projection, Athena computes partitions from the query's filters instead of needing a crawler or MSCK REPAIR:

CREATE EXTERNAL TABLE cloudtrail_org (
  eventversion STRING,
  useridentity STRUCT<type:STRING, principalid:STRING, arn:STRING, accountid:STRING,
                      invokedby:STRING, accesskeyid:STRING, username:STRING,
                      sessioncontext:STRUCT<attributes:STRUCT<mfaauthenticated:STRING, creationdate:STRING>,
                                            sessionissuer:STRUCT<type:STRING, principalid:STRING, arn:STRING,
                                                                 accountid:STRING, username:STRING>>>,
  eventtime STRING,
  eventsource STRING,
  eventname STRING,
  awsregion STRING,
  sourceipaddress STRING,
  useragent STRING,
  errorcode STRING,
  errormessage STRING,
  requestparameters STRING,
  responseelements STRING,
  requestid STRING,
  eventid STRING,
  readonly STRING,
  recipientaccountid STRING
)
PARTITIONED BY (account STRING, region STRING, day STRING)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://acme-org-cloudtrail-logs/AWSLogs/o-abc123/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.account.type' = 'enum',
  'projection.account.values' = '111122223333,444455556666,777788889999',
  'projection.region.type' = 'enum',
  'projection.region.values' = 'eu-west-1,eu-central-1,us-east-1',
  'projection.day.type' = 'date',
  'projection.day.range' = '2025/01/01,NOW',
  'projection.day.format' = 'yyyy/MM/dd',
  'projection.day.interval' = '1',
  'projection.day.interval.unit' = 'DAYS',
  'storage.location.template' = 's3://acme-org-cloudtrail-logs/AWSLogs/o-abc123/${account}/CloudTrail/${region}/${day}'
);

The account list is maintained by Terraform from the organization's account list, so new accounts appear automatically. Queries must filter on day (and ideally account and region) to scan only relevant data.

Five queries we use most

Who changed a security group?

SELECT eventtime, useridentity.arn, sourceipaddress, eventname, requestparameters
FROM cloudtrail_org
WHERE day BETWEEN '2026/04/01' AND '2026/04/30'
  AND account = '444455556666'
  AND eventname IN ('AuthorizeSecurityGroupIngress', 'ModifySecurityGroupRules')
  AND requestparameters LIKE '%sg-0a1b2c3d4e5f67890%'
ORDER BY eventtime;

Console logins without MFA:

SELECT eventtime, useridentity.arn, sourceipaddress
FROM cloudtrail_org
WHERE day >= '2026/07/01' AND eventname = 'ConsoleLogin'
  AND json_extract_scalar(additionaleventdata, '$.MFAUsed') = 'No';

(This one needs the additionaleventdata column added to the table definition.)

Activity from an access key suspected to be leaked:

SELECT eventtime, account, awsregion, eventsource, eventname, sourceipaddress, errorcode
FROM cloudtrail_org
WHERE day >= '2026/06/01' AND useridentity.accesskeyid = 'AKIAEXAMPLEKEY123456'
ORDER BY eventtime;

Changes to logging or guardrails:

SELECT eventtime, account, useridentity.arn, eventname
FROM cloudtrail_org
WHERE day >= '2026/06/01'
  AND eventname IN ('StopLogging', 'DeleteTrail', 'PutBucketPolicy', 'DeleteDetector',
                    'DisableSecurityHub', 'PutEventSelectors');

Denied calls by principal, a sign of probing:

SELECT useridentity.arn, count(*) AS denied
FROM cloudtrail_org
WHERE day >= '2026/07/01' AND errorcode IN ('AccessDenied', 'UnauthorizedOperation')
GROUP BY 1 ORDER BY denied DESC LIMIT 20;

Access to the logs

Read access to the log bucket is itself sensitive: CloudTrail records reveal resource names, IP addresses and who did what. Only the security account's investigation role can query the table, through an Athena workgroup with query result encryption and its own results bucket. Queries run through that workgroup are themselves logged, so investigations leave an audit trail too.

Cost and performance

Athena charges by data scanned. Partition filters keep typical investigation queries to a few gigabytes. For frequent queries, a scheduled job converts recent days into Parquet with a CTAS statement, which reduces scan size by an order of magnitude. CloudTrail Lake is an alternative managed option with its own pricing.

Key takeaways

  • Use one multi-region organization trail with log file validation.
  • Store logs in a dedicated log archive account with object lock, versioning and KMS.
  • Query with Athena using partition projection, always filtering by day.
  • Keep a library of investigation queries ready before you need them.
  • Add data events selectively, and watch Athena scan costs.

References