Use SQL to query and analyze events - ActionTrail - Alibaba Cloud - ActionTrail

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.

Parameter	Syntax	Description	SQL support	SPL support
arbitrary function	arbitrary(x)	Returns an arbitrary non-NULL value from x.	√	×
avg function	avg(x)	Calculates the arithmetic mean of x.	√	×
bitwise_and_agg function	bitwise_and_agg(x)	Returns the bitwise AND of all values in x.	√	×
bitwise_or_agg function	bitwise_or_agg(x)	Returns the bitwise OR of all values in x.	√	×
bool_and function	bool_and(boolean expression)	Returns true if all logs meet the condition. The `bool_and` function is equivalent to the `every` function.	√	×
bool_or function	bool_or(boolean expression)	Returns true if at least one log meets the condition.	√	×
checksum function	checksum(x)	Calculates the checksum of x.	√	×
count function	count(*)	Returns the total number of logs.	√	×
	count(1)	Returns the total number of logs, which is equivalent to `count(*)`.	√	×
	count(x)	Returns the number of logs where the value of x is not NULL.	√	×
count_if function	count_if(boolean expression)	Returns the number of logs that meet the specified condition.	√	×
every function	every(boolean expression)	Returns true if all logs meet the condition. The `every` function is equivalent to the `bool_and` function.	√	×
geometric_mean function	geometric_mean(x)	Calculates the geometric mean of x.	√	×
kurtosis function	kurtosis(x)	Calculates the kurtosis of x.	√	×
map_union function	map_union(x)	Returns the union of maps in a column. If a key exists in multiple maps, an arbitrary value for that key is chosen.	√	×
max function	max(x)	Returns the maximum value in x.	√	×
max function	max(x, n)	Returns the n largest values in x as an array.	√	×
max_by function	max_by(x, y)	Returns the value of x associated with the maximum value of y.	√	×
max_by function	max_by(x, y, n)	Returns an array of the x values corresponding to the n largest values of y.	√	×
min function	min(x)	Returns the minimum value in x.	√	×
min function	min(x, n)	Returns the n smallest values in x as an array.	√	×
min_by function	min_by(x, y)	Returns the value of x associated with the minimum value of y.	√	×
min_by function	min_by(x, y, n)	Returns an array of the x values corresponding to the n smallest values of y.	√	×
skewness function	skewness(x)	Calculates the skewness of x.	√	×
sum function	sum(x)	Calculates the sum of x.	√	×

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