Day 2 - CloudTrail Lake Advanced: SQL Audit Analysis, Insights Anomaly Detection, Cross-Account Queries

CloudTrail's S3 + Athena pattern is powerful but has friction. Building Athena tables, setting up partitions, writing queries, and interpreting results consume time when audits are urgent. CloudTrail Lake removes this friction. It stores event data directly in a dedicated lake and runs SQL instantly from the console. Today, from the SOA operator's perspective, we dive deep into Event Data Store design, SQL query patterns, how ML-based Insights detects anomalies, cross-account usage at organizational scale, and practical implementation.

CloudTrail Lake Design Philosophy: Unified Storage and Analysis

Traditional Trail (S3 storage) separates "storage and analysis." Events accumulate in S3 as JSON.gz, then Athena reads and queries them. This separation provides flexibility but adds operational burden: Athena table and partition management.

CloudTrail Lake ingests events directly into Columnar Storage. Columnar format dramatically reduces I/O when scanning specific fields. If you filter only eventName = 'ConsoleLogin', other columns aren't read—queries are faster and cheaper.

⚠️ Pitfall: Lake and Trail are billed separately. If same events go to both Trail (S3) and Lake, you pay double. For security audit: Lake. For long-term archive + SIEM integration: Trail+S3. For both: understand the dual cost. Organization Trail lowers Trail cost, but Lake Ingest cost is separate.

Event Data Store (EDS): Core Container of Lake

Event Data Store is the unit that stores events within Lake. One EDS holds one stream of events. Best practice: create multiple EDS—separate Management Events, Data Events, and Insights Events.

EDS Core Parameters

aws cloudtrail create-event-data-store \
  --name "org-management-events-7yr" \
  --advanced-event-selectors '[
    {
      "Name": "All Management Events",
      "FieldSelectors": [
        {"Field": "eventCategory", "Equals": ["Management"]}
      ]
    }
  ]' \
  --retention-period 2555 \
  --multi-region-enabled \
  --organization-enabled \
  --termination-protection-enabled \
  --kms-key-id arn:aws:kms:ap-northeast-2:111122223333:key/mrk-abc123

💡 Related Theory: EDS termination-protection is the same pattern as database Delete Protection. RDS DeletionProtection, S3 Object Lock, DynamoDB DeletionProtection follow the same principle. Audit data must "not be accidentally deleted"—this requirement originates from regulations (PCI-DSS 10.7: 1+ year log retention, HIPAA §164.312: 6+ year retention). Disabling termination-protection requires a separate API call first, enforcing "two-step deletion."

Advanced Event Selector: Precise Filtering

Fine-filter EDS events to control costs.

# Collect Data Events only from sensitive S3 buckets
aws cloudtrail create-event-data-store \
  --name "sensitive-s3-data-events" \
  --advanced-event-selectors '[
    {
      "Name": "S3 PII buckets - Object level",
      "FieldSelectors": [
        {"Field": "eventCategory", "Equals": ["Data"]},
        {"Field": "resources.type", "Equals": ["AWS::S3::Object"]},
        {"Field": "resources.ARN", "StartsWith": [
          "arn:aws:s3:::pii-data-prod/",
          "arn:aws:s3:::financial-records/"
        ]},
        {"Field": "readOnly", "Equals": ["false"]}
      ]
    }
  ]' \
  --retention-period 365

readOnly: false captures writes (PutObject, DeleteObject) only, drastically reducing data volume vs. all read events.

Filterable fields:

🔍 Deeper Dive: Data Events are disabled by default; enabling incurs large costs. S3 object logging on large buckets generates billions of events daily. Advanced Event Selector narrowing to "sensitive buckets only," "writes only," "specific prefix only" is critical for cost control. Unfiltered S3 Data Events collection can cost thousands monthly in Ingest charges.

SQL Query Advanced: Real-World Audit Scenarios

CloudTrail Lake SQL is Presto/Trino-based. Close to standard ANSI SQL, supporting JSON parsing, Window Functions, and aggregates.

Basic Query Structure

SELECT <column list>
FROM <event-data-store-id>
WHERE <conditions>
  AND eventTime > DATE_ADD('day', -N, NOW())
ORDER BY <sorting>
LIMIT <rows>;

<event-data-store-id> is the actual UUID-form EDS ID (e.g., 1234abcd-12ab-34cd-56ef-1234567890ab).

Real Query Patterns

Pattern 1: Root Account Login Tracking (Security First Priority)

SELECT
  eventTime,
  userIdentity.type,
  sourceIPAddress,
  userAgent,
  additionalEventData
FROM 1234abcd-12ab-34cd-56ef-1234567890ab
WHERE eventName = 'ConsoleLogin'
  AND userIdentity.type = 'Root'
  AND eventTime > DATE_ADD('day', -90, NOW())
ORDER BY eventTime DESC;

Root logins demand immediate alert regardless. Pair with EventBridge Rule + SNS.

Pattern 2: IAM Privilege Escalation (Insider Threat)

