All Products
Search
Document Center

ActionTrail:SQL statements in advanced event queries

Last Updated:Oct 18, 2023

You can use SQL statements in advanced event queries to query or analyze events that are delivered to Simple Log Service by using a trail.

Important If you want to use strings in analytic statements, you must enclose the strings in single quotation marks (''). Strings that are not enclosed or strings that are enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "status" indicates the status log field.

Examples

  • Full-text query

    Specify a keyword such as a field name or a field value.

    Example: Query events that contain the keyword DescribeInstances.

    DescribeInstances 
  • Property-based exact match

    Specify an event property in the [Event property name]:[Event property value] format. You must specify an exact name and value. For more information about event properties, see Event properties in this topic.

    Example: Query events in which the value of event.eventSource is actiontrail.cn-hangzhou.aliyuncs.com.

    event.eventSource: actiontrail.cn-hangzhou.aliyuncs.com
  • Attribute-based fuzzy match

    Specify an event property in the [Event property name]:[Event property value] format. You must specify an exact name. For the value, you can add an asterisk (*) to match multiple characters or a question mark (?) to match a single character.

    • Example 1: Query all AccessKey pair-based access events.

       event.userIdentity.accessKeyId: *
    • Example 2: Query events in which the value of event.eventSource starts with actiontrail.

      event.eventSource: actiontrail*
  • Multi-condition query

    Use parentheses () and logical operators AND or OR to perform a multi-condition query.

    • Example 1: Query all events on failures of AccessKey pair-based access.

      event.errorCode: * AND event.userIdentity.accessKeyId: *
    • Example 2: Query events in which the value of event.eventName is Create, CreateInstance, or RunInstances.

       event.eventName: Create  OR event.eventName: CreateInstance  OR event.eventName: RunInstances 
    • Example 3: Query events in which the value of event.serviceName is kafka and the value of eventName is Create, CreateInstance, or RunInstances.

      (event.serviceName: AliKafka AND ( event.eventName: Create  OR event.eventName: CreateInstance  OR event.eventName: RunInstances  )

  • Aggregate query

    You can use the aggregate query syntax of Simple Log Service to generate aggregate statistics on events. For example, you can use the GROUP BY clause to generate aggregate statistics on specific fields.

    • Example 1: Query the numbers of events that are aggregated by cloud service.

      * | SELECT "event.serviceName" AS service, COUNT(*) AS count FROM log GROUP BY "event.serviceName"
    • Example 2: Query the numbers of events that are aggregated by user and cloud service.

      * | SELECT "event.userIdentity.principalId" AS principalId, "event.serviceName" AS service, COUNT(*) AS count FROM log GROUP BY principalId,service

Event properties

The following table describes the event attributes supported by ActionTrail.

Category

Event property name

Description

Operator

event.userIdentity.accountId

The Alibaba Cloud account ID.

event.userIdentity.accessKeyId

The key ID.

event.userIdentity.principalId

The requester ID.

event.userIdentity.type

The account type.

event.userIdentity.userName

The username.

Action

event.serviceName

The service name.

event.eventRW

The read/write type.

event.eventName

The event name.

event.apiVersion

The version information.

event.errorMessage

The error message.

event.errorCode

The error code.

Associated resource

event.resourceType

The resource type.

event.resourceName

The resource name.

Location

event.acsRegion

The region.

event.eventSource

The event source.

event.sourceIpAddress

The source IP address.

Others

event.requestId

The request ID.

event.eventId

The event ID.

Aggregate functions

The following table describes the aggregate functions supported by ActionTrail.

FunctionSyntaxDescription
arbitrary functionarbitrary(x)Returns a random, non-null value of the x field.
avg functionavg(x)Calculates the average of the values of the x field.
bitwise_and_agg functionbitwise_and_agg(x)Returns the result of the bitwise AND operation on the values of the x field.
bitwise_or_agg functionbitwise_or_agg(x)Returns the result of the bitwise OR operation on the values of the x field.
bool_and functionbool_and(boolean expression)Checks whether all log entries meet the specified condition. If all log entries meet the specified condition, the function returns true.

This function is equivalent to the every function.

bool_or functionbool_or(boolean expression)Checks whether a log entry that meets the specified condition exists. If a log entry that meets the specified condition exists, the function returns true.
checksum functionchecksum(x)Calculates the checksum of the values of the x field.
count functioncount(*)Counts the number of log entries.
count(1)Counts the number of log entries. This function is equivalent to the count(*) function.
count(x)Counts the number of log entries that contain the x field whose value is not null.
count_if functioncount_if(boolean expression)Counts the number of log entries that meet the specified condition.
every functionevery(boolean expression)Checks whether all log entries meet the specified condition. If all log entries meet the specified condition, the function returns true.

This function is equivalent to the bool_and function.

geometric_mean functiongeometric_mean(x)Calculates the geometric mean of the values of the x field.
kurtosis functionkurtosis(x)Calculates the excess kurtosis of the values of the x field.
map_union functionmap_union(x)Returns the result of the union operation on the specified maps. If multiple input maps have the same key, the function selects an arbitrary input map from these input maps to be in the output map.
max functionmax(x)Queries the largest value of the x field.
max(x,n)Queries the n largest values of the x field. The function returns an array.
max_by functionmax_by(x,y)Queries the value of x that is associated with the largest value of the y field.
max_by(x,y,n)Queries the values of x that are associated with the n largest values of the y field.
min functionmin(x)Queries the minimum value of the x field.
min(x,n)Queries the n smallest values of the x field. The function returns an array.
min_by functionmin_by(x,y)Queries the value of x that is associated with the smallest value of the y field.
min_by(x,y,n)Queries the values of x that are associated with the n smallest values of the y field. The function returns an array.
skewness functionskewness(x)Calculates the skewness of the values of the x field.
sum functionsum(x)Calculates the sum of the values of the x field.

Reserved words

The following code block shows all reserved words in SQL statements:

AND
AS
BETWEEN
BY
CASE
CAST
CROSS
CUBE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DISTINCT
ELSE
END
ESCAPE
EXCEPT
EXISTS
FROM
GROUP
GROUPING
HAVING
IN
INNER
INSERT
INTERSECT
INTO
IS
JOIN
LEFT
LIKE
LIMIT
LOCALTIME
LOCALTIMESTAMP
NATURAL
NOT
NULL
ON
OR
ORDER
OUTER
RIGHT
ROLLUP
SELECT
THEN
TRUE
UNION
UNNEST
VALUES
WHEN
WHERE
WITH