Reading CloudTrail with DuckDB

01The query I was going to ship

AWS signed the agreement to acquire DuckLabs, the team behind DuckDB, on 26 August 2026. The database stays MIT-licensed under the independent DuckDB Foundation and the founders keep leading the technical direction, so nothing changes for anyone using it. But the collaboration that led there started in early 2025 around S3 Tables and SageMaker Lakehouse, which tells you where this is going: S3 as a query target rather than a storage tier.

That was enough to make me try something I had been putting off. CloudTrail forensics without Athena.

I had already written the queries. I had tested them against synthetic CloudTrail I generated myself, with a fake compromised key, a fake enumeration burst, a fake pile of AccessDenied errors. Everything worked. Then I pointed the same queries at a real account with almost nothing in it, and one of them fell over in a way I would not have caught until it paged someone.

That is most of what this post is about.

02Why CloudTrail is the awkward shape

A CloudTrail file in S3 is gzipped JSON, and every event you want is buried inside a top-level Records array:

{"Records": [
  {"eventTime": "...", "eventName": "RunInstances",
   "userIdentity": {"type": "Root", "arn": "..."},
   "requestParameters": {...}},
  ...
]}

Nested objects, inside an array, inside a gzipped file, spread across thousands of small objects. That shape is why Athena over raw CloudTrail is slow, and why every example query you find online is a thicket of json_extract_scalar calls.

03Getting DuckDB onto the box

There is no server and no cluster. DuckDB is a single binary that runs inside whatever process you start, so a t3.large with an instance profile is the whole environment.

Then load the S3 extension and create a secret. PROVIDER credential_chain resolves credentials the way the AWS SDK does, so on EC2 it reads the instance profile from IMDS and no key material ever appears in SQL or on disk.

Before writing any SQL, confirm access. This is the step that fails, and it fails for ordinary IAM reasons rather than interesting ones.

04One macro, and the nesting goes away

unnest(Records) explodes the array into one row per event. format='auto' handles the gzip. Struct fields come out with dot notation, so r.userIdentity.arn is just a column reference and there are no JSON functions anywhere.

05What 118 events actually look like

First question: what is in there at all?

Not one mutation in the whole list. Most of any trail is the platform talking to itself — the SSM agent reporting in, the console loading panels, services polling their own configuration. Which is why the first filter earns its place immediately:

SELECT event_time, event_name, identity_type, source_ip
FROM trail(getvariable('trail_path'))
WHERE NOT read_only
ORDER BY event_time;

One human, two credential paths. My console actions arrive as Root from a home IP; the instance's own calls arrive as an AssumedRole from its public address. Worth knowing before you build anything that treats a new IP as suspicious.

readOnly is CloudTrail's own flag, incidentally, not something you infer from the verb name. One boolean instead of a regex over API names.

06The baseline table

This is the query I would put in front of a customer first. Not a detection — a description of normal.

Two things fall out of it.

A NULL principal is normal. AWS services calling on their own behalf have no ARN at all: identity_type is AWSService and principal_arn is NULL. That was 21 calls here. Any detection keyed on principal_arn drops them silently, which is the worst way for a rule to fail.

The user agent discriminates better than the IP. The instance role shows up as both amazon-ssm-agent/3.3.4624.0 and a bare Go-http-client/1.1. A bare HTTP client is also what attacker tooling looks like, so allowlisting user agents is fragile in both directions.

I know better than that. It is a throwaway account, which is precisely the excuse everyone uses, and it is the first thing I would flag in someone else's environment. One query on my own data made a point that a hundred best-practice posts had not.

07The burst that looked like an attack

Nothing had failed yet, so I generated some errors and ran the query I had already written for this post:

SELECT date_trunc('minute', event_time) AS minute, principal_arn, source_ip,
       count(*) AS denied, count(DISTINCT event_name) AS apis
FROM trail(getvariable('trail_path'))
WHERE error_code IS NOT NULL
GROUP BY 1,2,3 ORDER BY denied DESC;

That is textbook enumeration. If I had seen that shape in a customer account I would have escalated it. So before doing anything else, I looked at what it was actually failing on.

