Build a CloudTrail-to-SIEM detection pipeline

GuardDuty tells you what AWS decides is suspicious. A detection engineer needs to write their own logic against the raw audit trail - and CloudTrail is that raw trail. This walkthrough wires CloudTrail into a queryable pipeline (S3 → Athena), writes detections for the techniques you detonated in the break-it-then-catch-it walkthrough, expresses them portably as Sigma rules, and proves they actually fire. It is the blue-team counterpart to the offensive lab.

What you will build

• A CloudTrail trail delivering to S3 (reused from the offensive lab, or created fresh).
• An Athena table over the CloudTrail logs, using partition projection so scans stay cheap.
• A library of detection SQL for the classic attacker actions.
• The same logic written as portable Sigma rules.
• Proof the detections fire against real telemetry you generated.

Prerequisites

• A lab account with CloudTrail delivering to S3 (the offensive lab sets this up).
• Amazon Athena enabled (it queries the S3 logs directly; cost is pennies with partition projection).
• Telemetry worth hunting - run the offensive lab's attacks before you start.
• Background from the detection engineering and cloud SOC pages.

Step-by-step

1. Confirm CloudTrail is landing in S3

aws cloudtrail describe-trails --query 'trailList[].{name:Name,bucket:S3BucketName}'
aws s3 ls s3://<your-trail-bucket>/AWSLogs/<account-id>/CloudTrail/ --recursive | head

You should see gzip'd JSON objects partitioned by region and date. If nothing is there yet, generate some activity (a few CLI calls) and wait a few minutes - CloudTrail delivers in batches.

2. Create the Athena table with partition projection

Partition projection lets Athena compute partitions from the S3 path pattern, so you never run MSCK REPAIR or add partitions by hand. Run this DDL in the Athena console (swap in your bucket, account, and regions):

CREATE EXTERNAL TABLE cloudtrail_logs (
  eventVersion STRING,
  userIdentity STRUCT<
    type:STRING, principalId:STRING, arn:STRING, accountId:STRING,
    userName:STRING, invokedBy:STRING,
    sessionContext:STRUCT<attributes:STRUCT<mfaAuthenticated:STRING,creationDate:STRING>>>,
  eventTime STRING,
  eventSource STRING,
  eventName STRING,
  awsRegion STRING,
  sourceIPAddress STRING,
  userAgent STRING,
  errorCode STRING,
  errorMessage STRING,
  requestParameters STRING,
  responseElements STRING,
  additionalEventData STRING,
  eventID STRING,
  eventType STRING,
  recipientAccountId STRING
)
PARTITIONED BY (region STRING, date STRING)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORAGE LOCATION 's3://<bucket>/AWSLogs/<account>/CloudTrail/'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.region.type'='enum',
  'projection.region.values'='us-east-1,us-west-2',
  'projection.date.type'='date',
  'projection.date.range'='2025/01/01,NOW',
  'projection.date.format'='yyyy/MM/dd',
  'storage.location.template'='s3://<bucket>/AWSLogs/<account>/CloudTrail/${region}/${date}'
);

If the schema ever drifts, the fastest fix is the console: CloudTrail → Event history → Create Athena table generates a matching DDL for your trail. The key idea to internalise: requestParameters and responseElements arrive as JSON strings, so you parse them with json_extract_scalar, not struct access.

3. Baseline: who is doing what?

SELECT useridentity.arn AS who, eventname, count(*) AS n
FROM cloudtrail_logs
WHERE date >= date_format(current_date - interval '1' day, '%Y/%m/%d')
GROUP BY 1, 2
ORDER BY n DESC
LIMIT 50;

Always filter on date (and region when you can). That is what keeps Athena from scanning every object in the bucket - the difference between a one-cent query and a painful one.

4. The detection library

Each query below is a detection. Read the "why it matters" line, then run it against your telemetry.

Root account activity - the root user should almost never make API calls.

SELECT eventtime, eventname, sourceipaddress
FROM cloudtrail_logs
WHERE useridentity.type = 'Root' AND eventtype = 'AwsApiCall'
  AND date >= date_format(current_date - interval '7' day, '%Y/%m/%d');

Console login without MFA - a successful sign-in with no second factor.

SELECT eventtime, useridentity.arn, sourceipaddress
FROM cloudtrail_logs
WHERE eventname = 'ConsoleLogin'
  AND json_extract_scalar(additionaleventdata, '$.MFAUsed') = 'No'
  AND json_extract_scalar(responseelements, '$.ConsoleLogin') = 'Success';

Access key created - a common persistence step (Pacu and manual privesc both do this).

SELECT eventtime, useridentity.arn AS actor,
       json_extract_scalar(responseelements, '$.accessKey.userName') AS target_user
