Query ActionTrail events in Simple Log Service with SQL statements - ActionTrail - Alibaba Cloud - ActionTrail

ActionTrail records events for the last 90 days. To query and analyze older events, create a trail to deliver events to Simple Log Service (SLS), and then use SQL statements in SLS.

SQL statement syntax

SQL statements use the following format: <Query statement> | <Analytic statement>.

The following table lists the query and analytic statements for each supported query method.

Query method	Query statement	Analytic statement
Event query	Query by read/write type: `* AND "event.eventCategory": Management AND "event.eventRW": Write` Query by username: `* AND "event.eventCategory": Management AND "event.userIdentity.userName": "xxx"` Query by event name: `* AND "event.eventCategory": Management AND "event.eventName": "DescribeScalingGroups"` Query by resource type: `* AND "event.eventCategory": Management AND "event.resourceType": "ACS::ECS::Instance"` Resource Name: `* AND "event.eventCategory": Management AND "event.resourceName": "i-xxx"` Service name: `* AND "event.eventCategory": Management AND "event.serviceName": "Ecs"` Query 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	Read/write type: `* AND "event.eventCategory": Management AND "event.eventRW": Write` Event Name: `* AND "event.eventCategory": Management AND "event.eventName": "DescribeScalingGroups"` Service name: `* AND "event.eventCategory": Management AND "event.serviceName": "ECS"` Query 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 by IP address: `* AND "event.eventCategory": Insight AND event.insightDetails.insightType: IpInsight AND "event.insightDetails.sourceIpAddress": "10.12.XX.XX"` Query by insight event type: `* AND "event.eventCategory": Insight AND event.insightDetails.insightType: IpInsight` Query 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`

SQL statement examples

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 accountId, "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 time range for your query, we recommend that you add LIMIT N to the statement to return only N events. For example, setting LIMIT 20 returns 20 events.

* AND "event.eventCategory": Management AND "event.eventRW": Write | SELECT "event.serviceName" AS serviceName, "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, serviceName, eventName, eventRW, sourceIpAddress, resourceType, resourceName, accessKeyId, userType, userName, eventRegion ORDER BY time DESC LIMIT 20
Example 3: Query all IP anomalies in insight events

* 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