SELECT
  eventTime,
  userIdentity.arn AS actor,
  eventName,
  requestParameters,
  sourceIPAddress
FROM 1234abcd-12ab-34cd-56ef-1234567890ab
WHERE eventName IN (
  'AttachRolePolicy',
  'PutRolePolicy',
  'CreatePolicy',
  'CreatePolicyVersion',
  'SetDefaultPolicyVersion',
  'CreateAccessKey',
  'UpdateAssumeRolePolicy',
  'AddUserToGroup',
  'PutGroupPolicy',
  'AttachGroupPolicy'
)
  AND eventTime > DATE_ADD('day', -7, NOW())
ORDER BY eventTime DESC;

Pattern 3: Large S3 Download Detection (Data Exfil Suspect)

SELECT
  userIdentity.arn,
  requestParameters.bucketName,
  COUNT(*) AS download_count,
  MIN(eventTime) AS first_download,
  MAX(eventTime) AS last_download
FROM 1234abcd-12ab-34cd-56ef-1234567890ab
WHERE eventName IN ('GetObject', 'HeadObject')
  AND eventTime > DATE_ADD('hour', -24, NOW())
GROUP BY userIdentity.arn, requestParameters.bucketName
HAVING COUNT(*) > 1000
ORDER BY download_count DESC;

Pattern 4: Abnormal AssumeRole (Credential Theft Suspect)

SELECT
  userIdentity.arn AS source_identity,
  requestParameters.roleArn AS assumed_role,
  COUNT(*) AS assume_count,
  COUNT(DISTINCT sourceIPAddress) AS distinct_ips,
  MIN(eventTime) AS first_seen,
  MAX(eventTime) AS last_seen
FROM 1234abcd-12ab-34cd-56ef-1234567890ab
WHERE eventName = 'AssumeRole'
  AND errorCode IS NULL
  AND eventTime > DATE_ADD('hour', -1, NOW())
GROUP BY userIdentity.arn, requestParameters.roleArn
HAVING COUNT(*) > 20 OR COUNT(DISTINCT sourceIPAddress) > 3
ORDER BY assume_count DESC;

Rapid role assumption from multiple IPs signals credential share or theft.

Pattern 5: Specific User Activity Timeline (Incident Investigation)

SELECT
  eventTime,
  eventSource,
  eventName,
  sourceIPAddress,
  userAgent,
  requestParameters,
  responseElements,
  errorCode,
  errorMessage
FROM 1234abcd-12ab-34cd-56ef-1234567890ab
WHERE (
  userIdentity.arn LIKE '%:user/alice%'
  OR userIdentity.sessionContext.sessionIssuer.arn LIKE '%:user/alice%'
)
  AND eventTime BETWEEN '2026-05-01T00:00:00Z' AND '2026-05-27T23:59:59Z'
ORDER BY eventTime ASC
LIMIT 500;

📚 Case Study: 2024 fintech startup insider threat. Departed employee's credentials expired after 3 days post-departure. That employee downloaded customer data from S3 for 3 days. Query Pattern 3 detected anomalous download. GetObject jumped from 10/day average to 50,000/day post-departure. Insights auto-detected the anomaly and EventBridge → Slack alert triggered in 15 minutes; IAM Key disabled within 15 minutes total response time.

Lake Direct Query vs. Athena: Selection

🔍 Deeper Dive: CloudTrail Lake query engine (Presto/Trino) has 1,000-row result maximum. For more results, paginate with separate LIMIT queries or use Lake's "export results to S3" feature. Query cost is per data scanned (TB at $5); narrowing eventTime range and selecting only needed columns cuts costs. SELECT * scans all; columnar storage benefits maximized by naming specific columns.

CloudTrail Insights: ML-Based Anomaly Detection Internals

Two Insights Types

ApiCallRateInsight: Detects when specific API call rate spikes abnormally.

• Example: RunInstances normally 5/hour → suddenly 500/hour → crypto mining attack or credential theft

ApiErrorRateInsight: Detects when specific API error rate spikes abnormally.

• Example: GetObject AccessDenied normally 1/minute → suddenly 1,000/minute → IAM policy error or brute-force access

Internal Operation Mechanism

[Stage 1: Baseline Learning]
Insights activated → 7 days normal pattern learning
Calculate statistical distribution per API per hour
(mean, std deviation, seasonality, etc.)

[Stage 2: Real-time Comparison]
Every minute, compare current pattern to Baseline
Statistically significant deviation → Insight event generated

[Stage 3: Insight Event Creation]
Insight event → Trail S3 (separate storage) + EventBridge delivery
source: aws.cloudtrail
detail-type: AWS Insight via CloudTrail

💡 Related Theory: CloudTrail Insights anomaly detection is Time Series Anomaly Detection. Originates from Statistical Process Control (Shewhart Control Chart, 1924): "observations outside normal variance range = anomaly." Modern ML uses Isolation Forest, LSTM Autoencoder, or Random Cut Forest (Amazon 2016, used by CloudWatch Anomaly Detection). AWS doesn't disclose Insights algorithm, but 7-day learning + minute-level evaluation suggests statistical time-series basis.

