SQL statements in advanced event queries - ActionTrail - Alibaba Cloud Documentation Center

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.
Associated resource	`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.
Others	`event.eventId`	The event ID.

Aggregate functions

The following table describes the aggregate functions supported by ActionTrail.

Function	Syntax	Description	Supported in SQL	Supported in SPL
arbitrary function	arbitrary(x)	Returns a random, non-null value of the x field.	√	×
avg function	avg(x)	Calculates the average of the values of the x field.	√	×
bitwise_and_agg function	bitwise_and_agg(x)	Returns the result of the bitwise AND operation on the values of the x field.	√	×
bitwise_or_agg function	bitwise_or_agg(x)	Returns the result of the bitwise OR operation on the values of the x field.	√	×
bool_and function	bool_and(boolean expression)	Checks whether all logs meet the specified condition. If yes, the function returns true. This function is equivalent to the every function.	√	×
bool_or function	bool_or(boolean expression)	Checks whether a log that meets the specified condition exists. If yes, the function returns true.	√	×
checksum function	checksum(x)	Calculates the checksum of the values of the x field.	√	×
count function	count(*)	Counts the number of logs.	√	×
	count(1)	Counts the number of logs. This function is equivalent to the count(*) function.	√	×
	count(x)	Counts the number of logs whose value of the x field is not NULL.	√	×
count_if function	count_if(boolean expression)	Counts the number of logs that meet the specified condition.	√	×
every function	every(boolean expression)	Checks whether all logs meet the specified condition. If yes, the function returns true. This function is equivalent to the bool_and function.	√	×
geometric_mean function	geometric_mean(x)	Calculates the geometric mean of the values of the x field.	√	×
kurtosis function	kurtosis(x)	Calculates the kurtosis of the values of the x field.	√	×
map_union function	map_union(x)	Returns the result of the union operation on the specified maps. If a key exists in multiple input maps, the function randomly returns one of the values of the key.	√	×
max function	max(x)	Queries the largest value of the x field.	√	×
max function	max(x, n)	Queries the n largest values of the x field. The function returns an array.	√	×
max_by function	max_by(x, y)	Queries the value of x that is associated with the largest value of the y field.	√	×
max_by function	max_by(x, y, n)	Queries the values of x that are associated with the n largest values of the y field. The function returns an array.	√	×
min function	min(x)	Queries the smallest value of the x field.	√	×
min function	min(x, n)	Queries the n smallest values of the x field. The function returns an array.	√	×
min_by function	min_by(x, y)	Queries the value of x that is associated with the smallest value of the y field.	√	×
min_by function	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 function	skewness(x)	Calculates the skewness of the values of the x field.	√	×
sum function	sum(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