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