AWS CloudTrail Analysis with Amazon Athena: Organization-Level Setup Guide

This comprehensive guide provides step-by-step instructions for setting up Amazon Athena to query AWS CloudTrail logs at the organization level. This setup enables security investigations, compliance monitoring, and audit analysis across multiple AWS accounts using advanced partition projection for optimal performance.

Prerequisites

• AWS CloudTrail configured and logging to S3
• Amazon Athena service access
• S3 bucket permissions for CloudTrail logs
• IAM permissions for Athena operations
• KMS key permissions for encrypted CloudTrail logs (see Step 1.3)

Step 1: Set Up Amazon Athena

1.1 Configure Athena Query Result Location

Athena requires an S3 location to store query results. You need to set this up before running any queries.

Via AWS Console:

1. Open Amazon Athena console
2. Click Settings tab
3. Click "Manage" next to Query result location
4. Enter S3 path: s3://your-athena-results-bucket/athena-results/
5. Click Save

Via AWS CLI (Alternative):

# Create a workgroup with result location configured
aws athena create-work-group \
    --name cloudtrail-investigation \
    --configuration ResultConfiguration='{OutputLocation=s3://your-athena-results-bucket/athena-results/}'

Note: Replace your-athena-results-bucket with an existing S3 bucket you have write access to.

1.2 Create Database for CloudTrail Analysis

Run this SQL query in Athena to create a dedicated database for CloudTrail tables:

CREATE DATABASE IF NOT EXISTS cloudtrail_analysis
COMMENT 'Database for CloudTrail log analysis and security investigations';

How to run:

1. Open Athena Query Editor
2. Paste the SQL above
3. Click "Run query"

1.3 Configure KMS Permissions for Encrypted CloudTrail Logs (Optional)

When Required: Only if your CloudTrail S3 bucket is encrypted using a KMS key from a different AWS account (common with AWS Control Tower setup).

Common Scenario:

• CloudTrail logs in Log Archive Account (e.g., 123456789012)
• KMS key in Management Account (e.g., 987654321098)
• Running Athena queries from Log Archive Account

Solution: Add KMS key policy in the Management Account to allow the Log Archive Account to decrypt CloudTrail logs.

Steps:

1. Go to AWS KMS console in the Management Account
2. Find the CloudTrail KMS key (check describe-trails output for KmsKeyId)
3. Edit the key policy and add this statement:

{
  "Sid": "Allow Athena to use KMS for decryption",
  "Effect": "Allow",
  "Principal": {
    "AWS": "arn:aws:iam::123456789012:role/service-role/AthenaQueryRole"
  },
  "Action": "kms:Decrypt",
  "Resource": "arn:aws:kms:ap-southeast-3:987654321098:key/your-kms-key-id"
}

Security Best Practice: Use a specific IAM role instead of account root for least privilege access.

Replace with your values:

• 123456789012: Your Log Archive Account ID (where you run Athena)
• 987654321098: Your Management Account ID (where KMS key exists)
• your-kms-key-id: Your actual KMS key ID
• AthenaQueryRole: Your specific IAM role for Athena operations

How to find your KMS key ID:

aws cloudtrail describe-trails --region your-region --query 'trailList[0].KmsKeyId'

Additional Security Requirement: Ensure your Athena service role has S3 permissions to read the CloudTrail bucket:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::aws-controltower-logs-987654321098-ap-southeast-3",
        "arn:aws:s3:::aws-controltower-logs-987654321098-ap-southeast-3/*"
      ]
    }
  ]
}

Step 2: Create Organization-Level CloudTrail Table

Reference: AWS Documentation - Creating a Table for CloudTrail Logs

2.1 Determine Your CloudTrail S3 Location

Before creating the table, you need to find your CloudTrail S3 location structure.

Organization-Level S3 Structure:

s3://bucket-name/prefix/AWSLogs/organization-id/

Location Structure Explanation:

