Use custom log query and analysis

A log query statement consists of two parts: search syntax and analytics syntax, divided by vertical bars (|).

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 and 404. Example:

    www.aliyun.com 404

    or

    www.aliyun.com and 404

  • Conditional query example

    Search for logs containing www.aliyun.com and error, or logs containing www.aliyun.com and 404. Example:

    www.aliyun.com **and** (error **and** 404)

  • Prefix query example

    Search for logs containing www.aliyun.com and start with failed_. Example:

    www.aliyun.com and failed_*

    Note Field query only supports suffixes with asterisks (*). Prefixes with asterisks (*) are not supported.
• Field query

  The comparison of numeric type fields can be implemented in the format of field: value or field>=value, with the combination of and, and or. It can also be used together with full-text search, with the combination of and and or.

  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 as level, 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.

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 the date_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 function date_trunc provides alignment for other units, including second, minute, hour, week, month, and year. 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 function from_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 and date_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 source ipwarn_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 provider ip_to_provider, determining whether ip_to_domain is an Internet or Intranet IP address, see IP geographic functions.