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 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 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 summary query||
|Insight event query||
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 wide time range for the query, we recommend that you set the
LIMITfield to an appropriate number N. This indicates that
Nevents will be returned. For example, the setting
LIMIT 20indicates that 20 events will be returned.
* 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