• s3://bucket-name/ - CloudTrail destination S3 bucket
• prefix/ - S3 key prefix (often organization ID for Control Tower)
• AWSLogs/ - Standard AWS logging folder structure
• organization-id/ - Organization ID (repeated from prefix for org-level trails)

How to find your values:

Check via AWS CLI:

aws cloudtrail describe-trails --region your-region

Example CLI Output:

{
    "trailList": [
        {
            "Name": "aws-controltower-BaselineCloudTrail",
            "S3BucketName": "aws-controltower-logs-987654321098-ap-southeast-3",
            "S3KeyPrefix": "o-example12345",
            "IsOrganizationTrail": true
        }
    ]
}

Key Fields for Table Creation:

• S3 Bucket: S3BucketName = aws-controltower-logs-987654321098-ap-southeast-3
• S3 Prefix: S3KeyPrefix = o-example12345
• Organization ID: Check from AWS Organization = o-example12345
• Full Location: s3://aws-controltower-logs-987654321098-ap-southeast-3/o-example12345/AWSLogs/o-example12345/

2.2 Create the Table

CREATE EXTERNAL TABLE cloudtrail_analysis.org_cloudtrail_logs (
    eventversion STRING,
    useridentity STRUCT<
        type: STRING,
        principalid: STRING,
        arn: STRING,
        accountid: STRING,
        invokedby: STRING,
        accesskeyid: STRING,
        username: STRING,
        onbehalfof: STRUCT<
            userid: STRING,
            identitystorearn: STRING>,
        sessioncontext: STRUCT<
            attributes: STRUCT<
                mfaauthenticated: STRING,
                creationdate: STRING>,
            sessionissuer: STRUCT<
                type: STRING,
                principalid: STRING,
                arn: STRING,
                accountid: STRING,
                username: STRING>,
            ec2roledelivery: STRING,
            webidfederationdata: STRUCT<
                federatedprovider: STRING,
                attributes: map<string,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,
    readonly STRING,
    resources ARRAY<STRUCT<
        arn: STRING,
        accountid: STRING,
        type: STRING>>,
    eventtype STRING,
    apiversion STRING,
    recipientaccountid STRING,
    serviceeventdetails STRING,
    sharedeventid STRING,
    vpcendpointid STRING,
    vpcendpointaccountid STRING,
    eventcategory STRING,
    addendum STRUCT<
        reason: STRING,
        updatedfields: STRING,
        originalrequestid: STRING,
        originaleventid: STRING>,
    sessioncredentialfromconsole STRING,
    edgedevicedetails STRING,
    tlsdetails STRUCT<
        tlsversion: STRING,
        ciphersuite: STRING,
        clientprovidedhostheader: STRING>
)
PARTITIONED BY (
    `accountid` string,
    `region` string,
    `timestamp` string
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://your-bucket-name/your-prefix/AWSLogs/your-organization-id/'
TBLPROPERTIES (
    'projection.enabled'='true',
    'projection.timestamp.format'='yyyy/MM/dd',
    'projection.timestamp.interval'='1',
    'projection.timestamp.interval.unit'='DAYS',
    'projection.timestamp.range'='2020/01/01,NOW',
    'projection.timestamp.type'='date',
    'projection.accountid.type'='enum',
    'projection.accountid.values'='123456789012,987654321098',
    'projection.region.type'='enum',
    'projection.region.values'='ap-southeast-1,ap-southeast-3',
    'storage.location.template'='s3://your-bucket-name/your-prefix/AWSLogs/your-organization-id/${accountid}/CloudTrail/${region}/${timestamp}'
);

Replace with your actual values:

• your-bucket-name: S3 bucket from step 2.1
• your-prefix: S3 prefix from step 2.1
• your-organization-id: Organization ID from step 2.1
• projection.accountid.values: Comma-separated list of your AWS account IDs
• projection.region.values: Comma-separated list of regions you want to query

Key Features:

• Partition Projection: Automatically generates partitions without manual MSCK REPAIR
• Date-based Partitioning: Uses timestamp instead of separate year/month/day
• Account and Region Filtering: Efficient querying across multiple accounts and regions
• Dynamic Partition Discovery: No need to manually add partitions for new dates

Step 3: Table Management

3.1 Adding New Account IDs

When you add new AWS accounts to your organization, update the partition projection to include them:

-- Add new account ID to existing list
ALTER TABLE cloudtrail_analysis.org_cloudtrail_logs 
SET TBLPROPERTIES (
    'projection.accountid.values'='123456789012,987654321098,345678901234'
);

How to find new account IDs:

# List all accounts in your organization
aws organizations list-accounts --query 'Accounts[].Id' --output text

Step 4: Example Queries

Reference: AWS Documentation - CloudTrail Log Query Examples

4.1 Basic Verification Query

-- Verify table setup and data availability
SELECT 
    eventtime,
    eventname,
    eventsource,
    sourceipaddress,
    useridentity.username,
    awsregion,
    recipientaccountid
FROM cloudtrail_analysis.org_cloudtrail_logs
WHERE timestamp = '2025/09/30'
LIMIT 10;

4.2 Authentication Events Query

-- Find authentication-related events
SELECT 
    eventtime,
    eventname,
    eventsource,
    sourceipaddress,
    useridentity.username,
    errorcode,
    errormessage,
    recipientaccountid
FROM cloudtrail_analysis.org_cloudtrail_logs
WHERE 
    eventsource = 'signin.amazonaws.com'
    AND timestamp BETWEEN '2025/09/25' AND '2025/09/30'
ORDER BY eventtime DESC;

4.3 Failed Login Analysis

-- Analyze failed login attempts
SELECT 
    eventtime,
    sourceipaddress,
    useridentity.username,
    errormessage,
    recipientaccountid,
    awsregion,
    COUNT(*) OVER (PARTITION BY sourceipaddress) as attempts_from_ip
FROM cloudtrail_analysis.org_cloudtrail_logs
WHERE 
    eventsource = 'signin.amazonaws.com'
    AND eventname = 'ConsoleLogin'
    AND errorcode IS NOT NULL
    AND timestamp BETWEEN '2025/09/25' AND '2025/09/30'
ORDER BY eventtime DESC;

4.4 IP Address Investigation

-- Investigate specific IP addresses
SELECT 
    sourceipaddress,
    eventname,
    eventsource,
    COUNT(*) as event_count,
    MIN(eventtime) as first_seen,
    MAX(eventtime) as last_seen,
    COUNT(DISTINCT useridentity.username) as unique_users,
    COUNT(DISTINCT recipientaccountid) as unique_accounts
FROM cloudtrail_analysis.org_cloudtrail_logs
WHERE 
    sourceipaddress IN ('192.0.2.100', '203.0.113.50')
    AND timestamp BETWEEN '2025/09/25' AND '2025/09/30'
GROUP BY sourceipaddress, eventname, eventsource
ORDER BY sourceipaddress, event_count DESC;

4.5 MFA Usage Analysis

-- Analyze MFA usage patterns
SELECT 
    useridentity.sessioncontext.attributes.mfaauthenticated as mfa_used,
    COUNT(*) as login_count,
    COUNT(DISTINCT useridentity.username) as unique_users,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage
FROM cloudtrail_analysis.org_cloudtrail_logs
WHERE 
    eventsource = 'signin.amazonaws.com'
    AND eventname = 'ConsoleLogin'
    AND errorcode IS NULL
    AND timestamp BETWEEN '2025/09/25' AND '2025/09/30'
GROUP BY useridentity.sessioncontext.attributes.mfaauthenticated
ORDER BY login_count DESC;

Conclusion

This guide provides a production-ready solution for analyzing AWS CloudTrail logs across your entire organization using Amazon Athena with advanced partition projection. The setup enables organization-wide security investigations, compliance monitoring, and audit analysis through a single table that automatically discovers partitions and scales efficiently as you add new AWS accounts. With the provided table structure and example queries, you have a robust foundation for comprehensive security monitoring that eliminates manual partition management while delivering fast query performance and cost-effective analysis of your CloudTrail data.