Query and analyze logs using SQL statements - Security Center

The Log Analysis page in Security Center lets you write custom queries and statistical analyses against your security logs. Use the query syntax described in this topic to filter logs by keyword or field, aggregate results with SQL, and analyze logon patterns by time or IP address.

How it works

A log query statement has two parts, separated by a vertical bar (|):

<search syntax> | <analytics syntax>

Both parts are optional:

Part	What it does	What it cannot do
Search	Filters logs by keyword, field value, or numeric range	Cannot aggregate or compute statistics
Analytics	Aggregates and computes statistics on the filtered results or all data in the time range	Cannot filter by full-text keyword on its own

If the search part is * or empty, no filtering is applied — all logs in the selected time range are passed to the analytics part.
If the analytics part is empty, the matching log entries are returned as-is, with no statistics computed.

To open the query editor, go to Risk Governance > Log Analysis in the left-side navigation pane of the Security Center console, then enter your statement in Search & Analyze.

Search syntax

The search part supports full-text queries and field-specific searches. The query editor supports multi-line input and syntax highlighting.

Full-text query

Search across all log fields without specifying a field name. Separate multiple keywords with a space or and. Enclose phrases that contain spaces in double quotation marks (").

Query	What it returns
`www.aliyundoc.com 404`	Logs containing both terms
`www.aliyundoc.com and 404`	Same as above
`www.aliyundoc.com and (error or 404)`	Logs containing `www.aliyundoc.com` and either `error` or `404`
`www.aliyundoc.com and failed_*`	Logs containing `www.aliyundoc.com` and any term starting with `failed_`

Wildcard matching only supports trailing asterisks (failed_*). A leading asterisk (*failed) is not supported.

For the full search syntax reference, see Log search overview.

Field-specific search

Filter on a specific field using Field: Value or numeric comparisons such as Field >= Value. Combine multiple field conditions with and or or, or mix them with full-text terms.

Each log entry includes a __topic__ field that identifies the log type. Use __topic__ to target a specific log source, then add field filters for that log type.

Examples

Find alert logs with a serious severity level:

__topic__ : sas-security-log and level: serious

Find SSH logon events from a specific IP address:

__topic__:aegis-log-login and ip:1.2.XX.XX and warn_type:SSHLOGIN

Find internal DNS queries with a response time greater than 1 second:

__topic__:local-dns and time_usecond > 1000000

Find internal DNS queries with a response time between 1 second and 10 seconds:

__topic__:local-dns and time_usecond in [1000000,10000000]

Analytics syntax

The analytics part uses SQL-92 statements. The from log clause — equivalent to from <table name> in standard SQL — can be omitted.

By default, queries return the first 100 log entries. Use the LIMIT clause to change this. For details, see LIMIT clause.

For a full list of supported SQL functions, see Log analysis overview.

Time-based queries

Every log entry has a built-in __time__ field that stores the log timestamp as a UNIX timestamp (seconds elapsed since January 1, 1970, 00:00:00 UTC). Convert this field before displaying it in query results.

Retrieve the most recent logon events from a specific IP

Return the 10 most recent logon events from IP 1.2.XX.XX, with a human-readable timestamp:

__topic__: aegis-log-login and ip: 1.2.XX.XX
| select date_format(__time__, '%Y-%m-%d %H:%i:%s') as time, warn_ip, warn_type
order by __time__ desc
limit 10

Calculate how many days have passed since each logon

Add a days_passed column that shows the age of each logon event in days:

__topic__: aegis-log-login and ip: 1.2.XX.XX
| select date_format(__time__, '%Y-%m-%d %H:%i:%s') as time, warn_ip, warn_type ,
round((to_unixtime(now()) - __time__)/86400,1) as "days_passed"
order by __time__ desc
limit 10

round((to_unixtime(now()) - __time__)/86400, 1) works as follows:

to_unixtime(now()) converts the current time to a UNIX timestamp.
Subtract __time__ to get the elapsed seconds.
Divide by 86400 (seconds in a day).
round(..., 1) rounds the result to one decimal place.

Count daily logon events for a device

Group logon events by day and count how many occurred each day:

__topic__: aegis-log-login and ip: 1.2.XX.XX
| select date_trunc('day', __time__) as dt,
count(1) as PV
group by dt
order by dt

date_trunc('day', __time__) aligns each timestamp to the start of its day. Replace 'day' with 'second', 'minute', 'hour', 'week', 'month', or 'year' to group by a different time unit.

Count logon events per 5 minutes

Group events into 5-minute buckets across all devices:

__topic__: aegis-log-login
| select from_unixtime(__time__ - __time__% 300) as dt,
count(1) as PV
group by dt
order by dt
limit 1000

__time__ - __time__ % 300 rounds each timestamp down to the nearest 5-minute boundary (300 seconds). limit 1000 retrieves the first 1,000 buckets, which covers approximately 83 hours of data.

For more time functions such as date_parse and date_format, see Date and time functions.

IP address-based queries

The warn_ip field in logon log entries stores the source IP address. Use IP geolocation functions to analyze where logons originate.

Identify source countries for logons to a specific server

__topic__: aegis-log-login and uuid: 12344567
| SELECT ip_to_country(warn_ip) as country,
count(1) as "Number of logons"
group by country

ip_to_country(warn_ip) returns the country that corresponds to the source IP address.

Break down logons by province or state

__topic__: aegis-log-login and uuid: 12344567
| SELECT ip_to_province(warn_ip) as province,
         count(1) as "Number of logons"
         group by province

ip_to_province(warn_ip) returns the province for Chinese IP addresses. For non-Chinese IPs, it attempts to return the equivalent province or state, but the result cannot be plotted on a China map.

Map the geographic distribution of logons

__topic__: aegis-log-login and uuid: 12344567
| SELECT ip_to_geo(warn_ip) as geo,
         count(1) as "Number of logons"
         group by geo
         limit 10000

ip_to_geo(warn_ip) returns the latitude and longitude of the source IP address. limit 10000 retrieves up to 10,000 entries for geographic plotting.

For additional IP functions — including ip_to_provider (ISP lookup) and ip_to_domain (public vs. private IP detection) — see IP functions.