Cloud Security Office Hours Banner

Build a CloudTrail-to-SIEM detection pipeline

Turn raw CloudTrail logs into a working detection pipeline. Query them with Athena, write detections for real attacker actions, make them portable with Sigma, and prove they fire.

Jump to the build Back to Home Lab

· · Vendor-neutral

Time: ~4 hours  ·  Difficulty: Intermediate → Advanced  ·  Stack: CloudTrail · S3 · Athena · Sigma · (optional) OpenSearch

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.

Do the offensive lab first. This pipeline hunts through telemetry. The most instructive way to run it is against the events you generated in the break-it-then-catch-it walkthrough - so your queries have real attacker activity to catch, not just your own console clicks.

On this page

  1. What you will build
  2. Prerequisites
  3. Step-by-step
  4. Common mistakes
  5. Where next

What you will build

Prerequisites

Step-by-step

1. Confirm CloudTrail is landing in S3

aws cloudtrail describe-trails --query 'trailList[].{name:Name,bucket:S3BucketName}'   # list each trail and the bucket it writes to
aws s3 ls s3://<your-trail-bucket>/AWSLogs/<account-id>/CloudTrail/ --recursive | head   # --recursive walks the prefix; head shows just the first few objects

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):

-- EXTERNAL = the data stays in S3; this only defines a schema Athena reads through.
CREATE EXTERNAL TABLE cloudtrail_logs (
  eventVersion STRING,
  userIdentity STRUCT<                        -- userIdentity is nested JSON, so it maps to a STRUCT you dot into (useridentity.arn)
    type:STRING, principalId:STRING, arn:STRING, accountId:STRING,
    userName:STRING, invokedBy:STRING,
    sessionContext:STRUCT<attributes:STRUCT<mfaAuthenticated:STRING,creationDate:STRING>>>,
  eventTime STRING,
  eventSource STRING,                          -- the service, e.g. s3.amazonaws.com
  eventName STRING,                            -- the API action, e.g. GetObject - the column most detections filter on
  awsRegion STRING,
  sourceIPAddress STRING,                      -- where the call came from - key for "off-instance credential use"
  userAgent STRING,
  errorCode STRING,                            -- present when a call was denied/failed - useful for spotting brute force
  errorMessage STRING,
  requestParameters STRING,                    -- NOTE: a raw JSON *string*, not a struct - parse with json_extract_scalar
  responseElements STRING,                     -- same: JSON string
  additionalEventData STRING,
  eventID STRING,
  eventType STRING,
  recipientAccountId STRING
)
PARTITIONED BY (region STRING, date STRING)    -- partitions carve the data by region+date so queries scan less of it
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'   -- SERDE = the reader that parses each line as JSON
STORAGE LOCATION 's3://<bucket>/AWSLogs/<account>/CloudTrail/'
TBLPROPERTIES (
  'projection.enabled'='true',                 -- partition projection: compute partitions from the path, no MSCK REPAIR ever
  'projection.region.type'='enum',             -- region is a fixed list of values...
  'projection.region.values'='us-east-1,us-west-2',   -- ...these ones (add every region your trail covers)
  'projection.date.type'='date',               -- date is a range...
  'projection.date.range'='2025/01/01,NOW',    -- ...from this start date up to today
  'projection.date.format'='yyyy/MM/dd',       -- the date format used in the S3 path
  'storage.location.template'='s3://<bucket>/AWSLogs/<account>/CloudTrail/${region}/${date}'   -- how region+date map back to an S3 path
);

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   -- who did what, and how many times
FROM cloudtrail_logs
WHERE date >= date_format(current_date - interval '1' day, '%Y/%m/%d')   -- last 1 day; formatted to match the partition (yyyy/MM/dd)
GROUP BY 1, 2            -- group by the 1st and 2nd selected columns (who, eventname)
ORDER BY n DESC         -- busiest actor+action first
LIMIT 50;               -- cap the result at 50 rows

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'      -- the account root user, which should almost never call the API
  AND eventtype = 'AwsApiCall'        -- ignore console-login/service events, keep real API actions
  AND date >= date_format(current_date - interval '7' day, '%Y/%m/%d');   -- last 7 days (partition-bounded, so it's cheap)

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

SELECT eventtime, useridentity.arn, sourceipaddress
FROM cloudtrail_logs
WHERE eventname = 'ConsoleLogin'                                            -- a sign-in to the web console
  AND json_extract_scalar(additionaleventdata, '$.MFAUsed') = 'No'         -- reach into the JSON string: no second factor
  AND json_extract_scalar(responseelements, '$.ConsoleLogin') = 'Success'; -- and the login actually succeeded

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

SELECT eventtime, useridentity.arn AS actor,                                       -- who created the key
       json_extract_scalar(responseelements, '$.accessKey.userName') AS target_user -- and which user it was created FOR
FROM cloudtrail_logs
WHERE eventname = 'CreateAccessKey';   -- actor != target_user is the tell: someone minting a key on another user (persistence)

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');   -- any change that could disable or narrow logging = someone blinding the logs

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

SELECT eventtime, useridentity.arn, requestparameters
FROM cloudtrail_logs
WHERE eventname = 'AuthorizeSecurityGroupIngress'   -- a new inbound firewall rule was added
  AND requestparameters LIKE '%0.0.0.0/0%';         -- LIKE %..% substring-matches the "open to the whole internet" CIDR

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')    -- someone changed a bucket's policy or ACL
  AND (requestparameters LIKE '%AllUsers%'               -- the three ways "public" shows up in the request JSON:
       OR requestparameters LIKE '%"Principal":"*"%'     --   the AllUsers ACL group,
       OR requestparameters LIKE '%"Principal":{"AWS":"*"}%');   --   or a wildcard principal (either JSON shape)

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,       -- SSM: which parameter was read
       json_extract_scalar(requestparameters, '$.secretId') AS secret   -- Secrets Manager: which secret was read
FROM cloudtrail_logs
WHERE eventname IN ('GetParameter','GetParameters','GetSecretValue');   -- the read APIs for SSM and Secrets Manager - the quiet collection step

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'     -- a call made with temporary role credentials
  AND useridentity.arn LIKE '%i-%'          -- role session named for an instance id (i-0abc...) = an EC2 instance profile
  AND sourceipaddress NOT LIKE '%.amazonaws.com'   -- but the call did NOT originate from inside AWS...
  AND sourceipaddress NOT IN ('CHANGE-ME-your-lab-egress-ip');   -- ...and isn't your own known egress IP -> the creds walked off the box

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       # human-readable name for the rule
id: 4d2a12ab-9e3f-4b1a-8c77-lab-example  # a unique id (normally a real UUID)
status: experimental                     # maturity: experimental -> test -> stable
logsource:                               # which logs this rule applies to
  product: aws
  service: cloudtrail
detection:
  selection:                             # a named block of match conditions...
    eventSource: cloudtrail.amazonaws.com
    eventName:                           # match if eventName is either of these:
      - StopLogging
      - DeleteTrail
  condition: selection                   # fire when the "selection" block matches
level: high                              # alert severity
tags:                                    # MITRE ATT&CK mapping for this behaviour
  - attack.defense-evasion
  - attack.t1562.008                     # T1562.008 = Impair Defenses: Disable Cloud Logs

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)

8. Cost and teardown

-- Athena bills per TB scanned; partitioned WHERE clauses keep it to pennies.
DROP TABLE cloudtrail_logs;   -- removes only the Athena schema - the underlying S3 logs are untouched
-- then empty and delete the trail bucket if you are done with the account (that deletes the actual log data)

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

Where next