How can I use SQL statements to query ActionTrail events delivered to Simple Log Service? - ActionTrail

ActionTrail helps you monitor the operations within your Alibaba Cloud account and records the events that were generated in the last 90 days. If you want to analyze the events that were generated more than 90 days ago, you can create a trail in the ActionTrail console and deliver the events to the specified Simple Log Service Logstore. Then, you can use SQL statements to query and analyze the delivered events. This topic describes how to write SQL statements to query events in Simple Log Service.

Syntax of SQL statements

SQL statements are in the format of <Search statement> | <Analytic statement>.

ActionTrail allows you to use SQL statements to query events in different scenarios. The following table describes the search statements and analytic statements that can be used to query events in different scenarios:

Scenario	Sample search statement	Sample analytic statement
Event query	Query events by read/write type: `* AND "event.eventCategory": Management AND "event.eventRW": Write` Query events by username: `* AND "event.eventCategory": Management AND "event.userIdentity.userName": "xxx"` Query events by event name: `* AND "event.eventCategory": Management AND "event.eventName": "DescribeScalingGroups"` Query events by resource type: `* AND "event.eventCategory": Management AND "event.resourceType": "ACS::ECS::Instance"` Query events by resource name: `* AND "event.eventCategory": Management AND "event.resourceName": "i-xxx"` Query events by Alibaba Cloud service name: `* AND "event.eventCategory": Management AND "event.serviceName": "Ecs"` Query events by AccessKey ID: `* AND "event.eventCategory": Management "event.userIdentity.accessKeyId": "STS.xxxx"`	select "event.acsRegion" as acsRegion, "event.apiVersion" as apiVersion, "event.eventId" as eventId, "event.eventName" as eventName, "event.eventRW" as eventRW, "event.eventSource" as eventSource, from_unixtime(__time__) as eventTime, "event.eventType" as eventType, "event.eventVersion" as eventVersion, "event.errorCode" as errorCode, "event.errorMessage" as errorMessage, "event.requestId" as requestId, "event.requestParameterJson" as requestParameterJson, "event.resourceName" as resourceName, "event.resourceType" as resourceType, "event.serviceName" as serviceName, "event.sourceIpAddress" as sourceIpAddress, "event.userAgent" as userAgent, "event.userIdentity.accessKeyId" as accessKeyId, "event.userIdentity.accountId" as accontId, "event.userIdentity.principalId" as principalId, "event.userIdentity.type" as type, "event.userIdentity.userName" as userName
Event summary query	Query event summaries by read/write type: `* AND "event.eventCategory": Management AND "event.eventRW": Write` Query event summaries event name: `* AND "event.eventCategory": Management AND "event.eventName": "DescribeScalingGroups"` Query event summaries by Alibaba Cloud service name: `* AND "event.eventCategory": Management AND "event.serviceName": "Ecs"` Query event summaries by AccessKey ID: `* AND "event.eventCategory": Management "event.userIdentity.accessKeyId": "STS.xxxx"`	SELECT"event.serviceName"AS servieName,"event.eventName"AS eventName,"event.eventRw"AS eventRw,"event.sourceIpAddress"AS sourceIpAddress,"event.resourceName"AS resourceName,"event.resourceType"AS resourceType,"event.userIdentity.userName"AS userName,"event.userIdentity.type"AS userType,"event.userIdentity.accessKeyId"AS accessKeyId,"event.acsRegion"AS eventRegion,COUNT("event.eventId")AS n, date_trunc('hour', __time__) AS time GROUP BY time, servieName, eventName, eventRw, sourceIpAddress, resourceType, resourceName, accessKeyId, userType, userName, eventRegion ORDER BY time DESC LIMIT 20
Insight event query	Query insight events by unusual IP address: `* AND "event.eventCategory": Insight AND event.insightDetails.insightType: IpInsight AND "event.insightDetails.sourceIpAddress": "10.12.XX.XX"` Query insight events by event type: `* AND "event.eventCategory": Insight AND event.insightDetails.insightType: IpInsight` Query insight events by event ID: `* AND "event.eventCategory": Insight AND event.insightDetails.insightType: IpInsight AND "event.eventId": 6CE5DBDE-5D18-4BF9-BD6A-E0D2E1BA****`	`select from_unixtime(__time__) as eventTime, "event.acsRegion" as eventRegion, "event.insightDetails.sourceIpAddress" as sourceIpAddress, "event.insightDetails.insightContext.statistics.insightCount" as count`

Examples of SQL statements

Example 1: Query all management events of the write type
* AND "event.eventCategory": Management AND "event.eventRW": Write | select "event.acsRegion" as acsRegion, "event.apiVersion" as apiVersion, "event.eventId" as eventId, "event.eventName" as eventName, "event.eventRW" as eventRW, "event.eventSource" as eventSource, from_unixtime(__time__) as eventTime, "event.eventType" as eventType, "event.eventVersion" as eventVersion, "event.errorCode" as errorCode, "event.errorMessage" as errorMessage, "event.requestId" as requestId, "event.requestParameterJson" as requestParameterJson, "event.resourceName" as resourceName, "event.resourceType" as resourceType, "event.serviceName" as serviceName, "event.sourceIpAddress" as sourceIpAddress, "event.userAgent" as userAgent, "event.userIdentity.accessKeyId" as accessKeyId, "event.userIdentity.accountId" as accontId, "event.userIdentity.principalId" as principalId, "event.userIdentity.type" as type, "event.userIdentity.userName" as userName
Example 2: Query the summaries of all management events of the write type
Note
If you specify a long query time range, we recommend that you use the LIMIT N clause to limit the number of returned events to N. For example, if you use the LIMIT 20 clause, the system returns 20 events.
* AND "event.eventCategory": Management AND "event.eventRW": Write | SELECT"event.serviceName"AS servieName,"event.eventName"AS eventName,"event.eventRw"AS eventRw,"event.sourceIpAddress"AS sourceIpAddress,"event.resourceName"AS resourceName,"event.resourceType"AS resourceType,"event.userIdentity.userName"AS userName,"event.userIdentity.type"AS userType,"event.userIdentity.accessKeyId"AS accessKeyId,"event.acsRegion"AS eventRegion,COUNT("event.eventId")AS n, date_trunc('hour', __time__) AS time GROUP BY time, servieName, eventName, eventRw, sourceIpAddress, resourceType, resourceName, accessKeyId, userType, userName, eventRegion ORDER BY time DESC LIMIT 20
Example 3: Query all insight events of the IPInsight type
* AND "event.eventCategory": Insight AND event.insightDetails.insightType: IpInsight | select from_unixtime(__time__) as eventTime, "event.acsRegion" as eventRegion, "event.insightDetails.sourceIpAddress" as sourceIpAddress, "event.insightDetails.insightContext.statistics.insightCount" as count