AWS CloudTrail Threat Hunting with Athena: Query Patterns for Detecting IAM Abuse, Lateral Movement, and Data Exfiltration

Before querying, partition the CloudTrail table for performance:

-- Create the CloudTrail table in Athena
CREATE EXTERNAL TABLE cloudtrail_logs (
  eventVersion STRING,
  userIdentity STRUCT<
    type: STRING,
    principalId: STRING,
    arn: STRING,
    accountId: STRING,
    invokedBy: STRING,
    accessKeyId: STRING,
    userName: STRING,
    sessionContext: STRUCT<
      sessionIssuer: STRUCT<
        type: STRING,
        principalId: STRING,
        arn: STRING,
        accountId: STRING,
        userName: STRING
      >,
      attributes: STRUCT<
        mfaAuthenticated: STRING,
        creationDate: STRING
      >
    >
  >,
  eventTime STRING,
  eventSource STRING,
  eventName STRING,
  awsRegion STRING,
  sourceIPAddress STRING,
  userAgent STRING,
  errorCode STRING,
  errorMessage STRING,
  requestParameters STRING,
  responseElements STRING,
  resources ARRAY<STRUCT<arn:STRING,accountId:STRING,type:STRING>>,
  recipientAccountId STRING
)
PARTITIONED BY (region STRING, year STRING, month STRING, day STRING)
ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://your-cloudtrail-bucket/AWSLogs/YOUR_ACCOUNT_ID/CloudTrail/';

The quickest approach is to use the AWS-provided CloudFormation template that creates the table automatically: in the Athena console, go to Query editor > CloudTrail > Create table. Load partitions for the time range you want to query:

MSCK REPAIR TABLE cloudtrail_logs;

The highest-priority CloudTrail events for IAM abuse detection:

Find new IAM access keys created (possible persistence):

SELECT
  eventTime,
  userIdentity.userName AS actor,
  userIdentity.accessKeyId AS actor_key,
  sourceIPAddress,
  json_extract_scalar(requestParameters, '$.userName') AS target_user
FROM cloudtrail_logs
WHERE eventName = 'CreateAccessKey'
  AND year = '2026' AND month = '06'
ORDER BY eventTime DESC;

New access keys created for users other than the actor are suspicious. An attacker who compromised one IAM key often creates a second key as persistence.

Find inline policy attachments (bypass SCP-controlled managed policies):

SELECT
  eventTime,
  userIdentity.userName AS actor,
  sourceIPAddress,
  json_extract_scalar(requestParameters, '$.userName') AS target_user,
  json_extract_scalar(requestParameters, '$.policyName') AS policy_name
FROM cloudtrail_logs
WHERE eventName IN ('PutUserPolicy', 'PutRolePolicy', 'PutGroupPolicy')
  AND year = '2026' AND month = '06'
ORDER BY eventTime DESC;

Inline policy attachments that grant AdministratorAccess or iam:* permissions are critical findings.

Find cross-account role assumptions:

SELECT
  eventTime,
  userIdentity.principalId,
  userIdentity.arn AS source_identity,
  json_extract_scalar(requestParameters, '$.roleArn') AS assumed_role,
  sourceIPAddress
FROM cloudtrail_logs
WHERE eventName = 'AssumeRole'
  AND userIdentity.accountId != recipientAccountId
  AND year = '2026' AND month = '06'
ORDER BY eventTime DESC;

S3 GetObject events are the primary indicator of data exfiltration. The challenge is volume: legitimate applications generate millions of GetObject calls. Use anomaly-based queries rather than absolute threshold queries:

Find IAM users or roles accessing S3 buckets they have never accessed before (last 30 days vs prior 30 days):

-- Current 30 days
WITH current_period AS (
  SELECT DISTINCT
    userIdentity.arn,
    json_extract_scalar(requestParameters, '$.bucketName') AS bucket
  FROM cloudtrail_logs
  WHERE eventName = 'GetObject'
    AND eventSource = 's3.amazonaws.com'
    AND year = '2026' AND month = '06'
),
-- Prior 30 days baseline
prior_period AS (
  SELECT DISTINCT
    userIdentity.arn,
    json_extract_scalar(requestParameters, '$.bucketName') AS bucket
  FROM cloudtrail_logs
  WHERE eventName = 'GetObject'
    AND eventSource = 's3.amazonaws.com'
    AND year = '2026' AND month = '05'
)
SELECT c.arn, c.bucket
FROM current_period c
LEFT JOIN prior_period p ON c.arn = p.arn AND c.bucket = p.bucket
WHERE p.arn IS NULL;