FROM cloudtrail_logs
WHERE eventname = 'CreateAccessKey';

CloudTrail tampering - matches Stratus aws.defense-evasion.cloudtrail-stop. Someone blinding the logs is a high-severity signal.

SELECT eventtime, useridentity.arn, eventname, requestparameters
FROM cloudtrail_logs
WHERE eventname IN ('StopLogging','DeleteTrail','UpdateTrail','PutEventSelectors');

Security group opened to the world - ingress from 0.0.0.0/0.

SELECT eventtime, useridentity.arn, requestparameters
FROM cloudtrail_logs
WHERE eventname = 'AuthorizeSecurityGroupIngress'
  AND requestparameters LIKE '%0.0.0.0/0%';

S3 bucket made public - matches Stratus aws.exfiltration.s3-backdoor-bucket-policy.

SELECT eventtime, useridentity.arn, eventname, requestparameters
FROM cloudtrail_logs
WHERE eventname IN ('PutBucketPolicy','PutBucketAcl')
  AND (requestparameters LIKE '%AllUsers%'
       OR requestparameters LIKE '%"Principal":"*"%'
       OR requestparameters LIKE '%"Principal":{"AWS":"*"}%');

Secret read from SSM or Secrets Manager - the quiet collection step GuardDuty often ignores. This is the detection the offensive lab proved you needed.

SELECT eventtime, useridentity.arn, eventname,
       json_extract_scalar(requestparameters, '$.name') AS param,
       json_extract_scalar(requestparameters, '$.secretId') AS secret
FROM cloudtrail_logs
WHERE eventname IN ('GetParameter','GetParameters','GetSecretValue');

Instance credentials used off-instance - the heuristic behind GuardDuty's InstanceCredentialExfiltration. An assumed role that belongs to an EC2 instance profile, but whose calls come from a non-AWS source IP, means the role's credentials walked out of the instance.

SELECT eventtime, useridentity.arn, sourceipaddress, eventname
FROM cloudtrail_logs
WHERE useridentity.type = 'AssumedRole'
  AND useridentity.arn LIKE '%i-%'          -- role session named for an instance id
  AND sourceipaddress NOT LIKE '%.amazonaws.com'
  AND sourceipaddress NOT IN ('CHANGE-ME-your-lab-egress-ip');

5. Make them portable with Sigma

Sigma is a vendor-neutral detection format: write the logic once, convert it to whatever backend your SIEM speaks. Here is the CloudTrail-tampering detection as a Sigma rule:

title: CloudTrail Logging Disabled
id: 4d2a12ab-9e3f-4b1a-8c77-lab-example
status: experimental
logsource:
  product: aws
  service: cloudtrail
detection:
  selection:
    eventSource: cloudtrail.amazonaws.com
    eventName:
      - StopLogging
      - DeleteTrail
  condition: selection
level: high
tags:
  - attack.defense-evasion
  - attack.t1562.008

Convert it with the Sigma CLI - pip install sigma-cli then sigma convert -t <backend> rule.yml. The SigmaHQ repo already ships dozens of AWS CloudTrail rules - read them, and you will recognise the exact patterns from your detection library above. That is the portable, shareable form employers want to see.

6. Prove the detections fire

Re-run each query after generating attack telemetry. If you completed the offensive lab, the Stratus detonations are already in the trail - your StopLogging, PutBucketPolicy, and GetParameter queries should now return rows tied to the exact moment you detonated them. This closed loop - generate telemetry, query it, confirm the detection triggers - is the entire discipline of detection engineering in miniature.

7. Operationalize (pick one)

• Scheduled Athena: wrap a query in a Lambda triggered by EventBridge Scheduler; publish hits to SNS or a Slack webhook.
• OpenSearch: stream CloudTrail to Amazon OpenSearch (or a local Elastic stack) and build dashboards and alerting there.
• A SIEM you are learning: forward the logs to Splunk (free tier), Elastic, or a Microsoft Sentinel trial and port your Sigma rules across.

8. Cost and teardown

-- Athena bills per TB scanned; partitioned WHERE clauses keep it to pennies.
DROP TABLE cloudtrail_logs;
-- then empty and delete the trail bucket if you are done with the account

The one sneaky cost here is CloudTrail data events (S3 object-level, Lambda invocations) - they are voluminous and billable. Leave them off unless a specific detection needs them.

Common mistakes

• Querying without a date filter. Athena scans every partition and the bill balloons. Always bound by date (and region).
• Treating requestParameters as structured. It is a JSON string - parse it with json_extract_scalar.
• Writing detections you never test. An untested detection is a guess. Generate the telemetry and confirm it fires.
• Leaving data-event logging on. It is the CloudTrail cost that surprises people.