# Setting Up CloudTrail Operational Auditing with Athena Queries: A Complete Guide

> Master CloudTrail operational auditing with Athena queries. Learn to set up multi-region trails, send logs to S3, and use SQL for incident investigation and API activity analysis.

- Repository: [Amazon Web Services/agent-toolkit-for-aws](https://github.com/aws/agent-toolkit-for-aws)
- Tags: how-to-guide
- Published: 2026-07-02

---

**You can set up CloudTrail operational auditing by creating a multi-region trail that delivers logs to S3, then querying those logs directly with Athena using standard SQL to investigate security incidents and API activity.**

AWS CloudTrail captures management and data events across your AWS infrastructure, but the CloudTrail console UI has limits for historical analysis. By storing logs in Amazon S3 and creating an external table in **Amazon Athena**, you enable long-term, searchable operational auditing using familiar SQL syntax. The **Agent Toolkit for AWS** provides ready-made guidance and reference implementations for this architecture in [`skills/core-skills/aws-observability/references/cloudtrail.md`](https://github.com/aws/agent-toolkit-for-aws/blob/main/skills/core-skills/aws-observability/references/cloudtrail.md) and [`skills/specialized-skills/operations-skills/setting-up-cloudtrail-multi-region/references/cloudtrail-multi-region-setup.md`](https://github.com/aws/agent-toolkit-for-aws/blob/main/skills/specialized-skills/operations-skills/setting-up-cloudtrail-multi-region/references/cloudtrail-multi-region-setup.md).

## Architecture Overview

The recommended architecture for **CloudTrail operational auditing** follows a three-stage pipeline:

1. **CloudTrail → S3** – The trail writes compressed JSON log files to `s3://<bucket>/AWSLogs/<account-id>/CloudTrail/<region>/<yyyy>/<mm>/<dd>/`. Each file contains up to 10 MB of events.
2. **Athena → S3** – Athena reads those files directly without requiring ETL. A single external table maps the JSON fields (`eventTime`, `eventName`, `userIdentity`, `sourceIPAddress`, etc.).
3. **Operational Auditing** – Because Athena can query the full history (90 days of management events are free, while data events are retained as long as the bucket exists), you can answer "who did what, when" with simple SQL, avoiding the limits of the CloudTrail console UI.

## Prerequisites and Setup Steps

### Create an S3 Bucket for CloudTrail Logs

Before creating the trail, you need an S3 bucket with versioning and encryption enabled. According to the multi-region setup SOP in [`skills/specialized-skills/operations-skills/setting-up-cloudtrail-multi-region/references/cloudtrail-multi-region-setup.md`](https://github.com/aws/agent-toolkit-for-aws/blob/main/skills/specialized-skills/operations-skills/setting-up-cloudtrail-multi-region/references/cloudtrail-multi-region-setup.md), configure the bucket as follows:

```bash
aws s3api create-bucket \
    --bucket my-org-cloudtrail-logs-2024 \
    --region us-east-1

aws s3api put-bucket-versioning \
    --bucket my-org-cloudtrail-logs-2024 \
    --versioning-configuration Status=Enabled \
    --region us-east-1

aws s3api put-bucket-encryption \
    --bucket my-org-cloudtrail-logs-2024 \
    --server-side-encryption-configuration \
        Rules='[{"ApplyServerSideEncryptionByDefault":{"SSEAlgorithm":"aws:kms","KMSMasterKeyID":"1234abcd-12ab-34cd-56ef-1234567890ab"}}]' \
    --region us-east-1

```

You must also attach a bucket policy allowing CloudTrail to write logs. See [`skills/specialized-skills/storage-skills/securing-s3-buckets/SKILL.md`](https://github.com/aws/agent-toolkit-for-aws/blob/main/skills/specialized-skills/storage-skills/securing-s3-buckets/SKILL.md) for detailed bucket security configurations.

### Configure a Multi-Region CloudTrail Trail

Create a trail that captures global service events and enables log file validation. The `create-trail` command in [`cloudtrail-multi-region-setup.md`](https://github.com/aws/agent-toolkit-for-aws/blob/main/cloudtrail-multi-region-setup.md) specifies the following required parameters:

```bash
aws cloudtrail create-trail \
    --name org-audit-trail \
    --s3-bucket-name my-org-cloudtrail-logs-2024 \
    --include-global-service-events \
    --is-multi-region-trail \
    --enable-log-file-validation \
    --cloud-watch-logs-log-group-arn arn:aws:logs:us-east-1:123456789012:log-group/CloudTrail/APILogs \
    --cloud-watch-logs-role-arn arn:aws:iam::123456789012:role/CloudTrail-CloudWatchLogs-Role-org-audit-trail \
    --region us-east-1

aws cloudtrail start-logging --name org-audit-trail --region us-east-1

```

To audit data-plane activity such as S3 object access or Lambda invocations, enable **data events** using `put-event-selectors`:

```bash
aws cloudtrail put-event-selectors \
    --trail-name org-audit-trail \
    --event-selectors '[{
        "ReadWriteType":"All",
        "IncludeManagementEvents":true,
        "DataResources":[{
            "Type":"AWS::S3::Object",
            "Values":["arn:aws:s3:::my-org-cloudtrail-logs-2024"]
        }]
    }]'

```

### Verify Trail Logging Status

Confirm the trail is active and logging:

```bash
aws cloudtrail get-trail-status --name org-audit-trail --region us-east-1

```

This verification step is documented in step 8 of the multi-region setup reference.

## Configuring Athena for CloudTrail Log Analysis

### Create the Athena Database and External Table

In [`skills/core-skills/aws-observability/references/cloudtrail.md`](https://github.com/aws/agent-toolkit-for-aws/blob/main/skills/core-skills/aws-observability/references/cloudtrail.md), the recommended `CREATE EXTERNAL TABLE` statement maps the CloudTrail JSON schema to Athena columns. The table must be **partitioned** by `year`, `month`, and `day` to optimize query performance:

```sql
CREATE DATABASE IF NOT EXISTS cloudtrail_audit;

CREATE EXTERNAL TABLE IF NOT EXISTS cloudtrail_audit.cloudtrail_logs (
  eventVersion STRING,
  userIdentity STRUCT<
    type: STRING,
    principalId: STRING,
    arn: STRING,
    accountId: STRING,
    invokedBy: STRING,
    accessKeyId: STRING,
    userName: STRING,
    sessionContext: STRUCT<
      attributes: STRUCT<creationDate: STRING, mfaAuthenticated: STRING>,
      sessionIssuer: STRUCT<type: STRING, principalId: STRING, arn: STRING, accountId: 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,
  readOnly BOOLEAN,
  recipientAccountId STRING,
  serviceEventDetails STRING,
  sharedEventID STRING,
  vpcEndpointId STRING
)
PARTITIONED BY (year STRING, month STRING, day STRING)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ('ignore.malformed.json'='true')
LOCATION 's3://my-org-cloudtrail-logs-2024/AWSLogs/123456789012/CloudTrail/';

```

### Load Partitions

After creating the table, load the existing partitions to make historical data available:

```sql
MSCK REPAIR TABLE cloudtrail_audit.cloudtrail_logs;

```

## Operational Athena Queries for Security Auditing

With the table configured, you can run ad-hoc queries. Always scope queries by `year`, `month`, and `day` partitions to minimize data scanned and control costs.

### Identify Who Deleted an S3 Bucket

```sql
SELECT eventTime, userIdentity.arn, sourceIPAddress, eventName, requestParameters
FROM cloudtrail_audit.cloudtrail_logs
WHERE eventName = 'DeleteBucket'
  AND year = '2026' AND month = '04' AND day = '20'
ORDER BY eventTime DESC
LIMIT 100;

```

### Track Security Group Configuration Changes

```sql
SELECT eventTime, userIdentity.userName, eventName, awsRegion, requestParameters
FROM cloudtrail_audit.cloudtrail_logs
WHERE eventName LIKE '%SecurityGroup%'
  AND year = '2026'
ORDER BY eventTime DESC;

```

### Audit Data-Plane Access (S3 GetObject)

This query requires data events to be enabled on the trail:

```sql
SELECT eventTime, userIdentity.userName, sourceIPAddress, requestParameters.bucketName, requestParameters.key
FROM cloudtrail_audit.cloudtrail_logs
WHERE eventName = 'GetObject'
  AND year = '2026'
ORDER BY eventTime DESC
LIMIT 50;

```

### Analyze Failed API Calls by User

```sql
SELECT userIdentity.userName, errorCode, count(*) AS failures
FROM cloudtrail_audit.cloudtrail_logs
WHERE errorCode IS NOT NULL
  AND year = '2026'
GROUP BY userIdentity.userName, errorCode
ORDER BY failures DESC;

```

## Cost Optimization Considerations

Understanding the cost structure helps prevent unexpected charges:

- **Management events** are free for the first copy per region.
- **Data events** and **Insights** incur per-100k-event charges.
- **Athena** charges per terabyte of data scanned; use partitioning (`year`, `month`, `day`) and predicate pushdown with `WHERE eventTime BETWEEN …` clauses to limit scans.

The *Cost Implications* section in [`skills/specialized-skills/operations-skills/setting-up-cloudtrail-multi-region/references/cloudtrail-multi-region-setup.md`](https://github.com/aws/agent-toolkit-for-aws/blob/main/skills/specialized-skills/operations-skills/setting-up-cloudtrail-multi-region/references/cloudtrail-multi-region-setup.md) provides detailed pricing breakdowns.

## Summary

- **CloudTrail operational auditing** requires a multi-region trail delivering logs to S3, combined with an Athena external table mapping the JSON structure.
- **Partitioning** by date is critical for query performance and cost control when using Athena.
- **Data events** must be explicitly enabled to audit S3 object access and Lambda invocations, unlike management events which are captured by default.
- The **Agent Toolkit for AWS** provides complete reference implementations in [`cloudtrail-multi-region-setup.md`](https://github.com/aws/agent-toolkit-for-aws/blob/main/cloudtrail-multi-region-setup.md) and [`cloudtrail.md`](https://github.com/aws/agent-toolkit-for-aws/blob/main/cloudtrail.md), including bucket policies, IAM roles, and sample SQL queries.

## Frequently Asked Questions

### What is the difference between management events and data events in CloudTrail?

**Management events** capture control-plane operations like `CreateBucket`, `RunInstances`, or `DeleteSecurityGroup`, and the first copy per region is free. **Data events** capture data-plane activity such as `GetObject` or `Invoke`, and they incur additional charges per 100,000 events. You must explicitly configure event selectors using `put-event-selectors` to enable data event logging.

### How do I optimize Athena query costs for CloudTrail logs?

Always include **partition columns** (`year`, `month`, `day`) in your `WHERE` clauses to limit the data scanned. For example, use `WHERE year = '2026' AND month = '04'` instead of filtering on `eventTime` alone. Athena charges per terabyte scanned, so narrow time ranges and specific `eventName` filters significantly reduce costs.

### Can I query CloudTrail logs from multiple AWS accounts in Athena?

Yes. Point the Athena table `LOCATION` to a bucket that receives logs from multiple accounts via **organization trails**, or use **AWS Glue** crawlers to discover logs across multiple S3 prefixes. Ensure the IAM role running the queries has read access to all relevant `AWSLogs/<account-id>/` prefixes in the bucket.

### What permissions does Athena need to read CloudTrail S3 logs?

Athena requires the following permissions: `s3:GetObject` and `s3:ListBucket` for the CloudTrail log bucket, and `glue:*` permissions (or Athena-specific permissions) to read the table metadata from the AWS Glue Data Catalog. The bucket policy must also grant CloudTrail permissions to write logs, as defined in [`skills/specialized-skills/operations-skills/setting-up-cloudtrail-multi-region/references/cloudtrail-multi-region-setup.md`](https://github.com/aws/agent-toolkit-for-aws/blob/main/skills/specialized-skills/operations-skills/setting-up-cloudtrail-multi-region/references/cloudtrail-multi-region-setup.md).