Example queries - Amazon CloudTrail
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Example queries

This section describes how you can access sample queries in the CloudTrail console and includes a few example CloudTrail Lake queries to help you get started.

Note

You can also view queries created by the GitHub community. For more information and to view these sample queries, see CloudTrail Lake sample queries on the GitHub website. Amazon CloudTrail has not evaluated the queries in GitHub.

Viewing sample queries in the CloudTrail console

The CloudTrail console provides a number of sample queries that can help you get started writing your own queries.

To access the sample queries in the CloudTrail console

  1. Sign in to the Amazon Web Services Management Console and open the CloudTrail console at https://console.amazonaws.cn/cloudtrail/.

  2. From the navigation pane, under Lake, choose Query.

  3. Choose the Sample queries tab.

  4. To edit a sample query, choose the Query name. For information about running queries, see Run a query and save query results.

Example: Find all principal user identities who called CreateBucket on January 22, 2023

SELECT userIdentity.principalid, eventName FROM event_data_store_ID WHERE userIdentity.principalid IS NOT NULL AND eventTime > '2023-01-22 00:00:00' AND eventTime < '2023-01-23 00:00:00' AND eventName='CreateBucket'

Results

{ "QueryStatus": "FINISHED", "QueryStatistics": { "ResultsCount": 1, "TotalResultsCount": 1, "BytesScanned": 25077 }, "QueryResultRows": [ [ { "principalid": "principal_ID" }, { "eventName": "CreateBucket" } ] ] }

Example: Find all APIs that a user called on January 22, 2023

SELECT eventID, eventName, eventSource, eventTime FROM event_data_store_ID WHERE userIdentity.username = 'bob' AND eventTime > '2023-01-22 00:00:00' AND eventTime < '2023-01-23 00:00:00'

Results

{ "QueryStatus": "FINISHED", "QueryStatistics": { "ResultsCount": 2, "TotalResultsCount": 2, "BytesScanned": 13287 }, "QueryResultRows": [ [ { "eventID": "EXAMPLE-c3b6-43e4-aa35-b2490EXAMPLE" }, { "eventName": "DescribeQuery" }, { "eventSource": "cloudtrail.amazonaws.com" }, { "eventTime": "2023-01-22 16:53:53.000" } ], [ { "eventID": "EXAMPLE6-ac95-4b37-b587-76a80EXAMPLE" }, { "eventName": "ListBuckets" }, { "eventSource": "s3.amazonaws.com" }, { "eventTime": "2023-01-22 20:25:01.000" } ] ] }

Example: Find the number of API calls since January 1, 2023, grouped by eventName and eventSource

SELECT eventSource, eventName, COUNT(*) AS apiCount FROM event_data_store_ID WHERE eventTime > '2023-01-01 00:00:00' GROUP BY eventSource, eventName ORDER BY apiCount DESC

Results

{ "QueryStatus": "FINISHED", "QueryStatistics": { "ResultsCount": 3, "TotalResultsCount": 3, "BytesScanned": 10442 }, "QueryResultRows": [ [ { "eventSource": "s3.amazonaws.com" }, { "eventName": "PutObject" }, { "apiCount": "96059" } ], [ { "eventSource": "dynamodb.amazonaws.com" }, { "eventName": "DescribeTable" }, { "apiCount": "49426" } ], [ { "eventSource": "sts.amazonaws.com" }, { "eventName": "AssumeRole" }, { "apiCount": "45617" } ] ] }

Example: Find all users who signed in to the console in a set of Regions

SELECT eventTime, useridentity.arn, awsRegion FROM event_data_store_ID WHERE awsRegion in ('us-east-1', 'us-west-2') AND eventName = 'ConsoleLogin'

Results

{ "QueryStatus": "FINISHED", "QueryStatistics": { "ResultsCount": 2, "TotalResultsCount": 2, "BytesScanned": 15580 }, "QueryResultRows": [ [ { "eventTime": "2022-02-08 19:54:44.000" }, { "arn": "arn:aws:sts::123456789012:assumed-role/example-identity" }, { "awsRegion": "us-east-1" } ], [ { "eventTime": "2022-01-21 16:38:27.000" }, { "arn": "arn:aws:sts::123456789012:assumed-role/example-identity" }, { "awsRegion": "us-west-2" } ] ] }

Example: Find all CloudTrail Lake queries that were run in January 2023

SELECT element_at(responseElements, 'queryId'), element_at(requestParameters, 'queryStatement') FROM event_data_store_ID WHERE eventName='StartQuery' AND eventSource = 'cloudtrail.amazonaws.com' AND responseElements IS NOT NULL AND eventTime > '2023-01-01 00:00:00' AND eventTime < '2023-02-01 00:00:00'

Results

{ "QueryStatus": "FINISHED", "QueryStatistics": { "ResultsCount": 1, "TotalResultsCount": 1, "BytesScanned": 13002 }, "QueryResultRows": [ [ { "_col0": "arn:aws:cloudtrail:us-east-1:123456789012:eventdatastore/EXAMPLE-f852-4e8f-8bd1-bcf6cEXAMPLE" }, { "_col1": "select * from event_data_store_ID limit 1;" } ] ] }