クエリの例 - AWS CloudTrail

クエリの例

このセクションには、使用を開始するために役立つ CloudTrail Lake クエリの例が含まれています。

2022 年 1 月 22 日に CreateBucket をコールしたすべてのプリンシパルユーザー ID の検索

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

結果

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

2022 年 1 月 22 日にユーザーがコールしたすべての API を検索

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

結果

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

2022 年 1 月 1 日以降の API コール数を、eventNameeventSource をグループ化して検索

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

結果

{ "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" } ] ] }

一連のリージョンでコンソールにサインインしたすべてのユーザーを検索

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

結果

{ "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" } ] ] }

2022 年 1 月に実行したすべての CloudTrail Lake クエリを検索

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 > '2022-01-01 00:00:00' AND eventTime < '2022-02-01 00:00:00'

結果

{ "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;" } ] ] }