Demo Querying CloudTrail With Athena

This guide shows how to make AWS CloudTrail logs queryable using Amazon Athena. You’ll learn how to:

Create a CloudTrail trail that delivers logs to an S3 bucket.
Verify the CloudTrail bucket and its folder layout.
Use CloudTrail Event history to generate an Athena external table definition (DDL).
Configure Athena’s query-results location and run SQL queries against the table.

Follow the step-by-step instructions and examples below.

Create a trail that stores logs in S3

Open the CloudTrail console and create a trail to deliver management events to an S3 bucket. Give the trail a descriptive name (for example, management-events1) and allow CloudTrail to create or use an existing S3 bucket to store logs.

A screenshot of the AWS CloudTrail console on the "Quick trail create" page, showing the trail log bucket/folder field and an informational note that S3 storage may incur charges. The bottom right shows "Cancel" and an orange "Create trail" button.

After creating the trail, CloudTrail lists the trail and the S3 bucket used to store logs. Example output:

arn:aws:cloudtrail:us-east-2:635573991785:trail/management-events1
aws-cloudtrail-logs-635573991785-684e9ae5

Confirm the CloudTrail S3 bucket contents

Open the S3 console and inspect the bucket CloudTrail created. CloudTrail stores logs under a structured prefix like: s3://<bucket>/AWSLogs/<account-id>/CloudTrail/<region>/YYYY/MM/DD/... If no events have occurred yet, the folder may be empty initially.

A screenshot of the Amazon S3 console showing the Objects view for an aws-cloudtrail-logs bucket, with a CloudTrail/ folder listed. The toolbar shows buttons like Upload, Copy S3 URI, Download, Actions, and Create folder.

Create an Athena table from CloudTrail Event history

CloudTrail offers a convenient shortcut: in the CloudTrail console navigate to Event history and choose Create Athena table. CloudTrail will generate a CREATE EXTERNAL TABLE DDL tailored to the JSON structure of CloudTrail logs and the S3 prefix for your trail. Review and edit the generated DDL as needed (notably the LOCATION) before executing it in Athena.

A screenshot of the AWS CloudTrail Event history page in the AWS console showing a green success banner that an Amazon Athena table was created. The page also displays controls like "Download events," "Query in Lake," and "Create Athena table."

Example CREATE EXTERNAL TABLE statement

The following DDL is representative of what CloudTrail generates. Update the LOCATION to match your S3 prefix for CloudTrail logs.

CREATE EXTERNAL TABLE cloudtrail_logs_aws_cloudtrail_logs_635573991785_684e9ae5 (
  eventVersion STRING,
  userIdentity STRUCT<
    type: STRING,
    principalId: STRING,
    arn: STRING,
    accountId: STRING,
    invokedBy: STRING,
    accessKeyId: STRING,
    userName: STRING>,
  eventTime STRING,
  eventSource STRING,
  eventName STRING,
  awsRegion STRING,
  sourceIPAddress STRING,
  userAgent STRING,
  errorCode STRING,
  errorMessage STRING,
  requestParameters STRING,
  responseElements STRING,
  additionalEventData STRING,
  requestID STRING,
  eventID STRING,
  resources ARRAY<STRUCT<ARN:STRING, accountId:STRING, type:STRING>>,
  eventType STRING,
  apiVersion STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ('ignore.malformed.json'='true')
LOCATION 's3://aws-cloudtrail-logs-635573991785-684e9ae5/AWSLogs/635573991785/CloudTrail/'
TBLPROPERTIES ('has_encrypted_data'='false');

Open Amazon Athena and configure query results

Before running queries, open the Amazon Athena console and set a query-results S3 location. Athena stores query output and temporary artifacts in this location—use a dedicated S3 bucket or prefix that you control (do not reuse your CloudTrail logs bucket).

Configure a dedicated S3 bucket (or prefix) as the Athena query results location. This keeps temporary query artifacts separate from your CloudTrail logs.

Steps in the Athena console:

Open Query settings (or Settings).
Choose Manage (or edit) to set the results location to an S3 bucket/prefix you own.
Save settings and open the query editor.

Run a simple query against the CloudTrail table

With the table created and results location configured, run SQL to inspect CloudTrail events. For example, list recent events with their timestamps and the ARN of the identity that performed the action:

SELECT eventtime, eventname, useridentity.arn
FROM cloudtrail_logs_aws_cloudtrail_logs_635573991785_684e9ae5
LIMIT 100;

Click Run to execute the query. Athena will scan the relevant S3 files and return results, including the runtime and bytes scanned.

A screenshot of the Amazon Athena query editor in the AWS console, showing a cloudtrail_logs table in the left pane and query controls (Run again, Explain, Clear) on the right. The query results panel below shows a completed query with runtime and data-scanned stats.

You should now see rows with event timestamps, action names such as ListBuckets or GetTable, and the IAM identity ARN that performed each action. Use this data to investigate activity, audit access patterns, or build dashboards.

Cost considerations

Athena pricing is based on the data scanned by each query (see Amazon Athena pricing at https://aws.amazon.com/athena/pricing/). To reduce costs, partition data (for example by year/month/day), compress logs, or convert JSON logs to columnar formats (Parquet/ORC). Also remember S3 storage for CloudTrail logs incurs standard storage costs.

Quick reference

Task	Notes / Example
Create CloudTrail trail	Name it clearly, e.g., `management-events1`
CloudTrail S3 prefix	`s3://<bucket>/AWSLogs/<account-id>/CloudTrail/<region>/...`
Athena table DDL	Use CloudTrail’s Create Athena table to generate DDL; update `LOCATION`
Athena results bucket	Use a separate S3 bucket/prefix for query results (do not reuse logs bucket)

Summary

CloudTrail captures API activity and stores logs in S3.
Use CloudTrail Event history to generate an Athena external table mapping to CloudTrail JSON logs.
Configure Athena’s query-results S3 location before running queries.
Use SQL in Amazon Athena to analyze, audit, and investigate CloudTrail logs.

Links and references

CloudTrail documentation: https://docs.aws.amazon.com/cloudtrail/latest/userguide/cloudtrail-user-guide.html
Amazon S3 documentation: https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html
Amazon Athena documentation: https://docs.aws.amazon.com/athena/latest/ug/what-is.html
Athena pricing: https://aws.amazon.com/athena/pricing/

Introduction

Domain 1 - SDLC Automation

Domain 2 - Configuration Management and IaC

Domain 3 - Resilient Cloud Solutions

Domain 4 - Monitoring and Logging

Domain 5 - Incident and Event Response

Domain 6 - Security and Compliance

Demo Querying CloudTrail With Athena

Create a trail that stores logs in S3

Confirm the CloudTrail S3 bucket contents

Create an Athena table from CloudTrail Event history

Open Amazon Athena and configure query results

Run a simple query against the CloudTrail table

Cost considerations

Quick reference

Summary

Links and references

Watch Video

Practice Lab

​Create a trail that stores logs in S3

​Confirm the CloudTrail S3 bucket contents

​Create an Athena table from CloudTrail Event history

​Open Amazon Athena and configure query results

​Run a simple query against the CloudTrail table

​Cost considerations

​Quick reference

​Summary

​Links and references

Watch Video

Practice Lab

Create a trail that stores logs in S3

Confirm the CloudTrail S3 bucket contents

Create an Athena table from CloudTrail Event history

Open Amazon Athena and configure query results

Run a simple query against the CloudTrail table

Cost considerations

Quick reference

Summary

Links and references