如何通过SQL语句查询和分析日志_云安全中心(Security Center)-阿里云帮助中心

在云安全中心日志分析页面，您可以对日志进行自定义查询与分析，查询多种复杂场景下的日志。本文介绍使用查询和分析语句的方法。

概述

在风险治理 > 日志分析页面的查询/分析框中，您可以对日志进行自定义查询和分析。日志查询语句由查询语法（Search）和分析语法（Analytics）两部分组成，中间通过|进行分隔。

在对日志进行自定义查询和分析时，查询语法和分析语法都是可选项。以下是查询语法和分析语法的说明：

查询（Search）：查询条件可以由关键词、模糊语句、数值、区间范围和组合条件等产生。如果查询条件为空或星号（*），代表对该时间段所有数据不过滤任何条件，直接对所有查询结果进行统计。
分析（Analytics）：对查询结果或全量数据进行计算和统计。如果分析条件为空，代表只返回查询结果，不做统计。

查询语法

日志服务查询语法支持全文查询和字段查询，查询框支持换行显示、语法高亮等功能。

全文查询
不需要指定字段，直接输入关键字查询。可以用双引号（""）包裹关键字，多个关键字之间以空格或and分割。以下是全文查询的常用示例：
- 多关键字查询示例
  搜索所有包含www.aliyundoc.com和404的日志。例如：
```
www.aliyundoc.com 404
```
  或者：
```
www.aliyundoc.com and 404
```
- 条件查询示例
  搜索所有包含www.aliyundoc.com并且包含error或者404的日志。例如：
```
www.aliyundoc.com and (error or 404)
```
- 模糊查询示例
  搜索所有包含www.aliyundoc.com并且包含failed_开头关键字的日志。例如：
```
www.aliyundoc.com and failed_*
```
  说明
  全文查询只支持后缀加*，不支持前缀加*。
字段查询
可实现数值类型字段的比较，格式为字段：值或字段>=值，通过and、or等进行组合。也可以和全文搜索组合使用，同样通过and、or组合。
日志服务支持基于字段进行更精准的查询。
- 查询多字段示例
  搜索所有严重等级的安全报警的日志。例如：
```
__topic__ : sas-security-log and level: serious
```
  搜索某个客户端1.2.XX.XX上所有的SSH登录日志。例如：
```
__topic__:aegis-log-login and ip:1.2.XX.XX and warn_type:SSHLOGIN
```
  说明
  每条日志中都包含一个__topic__字段表示主题，日志都是通过该字段来区分。示例中用的字段level、warn_type、ip等都是特定日志类型的字段。
- 查询数值字段示例
  搜索所有响应时间超过1秒的本地DNS查询日志。例如：
```
__topic__:local-dns and time_usecond > 1000000
```
  也支持区间查询，查询响应时间大于1秒且小于等于10秒的本地DNS查询日志。例如：
```
__topic__:local-dns and time_usecond in [1000000,10000000]
```
详细的查询语法说明，请参见查询概述。

分析语法

您可以使用SQL 92语法对日志数据进行分析与统计。日志服务支持的语法与函数详细信息，请参见分析概述。

分析语句中可以省略SQL标准语法中的from表格名语句，即from log。

日志数据默认返回前100条，您可以使用LIMIT语法修改返回数据的条数。更多信息，请参见LIMIT子句。

基于日志时间的查询分析

每条日志都有一个内置字段__time__，表示这条日志的时间，以便在统计时进行基于时间的计算。其格式为Unix时间戳，本质是一个自从1970-01-01 00:00:00 UTC时间开始的累计过去的秒数。因此实际使用时，经过可选的计算后，需要格式化才可以展示。

选择并展示时间
这里在特定时间范围内，选择IP为1.2.XX.XX的最新10条登录日志，展示其中时间、来源IP以及登录类型。例如：
```
__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
```
计算时间
查询登录过后的天数，可以使用__time__进行计算。例如：
```
__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)，先用to_unixtime将now()获取的时间转化为Unix时间戳，再与内置时间字段__time__相减，获得已经过去的时间秒数。最后除以86400，即一天的总秒数，再用函数round(data, 1)圆整为小数点后1位数的值，可得出每条攻击日志距离现在已经过去了几天。
基于特定时间分组统计
如果想知道特定时间范围内某个设备的登录趋势，可使用如下SQL：
```
__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
```
这里使用内置字段__time__，传给函数date_trunc('day', ..)对时间按天对齐，将每条日志分组到了其所属的天的分区中进行统计总数（count(1)），并按照分区时间块排序。函数date_trunc第一个参数提供更多其他单位进行对齐，包括second、minute、hour、week、month、year等，函数说明，请参见日期和时间函数。
基于灵活时间分组统计
如果想知道更灵活的分组时间规律，例如整个账户下设备每5分钟的登录趋势，可以使用如下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                        
```
使用计算的内置时间字段计算__time__ - __time__% 300，同时使用函数from_unixtime进行格式化，将每条日志分组到了一个5分钟（300秒）的分区中进行统计总数（count(1)），并按照分区时间块排序，获得前1000条，相当于选择时间内的前83小时的数据。
更多关于时间解析的函数，例如将一个时间格式转化为另外一个格式，需要使用date_parse与date_format，函数说明，请参见日期和时间函数。

基于客户端IP的查询分析

日志中warn_ip表示登录日志的登录源IP。

登录源国家分布
查询某个设备登录来源的国家分布，例如：
```
__topic__: aegis-log-login and uuid: 12344567
| SELECT ip_to_country(warn_ip) as country,
count(1) as "登录次数"
group by country
```
这里先用函数ip_to_country得到这个登录源IPwarn_ip对应的国家信息。
登录者身份分布
使用函数ip_to_province获得更详细的基于省份的登录者分布，例如：
```
__topic__: aegis-log-login and uuid: 12344567
| SELECT ip_to_province(warn_ip) as province,
         count(1) as "登录次数"
         group by province
```
这里使用了另外一个IP函数ip_to_province来获得一个IP的所属省份。如果是中国以外的IP地址，会尝试转化为其国家所属省份（州），但在选择中国地图展示时，会无法展示出来。
登录者热力分布
使用函数ip_to_geo获得一张登录者的热力图：
```
__topic__: aegis-log-login and uuid: 12344567
| SELECT ip_to_geo(warn_ip) as geo,
         count(1) as "登录次数"
         group by geo
         limit 10000
```
这里使用了另一个IP函数ip_to_geo来获得一个IP的所在经纬度，并获取前1万条。
说明
了解基于IP的更多解析功能，例如获得IP所属运营商ip_to_provider、判断IP是内网还是外网ip_to_domain等，请参见IP函数。