MCP Server CloudTrail Lake — SQL queries for API call history, cross-account audit

CloudTrail Lake vs S3 + Athena — when to use each

Both options allow SQL queries over CloudTrail event history. The right choice depends on your retention requirements, query frequency, and operational overhead tolerance:

For MCP server use cases: if you are starting a new deployment and want the simplest possible audit setup with zero infrastructure management, use Lake. If you need to retain audit logs for more than 7 years, need to JOIN CloudTrail events with application tables in Athena, or need the lowest possible storage cost, use S3 + Athena.

Creating a Lake event data store

# Create a Lake event data store — retains 7 years, all management events
aws cloudtrail create-event-data-store \
  --name mcp-audit-lake \
  --retention-period 2557 \
  --multi-region-enabled \
  --organization-enabled \   # only if using AWS Organizations
  --advanced-event-selectors '[
    {
      "Name": "AllManagementEvents",
      "FieldSelectors": [
        {"Field": "eventCategory", "Equals": ["Management"]}
      ]
    },
    {
      "Name": "DynamoDB-WriteData",
      "FieldSelectors": [
        {"Field": "eventCategory", "Equals": ["Data"]},
        {"Field": "resources.type", "Equals": ["AWS::DynamoDB::Table"]},
        {"Field": "readOnly", "Equals": ["false"]}
      ]
    }
  ]'

# Note the EventDataStoreArn — needed for all queries
# Format: arn:aws:cloudtrail:us-east-1:123456789012:eventdatastore/EXAMPLE-1234-5678-ABCD

In CDK, Lake data stores are created with aws_cloudtrail.CfnEventDataStore:

import { aws_cloudtrail as cfnTrail } from "aws-cdk-lib";

new cfnTrail.CfnEventDataStore(this, "McpLake", {
  name: "mcp-audit-lake",
  retentionPeriod: 2557,
  multiRegionEnabled: true,
  terminationProtectionEnabled: true,
  advancedEventSelectors: [
    {
      name: "ManagementAndDynamoDB",
      fieldSelectors: [
        { field: "eventCategory", equals: ["Management", "Data"] },
        // Data events further filtered — Management selector is implicit
      ],
    },
  ],
});

Running queries with the CloudTrail Lake SQL API

Queries are submitted asynchronously. Submit a query, poll for completion, retrieve results:

import {
  CloudTrailClient,
  StartQueryCommand,
  GetQueryResultsCommand,
  GetQueryCommand,
} from "@aws-sdk/client-cloudtrail";

const cloudtrail = new CloudTrailClient({ region: "us-east-1" });

const EVENT_DATA_STORE_ARN =
  "arn:aws:cloudtrail:us-east-1:123456789012:eventdatastore/EXAMPLE-1234";

async function queryMcpToolCallAudit(
  principalPrefix: string,
  startDate: string, // "2026-09-01T00:00:00Z"
  endDate: string    // "2026-09-19T23:59:59Z"
): Promise[]> {
  // Submit query
  const { QueryId } = await cloudtrail.send(new StartQueryCommand({
    QueryStatement: `
      SELECT
        eventTime,
        eventName,
        eventSource,
        awsRegion,
        userIdentity.principalId,
        userIdentity.arn,
        sourceIPAddress,
        requestParameters,
        errorCode,
        errorMessage
      FROM ${EVENT_DATA_STORE_ARN}
      WHERE eventTime > '${startDate}'
        AND eventTime < '${endDate}'
        AND userIdentity.principalId LIKE '%${principalPrefix}%'
        AND readOnly = 'false'
      ORDER BY eventTime DESC
      LIMIT 1000
    `,
  }));

  // Poll until complete (typical latency 15-60s)
  while (true) {
    const status = await cloudtrail.send(new GetQueryCommand({
      EventDataStore: EVENT_DATA_STORE_ARN,
      QueryId,
    }));

    if (status.QueryStatus === "FINISHED") break;
    if (status.QueryStatus === "FAILED" || status.QueryStatus === "CANCELLED") {
      throw new Error(`Query ${QueryId} failed: ${status.QueryStatus}`);
    }
    await new Promise(r => setTimeout(r, 3000)); // poll every 3s
  }

  // Retrieve results (paginated)
  const results: Record[] = [];
  let nextToken: string | undefined;
  do {
    const page = await cloudtrail.send(new GetQueryResultsCommand({
      EventDataStore: EVENT_DATA_STORE_ARN,
      QueryId,
      NextToken: nextToken,
    }));
    for (const row of page.QueryResultRows ?? []) {
      // Each row is an array of {key, value} objects
      const obj: Record = {};
      for (const col of row) { obj[col.key!] = col.value; }
      results.push(obj);
    }
    nextToken = page.NextToken;
  } while (nextToken);

  return results;
}

