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.
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
isactiontrail.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 withactiontrail
.event.eventSource: actiontrail*
Multi-condition query
Use parentheses
()
and logical operatorsAND
orOR
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
isCreate
,CreateInstance
, orRunInstances
.event.eventName: Create OR event.eventName: CreateInstance OR event.eventName: RunInstances
Example 3: Query events in which the value of
event.serviceName
iskafka
and the value of eventName isCreate
,CreateInstance
, orRunInstances
.(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 |
| The Alibaba Cloud account ID. |
| The key ID. | |
| The requester ID. | |
| The account type. | |
| The username. | |
Action |
| The service name. |
| The read/write type. | |
| The event name. | |
| The version information. | |
| The error message. | |
| The error code. | |
Associated resource |
| The resource type. |
| The resource name. | |
Location |
| The region. |
| The event source. | |
| The source IP address. | |
Others |
| The request ID. |
| The event ID. |
Aggregate functions
The following table describes the aggregate functions supported by ActionTrail.
Function | Syntax | Description |
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 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 function | bool_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 function | checksum(x) | Calculates the checksum of the values of the x field. |
count function | count(*) | 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 function | count_if(boolean expression) | Counts the number of log entries that meet the specified condition. |
every function | every(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 function | geometric_mean(x) | Calculates the geometric mean of the values of the x field. |
kurtosis function | kurtosis(x) | Calculates the excess 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 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 function | max(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 function | max_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 function | min(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 function | min_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 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