Find high-volume S3 GetObject calls from a single identity in a short window:

SELECT
  userIdentity.arn,
  sourceIPAddress,
  date_trunc('hour', from_iso8601_timestamp(eventTime)) AS hour_bucket,
  COUNT(*) AS get_count,
  COUNT(DISTINCT json_extract_scalar(requestParameters, '$.bucketName')) AS distinct_buckets
FROM cloudtrail_logs
WHERE eventName = 'GetObject'
  AND eventSource = 's3.amazonaws.com'
  AND year = '2026' AND month = '06'
GROUP BY 1, 2, 3
HAVING COUNT(*) > 1000
ORDER BY get_count DESC;

Console logins without MFA, from unusual geolocations, or outside business hours are early indicators of credential compromise:

Find console logins without MFA:

SELECT
  eventTime,
  userIdentity.userName,
  sourceIPAddress,
  json_extract_scalar(additionalEventData, '$.MFAUsed') AS mfa_used,
  awsRegion
FROM cloudtrail_logs
WHERE eventName = 'ConsoleLogin'
  AND json_extract_scalar(additionalEventData, '$.MFAUsed') = 'No'
  AND errorCode IS NULL
  AND year = '2026' AND month = '06'
ORDER BY eventTime DESC;

Find root account console logins (should be near zero):

SELECT
  eventTime,
  userIdentity.type,
  sourceIPAddress,
  json_extract_scalar(additionalEventData, '$.MFAUsed') AS mfa_used
FROM cloudtrail_logs
WHERE eventName = 'ConsoleLogin'
  AND userIdentity.type = 'Root'
  AND year = '2026' AND month = '06'
ORDER BY eventTime DESC;

Any root login without a documented break-glass reason is a critical finding.

Find API calls from unusual source IPs for a given IAM user (new IP in last 7 days):

WITH baseline AS (
  SELECT DISTINCT userIdentity.userName, sourceIPAddress
  FROM cloudtrail_logs
  WHERE year = '2026' AND month = '05'
    AND userIdentity.type = 'IAMUser'
),
recent AS (
  SELECT DISTINCT userIdentity.userName, sourceIPAddress
  FROM cloudtrail_logs
  WHERE year = '2026' AND month = '06'
    AND userIdentity.type = 'IAMUser'
)
SELECT r.userName, r.sourceIPAddress AS new_ip
FROM recent r
LEFT JOIN baseline b ON r.userName = b.userName AND r.sourceIPAddress = b.sourceIPAddress
WHERE b.userName IS NULL
  AND r.sourceIPAddress NOT LIKE '10.%'
  AND r.sourceIPAddress NOT LIKE '172.16.%'
ORDER BY r.userName;

GuardDuty provides pre-built detection rules that surface known-bad patterns (cryptocurrency mining, Tor exit node usage, S3 bucket policy changes, IAM credential exfiltration). CloudTrail provides the full API call context that GuardDuty findings summarize.

When a GuardDuty finding fires, use CloudTrail to expand the investigation:

-- For a GuardDuty finding about a specific IAM principal:
-- Retrieve all API calls by that principal in the surrounding 4-hour window
SELECT
  eventTime,
  eventName,
  eventSource,
  sourceIPAddress,
  awsRegion,
  errorCode,
  json_extract_scalar(requestParameters, '$.bucketName') AS s3_bucket,
  json_extract_scalar(requestParameters, '$.roleName') AS iam_role
FROM cloudtrail_logs
WHERE userIdentity.arn = 'arn:aws:iam::123456789012:user/compromised-user'
  AND eventTime BETWEEN '2026-06-10T14:00:00Z' AND '2026-06-10T18:00:00Z'
ORDER BY eventTime ASC;

Build a threat hunt workflow:

1. Weekly: run the high-volume S3 GetObject query and new IAM access key query, review results
2. Daily: run the root login and console login without MFA queries, alert on any result
3. On-demand for GuardDuty findings: run the principal timeline query for the flagged identity
4. Monthly: run the new S3 access query (new buckets accessed by existing identities) as a low-and-slow exfiltration hunt

For automation, use AWS Lambda triggered by an EventBridge schedule to run these queries and send results to a Slack channel or SIEM via SNS.