Building Tables Over CloudTrail Data

Athena cannot query raw S3 files until you tell it how to read them. A table maps the CloudTrail JSON files in your S3 bucket to named columns with types. Once the table exists, the rich structure of CloudTrail events becomes addressable with ordinary SQL.

CloudTrail delivers events as JSON, each record describing one API call with nested fields like userIdentity, eventName, sourceIPAddress, and requestParameters. The table schema must mirror this nesting, including struct types for objects, so queries can reach fields like userIdentity.arn.

You do not have to hand-write the schema. AWS publishes a standard CREATE TABLE statement for CloudTrail, and the CloudTrail console and Athena can generate it for you. Using the provided DDL ensures every field, including deeply nested ones, is mapped correctly so your queries do not miss data.

A SerDe (Serializer/Deserializer) tells Athena how to parse each file. CloudTrail tables use a SerDe designed for the CloudTrail JSON layout, which understands the records array and nested structures. Choosing the correct SerDe is essential; the wrong one yields parse errors or null columns.

The table's LOCATION clause points to the S3 prefix where CloudTrail stores logs, typically a path that includes the account ID and region. Getting this prefix right matters: too broad and you scan irrelevant data, too narrow and you miss events you meant to query.

With the table built, you query nested fields using dot notation, such as userIdentity.arn or requestParameters.bucketName. SQL functions help unpack arrays and JSON strings. This is where Athena shines for investigations: you can filter precisely on the event name, principal, and parameters of any API call.

Instead of manual DDL, an AWS Glue crawler can scan the S3 data and infer a schema into the Data Catalog automatically. For well-known formats like CloudTrail the provided DDL is usually cleaner, but crawlers help for custom or evolving log formats where you would rather not write schema by hand.

CloudTrail JSON has fields that are themselves JSON strings, like requestParameters when values vary by service. You often parse these at query time with JSON functions. Knowing that some columns are strings to be parsed, not pre-structured columns, avoids confusion when a field does not behave like a normal column.

For an organization trail, logs from all accounts land under one bucket with account-specific prefixes. You can build a single table over the whole prefix to query every account at once, or per-account tables for isolation. A unified table is powerful for org-wide threat hunting in one query.

After creating the table, run a quick SELECT with a small LIMIT to confirm rows return and columns populate as expected. If columns are null, the SerDe, location, or schema is likely off. This sanity check saves you from building investigations on a silently broken table.

Once the CloudTrail table is correct, you have a queryable audit trail spanning all recorded history. Every console login, API call, and error is now a row you can filter and aggregate. This table is the foundation for the incident queries you will write next.

Test your table-building knowledge.

To query CloudTrail in Athena you build a table that maps the JSON files to columns, using AWS's provided CREATE TABLE DDL, the correct SerDe, and the right S3 LOCATION. Query nested fields with dot notation, parse JSON-string columns at query time, and verify with a small SELECT. An organization trail can be one table for org-wide hunting.