Useful CloudTrail Lake SQL queries for MCP audit

-- 1. All write operations by the MCP server role in the last 30 days
SELECT eventTime, eventName, eventSource, errorCode
FROM $EDS_ARN
WHERE eventTime > date_add('day', -30, NOW())
  AND userIdentity.arn LIKE '%mcp-server-task-role%'
  AND readOnly = 'false'
ORDER BY eventTime DESC;

-- 2. Failed operations (access denied, throttling) by any MCP principal
SELECT eventTime, eventName, eventSource, errorCode, errorMessage,
       userIdentity.principalId, sourceIPAddress
FROM $EDS_ARN
WHERE eventTime > date_add('day', -7, NOW())
  AND userIdentity.principalId LIKE '%mcp%'
  AND errorCode IS NOT NULL
ORDER BY eventTime DESC
LIMIT 500;

-- 3. AssumeRole events — detect credential vending patterns
SELECT eventTime,
       json_extract_scalar(requestParameters, '$.roleArn') AS assumed_role,
       json_extract_scalar(requestParameters, '$.roleSessionName') AS session_name,
       userIdentity.principalId AS calling_principal,
       sourceIPAddress
FROM $EDS_ARN
WHERE eventTime > date_add('day', -7, NOW())
  AND eventName = 'AssumeRole'
  AND userIdentity.principalId LIKE '%mcp%'
ORDER BY eventTime DESC;

-- 4. Count of write events per API per day (trend analysis)
SELECT
  date_trunc('day', eventTime) AS event_day,
  eventName,
  eventSource,
  count(*) AS call_count
FROM $EDS_ARN
WHERE eventTime > date_add('day', -30, NOW())
  AND readOnly = 'false'
  AND userIdentity.arn LIKE '%mcp-server-task-role%'
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 4 DESC;

-- 5. Detect DeleteItem and DeleteObject spikes (data destruction audit)
SELECT
  date_trunc('hour', eventTime) AS hour,
  eventName,
  count(*) AS count,
  approx_percentile(cast(additionalEventData AS varchar), 0.5) AS sample
FROM $EDS_ARN
WHERE eventTime > date_add('day', -7, NOW())
  AND eventName IN ('DeleteItem', 'DeleteObject', 'BatchWriteItem')
  AND userIdentity.arn LIKE '%mcp%'
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

CloudTrail Lake SQL supports most ANSI SQL functions: date_add, date_trunc, count, approx_percentile, json_extract_scalar, LIKE, IS NULL. Notable limitations: no JOINs across event data stores, no subqueries in FROM clause, LIMIT is required for large result sets (results above 1,000 rows need DeliveryS3Uri for S3 export).

Cross-account aggregation with AWS Organizations

For organizations running MCP servers across multiple accounts (dev, staging, production), a single Lake event data store with Organizations integration ingests events from all member accounts into one queryable location:

# In the management (payer) account:
aws cloudtrail create-event-data-store \
  --name mcp-org-audit-lake \
  --organization-enabled \
  --multi-region-enabled \
  --retention-period 2557

# In each member account — the Organization integration auto-creates
# a "channel" that routes CloudTrail events to the management account's Lake.
# No manual configuration required in member accounts when organizationEnabled=true.

# Verify events from a specific member account are arriving:
aws cloudtrail start-query \
  --query-statement "
    SELECT accountId, count(*) as event_count
    FROM arn:aws:cloudtrail:us-east-1:MGMT_ACCOUNT:eventdatastore/EXAMPLE
    WHERE eventTime > date_add('hour', -1, NOW())
    GROUP BY accountId
  "

When organizationEnabled: true, the data store automatically ingests events from all current and future member accounts in the organization. Queries can filter by accountId to scope to specific accounts or compare activity across accounts.

Exporting large result sets to S3

Queries returning more than 1,000 rows require S3 delivery. Results are written as Parquet files to the specified S3 prefix:

const { QueryId } = await cloudtrail.send(new StartQueryCommand({
  QueryStatement: "SELECT * FROM $EDS_ARN WHERE ...",
  DeliveryS3Uri: "s3://mcp-audit-exports/query-results/",
}));

// Results delivered to:
// s3://mcp-audit-exports/query-results//result_.parquet
// A manifest file lists all result files:
// s3://mcp-audit-exports/query-results//manifest.json

S3 delivery requires the CloudTrail service principal to have s3:PutObject on the export bucket. Add a bucket policy statement identical to the trail delivery bucket policy but scoped to the export bucket ARN.

Failure modes