> ## Documentation Index
> Fetch the complete documentation index at: https://notes.kodekloud.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Demo Querying CloudTrail With Athena

> Guide to store AWS CloudTrail logs in S3, create an Athena external table from event history, configure query results, and run SQL queries to analyze audit and API activity

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

1. Create a CloudTrail trail that delivers logs to an S3 bucket.
2. Verify the CloudTrail bucket and its folder layout.
3. Use CloudTrail Event history to generate an Athena external table definition (DDL).
4. 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.](https://mintcdn.com/kodekloud-c4ac6d9a/pbGljLCZ83hel575/images/AWS-Certified-DevOps-Engineer-Professional/Domain-6-Security-and-Compliance/Demo-Querying-CloudTrail-With-Athena/aws-cloudtrail-quick-trail-s3-note.jpg?fit=max\&auto=format\&n=pbGljLCZ83hel575\&q=85\&s=554eeeac382747594e7a9742dcf1ba37)

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

```text theme={null}
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.](https://mintcdn.com/kodekloud-c4ac6d9a/FCC3Du5gLxWM9z2p/images/AWS-Certified-DevOps-Engineer-Professional/Domain-6-Security-and-Compliance/Demo-Querying-CloudTrail-With-Athena/s3-aws-cloudtrail-logs-objects-view.jpg?fit=max\&auto=format\&n=FCC3Du5gLxWM9z2p\&q=85\&s=10a632426581872f2222ad29dbdb8f43)

## 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."](https://mintcdn.com/kodekloud-c4ac6d9a/pbGljLCZ83hel575/images/AWS-Certified-DevOps-Engineer-Professional/Domain-6-Security-and-Compliance/Demo-Querying-CloudTrail-With-Athena/cloudtrail-event-history-athena-table-created.jpg?fit=max\&auto=format\&n=pbGljLCZ83hel575\&q=85\&s=770af546506264670a32078b3a83bee7)

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.

```sql theme={null}
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:

1. Open **Query settings** (or **Settings**).
2. Choose **Manage** (or edit) to set the results location to an S3 bucket/prefix you own.
3. 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:

```sql theme={null}
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.](https://mintcdn.com/kodekloud-c4ac6d9a/pbGljLCZ83hel575/images/AWS-Certified-DevOps-Engineer-Professional/Domain-6-Security-and-Compliance/Demo-Querying-CloudTrail-With-Athena/aws-athena-cloudtrail-logs-query-results.jpg?fit=max\&auto=format\&n=pbGljLCZ83hel575\&q=85\&s=8d9a1054c817fae75c126535e2fea9bf)

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/](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](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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html)
* Amazon Athena documentation: [https://docs.aws.amazon.com/athena/latest/ug/what-is.html](https://docs.aws.amazon.com/athena/latest/ug/what-is.html)
* Athena pricing: [https://aws.amazon.com/athena/pricing/](https://aws.amazon.com/athena/pricing/)

> **[Watch Video](https://learn.kodekloud.com/user/courses/aws-certified-devops-engineer-professional/module/d51f4260-d612-4dcb-9a91-4eeb6540c6c7/lesson/b97cf891-2784-4f50-b501-6291f9fa6676)**

> **[Practice Lab](https://learn.kodekloud.com/user/courses/aws-certified-devops-engineer-professional/module/d51f4260-d612-4dcb-9a91-4eeb6540c6c7/lesson/a0f86628-7b52-4a41-82fd-f0350ab12215)**


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.