You can customize the query and analysis of the log in the Log Search field of the Log Analysis page to query logs under complex scenarios.

Both search syntax and analytics syntax are optional.
- Search: The search filters can be generated by using keywords, fuzzy match conditions, values, ranges, and combinations. If the value of the Search part is empty or an asterisk (*), all data of this time period is not filtered by any conditions, and the results of all queries are calculated.
- Analytics: It calculates and collects statistics on search results or full data. If the Analytics part is empty, the results of the query are returned and no statistics are calculated.
Search syntax
The search syntax of Log Service supports full-text query and field query. The query box supports multi-line search, syntax highlighting, and other features.
- Full-text query
Do not need to specify a field. Enter the keyword query directly. You can wrap a keyword with double quotation marks ("). Separate multiple keywords by spaces or by and.
- Example of multiple keywords query
Search for logs containing
www.aliyun.com
and404
. Example:www.aliyun.com 404
or
www.aliyun.com and 404
- Conditional query example
Search for logs containing
www.aliyun.com
anderror
, or logs containing www.aliyun.com and404
. Example:www.aliyun.com **and** (error **and** 404)
- Prefix query example
Search for logs containing
www.aliyun.com
and start withfailed_
. Example:www.aliyun.com and failed_*
Note Field query only supports suffixes with asterisks (*
). Prefixes with asterisks (*
) are not supported.
- Example of multiple keywords query
- Field query
The comparison of numeric type fields can be implemented in the format of
field: value
orfield>=value
, with the combination ofand
, andor
. It can also be used together with full-text search, with the combination ofand
andor
.Log Service supports more accurate queries based on fields.
- Multiple field query
Search for security alerting logs that are in
Severe
state. Example:__topic__ : sas-security-log and level: serious
Search for all SSH logins on the client 1.2.3.4. Example:
__topic__:aegis-log-login and ip:1.2.3.4 and warn_type:SSHLOGIN
Note Each Security Center log contains a__topic
field that represents a topic. This field is used to distinguish the 14 types of logs in Security Center. The fields used in the example, such aslevel
,warn_type
,IP
are the fields of specific types of Security Center logs. - Example of numeric field query
Search for all local DNS query logs that have a response time of more than one second. Example:
__topic__:local-dns and time_usecond > 1000000
Interval queries are also supported, which queries local DNS logs with a response time greater than five seconds and less than or equal to ten seconds: Example:
__topic__:local-dns and time_usecond in [1000000,10000000]
For more information about search syntax, see Index and search.
- Multiple field query
Analytics syntax
You can analyze and statisticize log data using the SQL/92 syntax. For more information about the syntax and functions supported by the Log Service, see Real-time analysis.
The from table name statement in the SQL standard syntax can be omitted from the analysis statement, which means the from log statement can be omitted.
Log data returns the first 100 entries by default, and you can modify the number of returned entries using the LIMIT syntax.
Query analysis based on log time
Each Security Center log has a built-in field: __time__
, which indicates the time when this log is generated, so that time-based calculations
can be performed in statistics. The format is a Unix timestamp, which is technically
the cumulative seconds since 1970-01-01 00:00:00 UTC. Therefore, when the timestamp
is in use, it must be calculated and then formatted before it is displayed.
- Select and show time
In this example, within a specific time range, we select the latest ten login logs with the IP of 1.2.3.4 and display the time, source IP address, and login type of each log. Example:
__topic__: aegis-log-login and ip: 1.2.3.4 | select date_format(__time__, '%Y-%m-%d %H:%i:%s') as time, warn_ip, warn_type order by __time__ desc limit 10
- Calculate time
Use
__time__
to calculate the number of days after the login. Example:__topic__: aegis-log-login and ip: 1.2.3.4 | 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
In the preceding example we use
round((to_unixtime(now()) - __time__)/86400, 1) to do the calculation
. First, we use to_unixtimeto convert the time obtained by now() to a Unix timestamp. Second, we subtract the build-in __time__ from the calculated value to get the number of seconds that have passed. Finally, we divide the calculated value by 86400, which is the total number of seconds in a day, and round it to the decimal with the function round(data, 1) to calculate the number of days that each attack log has passed since it is generated.
- Group statistics based on a specific time
If you want to know the logon trends for a device over a specific time range, you can use the following SQL:
__topic__: aegis-log-login and ip: 1.2.3.4 | select date_trunc('day', __time__) as dt, count(1) as PV group by dt order by dt
We pass the built-in
__time__
field to thedate_trunc('day', ..)
function to align the time by day. Each log is grouped into the partition of the day to which it belongs for calculating the total number (count(1)) and is sorted by partition time block. The first argument of the functiondate_trunc
provides alignment for other units, includingsecond
,minute
,hour
,week
,month
, andyear
. For more information about functions, see Date and time functions. - Group statistics based on a flexible time
If you want to know more flexible rules of time grouping, for example, login trends per five minutes for devices under the account, you can use the following SQL:
__topic__: aegis-log-login | select from_unixtime(__time__ - __time__% 300) as dt, count(1) as PV group by dt order by dt limit 1000
In the preceding example, we use the built-in time field to calculate
__time__ - __time__% 300
and format it using the functionfrom_unixtime
. Each log is grouped into a five-minute (300 seconds) partition for calculating the total number(count(1))
and sorted by partition time block to obtain the first 1,000 logs, which is equivalent to selecting data in the first 83 hours.The line chart is shown as follows:
For more information about time resolving functions, such as using
date_parse
anddate_format
to convert a time format to another, see Date and time functions.
Analysis of query based on client IP
The warn_ip
in Security Center logs represents the source IP of the login.
- The country distribution of login sources
Query the distribution of the source countries that logged on a specified server. For example:
__topic__: aegis-log-login and uuid: 12344567 | SELECT ip_to_country(warn_ip) as country, count(1) as "number of logons" group by country
In the preceding example, we use the
ip_to_country
function to retrieve the country information corresponding to the login sourceipwarn_ip
.The world map is shown as follows:
- Identity distribution of login
Use the
ip_to_province
function to retrieve a more detailed distribution of logins based on province. For example:__topic__: aegis-log-login and uuid: 12344567 | SELECT ip_to_province(warn_ip) as province, count(1) as "number of login" group by province
In the preceding example, we use another IP function
ip_to_province
to retrieve the source province of an IP address. If the IP address is out of China, the system attempts to convert it to province (or state) based on the country location of the address. However, it cannot be displayed when you select the China map.Display of China map:
- Geothermal distribution of login
Use the
ip_to_geo
function to retrieve a geothermal distribution of the logins:__topic__: aegis-log-login and uuid: 12344567 | SELECT ip_to_geo(warn_ip) as geo, count(1) as "number of logins" group by geo limit 10000
In the preceding example, we use another IP function
ip_to_geo
to retrieve the latitude and longitude of an IP. We set the limit to 10,000 to retrieve the first 10,000 records.Display of Amap:
Note For more information about IP-based parsing functions, such as obtaining the IP providerip_to_provider
, determining whetherip_to_domain
is an Internet or Intranet IP address, see IP geographic functions.