CloudTrail Lake: create an event data store and answer questions in SQL

SQL over CloudTrail without a pipeline

CloudTrail Lake stores events in an event data store and lets you query them with SQL, without exporting logs to S3 and setting up Athena. It bills for ingestion and for data scanned, so narrow every query by time. Check current availability and pricing for your account and Region before creating a store.

Snippet

aws cloudtrail create-event-data-store --name sandbox-mgmt \
  --retention-period 90 --no-multi-region-enabled

aws cloudtrail list-event-data-stores --query 'EventDataStores[].[Name,EventDataStoreArn]' --output text

Running a query

The FROM clause names the store by its ID, the last part of its ARN. start-query returns a query ID; get-query-results returns rows once the status is FINISHED.

Snippet

EDS=a1b2c3d4-5678-90ab-cdef-EXAMPLE11111
QID=$(aws cloudtrail start-query --query-statement \
  "SELECT eventTime, eventName, userIdentity.arn, sourceIPAddress, errorCode
   FROM $EDS
   WHERE eventTime > '2026-11-01 00:00:00' AND errorCode = 'AccessDenied'
   ORDER BY eventTime DESC LIMIT 20" --query QueryId --output text)
aws cloudtrail get-query-results --query-id "$QID" --output json

Reading the rows

Results come back as lists of single-key objects. A short jq expression flattens them into a table.

Snippet

aws cloudtrail get-query-results --query-id "$QID" | jq -r '.QueryResultRows[] | map(to_entries[0].value) | @tsv'

2026-11-02 03:12:40  ListBuckets       arn:aws:sts::111122223333:assumed-role/app-web-role/i-0abc  198.51.100.200  AccessDenied
2026-11-02 03:12:41  GetBucketPolicy   arn:aws:sts::111122223333:assumed-role/app-web-role/i-0abc  198.51.100.200  AccessDenied

Worked question: who creates access keys?

Long-lived access keys are a common source of leaks. This query lists every CreateAccessKey call in the window with the creator and the user the key was created for. In the sandbox it returns two rows: one from the platform team’s automation, expected, and one from a developer creating a key for themselves at 23:40, which goes to their manager with the account policy that discourages user keys.

Snippet

SELECT eventTime, userIdentity.arn AS creator,
       element_at(requestParameters, 'userName') AS forUser, sourceIPAddress
FROM <eds-id>
WHERE eventName = 'CreateAccessKey' AND eventTime > '2026-10-01 00:00:00'
ORDER BY eventTime

Diagram

A CloudTrail Lake question in four steps

Store

Create an event data store with a retention period.

Command

aws cloudtrail create-event-data-store --name sandbox-mgmt --retention-period 90

Ask

Start a SQL query bounded by time.

Command

aws cloudtrail start-query --query-statement "SELECT ... WHERE eventTime > '2026-11-01 00:00:00'"

Fetch
Collect rows when the status is FINISHED.
Command
```
aws cloudtrail get-query-results --query-id "$QID"
```

Flatten

Turn rows into a readable table.

Command

jq -r '.QueryResultRows[] | map(to_entries[0].value) | @tsv'

Check yourself

1. Why bound every CloudTrail Lake query by eventTime?

2. Which value goes in the FROM clause?

3. How do you read a key from requestParameters in Lake SQL?