The fix is to match authorization failures specifically, and to drop service principals calling from AWS endpoints:

SELECT date_trunc('minute', event_time) AS minute,
       principal_arn, source_ip,
       count(*)                              AS denials,
       count(DISTINCT event_name)            AS apis,
       string_agg(DISTINCT error_code, ', ') AS codes
FROM trail(getvariable('trail_path'))
WHERE regexp_matches(error_code,
        '(AccessDenied|UnauthorizedOperation|Forbidden|InvalidClientTokenId|SignatureDoesNotMatch)')
  AND source_ip NOT LIKE '%.amazonaws.com'
  AND identity_type <> 'AWSService'
GROUP BY 1, 2, 3
HAVING denials > 5
ORDER BY denials DESC;

That also excludes NoSuchEntity, which is a missing resource rather than a refused permission — the error you get probing for roles that do not exist, and one you would otherwise treat as a signal.

The uncomfortable part is that the original query passed every synthetic test I gave it, because every error I planted was an AccessDenied. I had built a test set that only contained the thing I was looking for. An almost empty real account falsified the query in under an hour.

08Four kinds of state, four lifetimes

The friction worth warning you about. I quit the session, came back, and got this:

An empty region means no secret existed at all, so DuckDB fell back to unsigned requests and S3 refused them. Relaunching also lost the variable, with an equally indirect message:

Four things that all feel like setup, and every one of them persists differently:

An init file settles all four at once:

# /root/.duckdbrc — loaded automatically on every launch
LOAD httpfs;
LOAD aws;
CREATE OR REPLACE SECRET ct (TYPE s3, PROVIDER credential_chain, REGION 'us-east-1');
SET VARIABLE trail_path = 's3://aws-cloudtrail-logs-.../2026/09/11/*.json.gz';

Set REGION explicitly even though credential_chain can usually infer it. With no secret loaded there is nothing to infer from, and you pay an extra round trip on every glob while DuckDB works it out.

COPY (
  SELECT principal_arn, source_ip, user_agent, count(*) AS events
  FROM trail(getvariable('trail_path'))
  GROUP BY 1,2,3
) TO 's3://findings/baselines/acct-1234/2026-09-11.parquet' (FORMAT parquet);

10How I would actually run this

1. Put the install in an SSM document, not in your shell history. Idempotent, architecture-aware for Graviton, attached as a State Manager Association so the tooling is already on the instance before anything goes wrong.
2. Pre-fetch the extensions during install. DuckDB downloads them from the internet on first use, and an instance in a private subnet during an incident may have no egress — which you would discover at the worst possible moment. INSTALL httpfs; INSTALL aws; INSTALL json; while things are calm.
3. Stage the query library on disk alongside it, at something like /opt/duckdb/queries, so an incident starts with duckdb -init rather than with writing SQL from memory.
4. Narrow the prefix before you widen it. .../2026/09/11/*.json.gz scans a day; .../2026/09/** scans a month. Same pruning logic Athena bills you for, except here it only costs wall-clock time.
5. Build the baseline table first and keep it. A detection is just a new row appearing in that table, and you cannot recognise new without having written down normal.
6. Test every rule against a real account before you trust it, even a boring one. Especially a boring one — the benign noise is the part synthetic data never gets right.

I want to be careful about what I am claiming here, because there is a version of this post that says Athena is finished and it would be wrong.

The moment you need continuous detection across an organisation, retention queries spanning years, or a shared query surface that a SOC team hits concurrently, the catalog stops being overhead and becomes the entire point. That is Security Lake, or Athena, or your SIEM. Those are platforms, and platforms need the infrastructure I spent this post avoiding.

What DuckDB replaced is the exploratory loop. Athena's $5/TB is not expensive in absolute terms; it is expensive relative to zero when one person runs thirty variations of the same query in an hour, which is exactly what incident scoping looks like. And the setup tax is paid per account, every time, which for anyone working across a lot of customer environments is the cost that actually accumulates.

So the honest positioning is narrower than “replacement” and more useful than “neat trick”: this is ad-hoc analytical SQL over log data with no infrastructure to stand up or maintain, and at scale it gives you a faster picture than waiting on a crawler to finish.