Insights Activation and Cost

# Enable Insights on Trail
aws cloudtrail put-insight-selectors \
  --trail-name "my-org-trail" \
  --insight-selectors '[
    {"InsightType": "ApiCallRateInsight"},
    {"InsightType": "ApiErrorRateInsight"}
  ]'
 
# Enable Insights directly on EDS
aws cloudtrail create-event-data-store \
  --name "insights-eds" \
  --advanced-event-selectors '[
    {
      "Name": "Insight Events",
      "FieldSelectors": [
        {"Field": "eventCategory", "Equals": ["Insight"]}
      ]
    }
  ]' \
  --retention-period 90

Insights cost: $0.35 per 100,000 Management Events analyzed. Large account with 1M Management Events/day = $3.50/day = $105/month.

⚠️ Two Pitfalls:

1. Insights needs 7+ days baseline construction. Enabling on new Trail immediately won't detect anomalies the first week. Pre-enable in important environments 7+ days before.
2. Insights analyzes Management Events only. Abnormal S3 Data Event spikes (sudden GetObject surge) are NOT detected by Insights. Use CloudWatch Metric Filter + Alarm or custom Lambda for such cases.

EventBridge + Insights: Auto-Response Pipeline

// EventBridge Rule for CloudTrail Insights
{
  "source": ["aws.cloudtrail"],
  "detail-type": ["AWS Insight via CloudTrail"],
  "detail": {
    "insightDetails": {
      "insightType": ["ApiCallRateInsight"],
      "insightContext": {
        "requestFrequency": [{
          "average": [{"numeric": [">", 100]}]
        }]
      }
    }
  }
}

Key fields in Insights event structure:

• insightDetails.insightType: ApiCallRateInsight or ApiErrorRateInsight
• insightDetails.eventName: API name where anomaly detected
• insightDetails.insightContext.requestFrequency.average: anomaly period average call count
• insightDetails.insightContext.baselineRequestFrequency.average: normal period average

Organization Trail + Lake Integration: Enterprise Audit Architecture

To audit all of AWS Organizations, not single account, use Organization Trail + Organization-enabled EDS.

Organization Trail Setup

# Run in management account or delegated CloudTrail admin account
aws cloudtrail create-trail \
  --name "org-master-trail" \
  --s3-bucket-name "org-audit-logs-archive" \
  --is-organization-trail \
  --is-multi-region-trail \
  --enable-log-file-validation \
  --include-global-service-events \
  --cloud-watch-logs-log-group-arn arn:aws:logs:ap-northeast-2:111122223333:log-group:/aws/cloudtrail/org \
  --cloud-watch-logs-role-arn arn:aws:iam::111122223333:role/CloudTrailToCloudWatchLogs
 
aws cloudtrail start-logging --name "org-master-trail"

Organization Trail traits:

• Created in management account, auto-applies to all member accounts
• Member accounts cannot delete/modify (management account only)
• New account added to Organization → Trail auto-applied
• Trusted Access delegation → Security account can manage

# Delegate CloudTrail management to security account
aws organizations enable-aws-service-access \
  --service-principal cloudtrail.amazonaws.com
 
aws cloudtrail register-organization-delegated-admin \
  --member-account-id 999988887777  # Audit account ID

Cross-Account Lake Query

Organization-enabled EDS queries all member account events in single EDS.

-- Root account usage across entire Organization in last 30 days
SELECT
  recipientAccountId AS account_id,
  eventTime,
  userIdentity.type,
  sourceIPAddress,
  userAgent
FROM org-eds-uuid-here
WHERE userIdentity.type = 'Root'
  AND eventTime > DATE_ADD('day', -30, NOW())
ORDER BY eventTime DESC;
 
-- IAM changes per account aggregate (enterprise compliance status)
SELECT
  recipientAccountId AS account_id,
  COUNT(*) AS iam_changes,
  COUNT(DISTINCT userIdentity.arn) AS distinct_actors
FROM org-eds-uuid-here
WHERE eventSource = 'iam.amazonaws.com'
  AND eventName NOT LIKE 'Get%'
  AND eventName NOT LIKE 'List%'
  AND eventName NOT LIKE 'Describe%'
  AND eventTime > DATE_ADD('day', -7, NOW())
GROUP BY recipientAccountId
ORDER BY iam_changes DESC;

recipientAccountId field identifies which account the event came from.

📚 Case Study: 2023 AWS re:Invent speaker story. Global finance company running 50 AWS accounts. Before: investigated security incident sequentially across 50 Athena accounts = 1 day. After: Organization-enabled CloudTrail Lake → single SQL query investigation = 10 minutes. Average incident investigation time (MTTR) 75% reduction.

CloudTrail Lake vs Athena: When to Use What

SOA operator's situation-based tool choice: