自定义日志查询与分析 - 安骑士

日志查询语句由查询语法（Search）和分析语法（Analytics）两部分组成，中间通过 |进行分隔：

查询语法和分析语法都可选。

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

查询语法

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

全文查询
不需要指定字段，直接输入关键字查询。可以用双引号（""）包裹关键字，多个关键字之间以空格或and分割。
- 多关键字查询示例
  搜索所有包含www.aliyun.com和404的日志。例如：
```
www.aliyun.com 404
```
  或者：
```
www.aliyun.com and 404
```
- 条件查询示例
  搜索所有包含www.aliyun.com并且包含error或者404的日志。例如：
```
www.aliyun.com and (error and 404)
```
- 前缀查询示例
  搜索所有包含www.aliyun.com并且包含failed_开头关键字的日志。例如：
```
www.aliyun.com and failed_*
```
  说明字段查询只支持后缀加 *，不支持前缀加 *。
字段查询
可实现数值类型字段的比较，格式为字段：值或字段>=值，通过and、or等进行组合。也可以和全文搜索组合使用，同样通过and、or组合。

日志服务支持基于字段进行更精准的查询。
- 查询多字段示例
  搜索所有严重等级的安全报警的日志。例如：
```
__topic__ : sas-security-log and level: serious
```
  搜索某个客户端1.2.3.4上所有的SSH登录。例如：
```
__topic__:aegis-log-login and ip:1.2.3.4 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语法修改返回范围。

基于日志时间的查询分析

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

选择并展示时间
这里在特定时间范围内，选择ip为1.2.3.4的最新10条登录日志，展示其中时间、来源IP以及登录类型。例如：
```
__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
```
计算时间
查询登录过后的天数，可以使用 __time__进行计算。例如：
```
__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
```
这里使用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.3.4
| 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、miniute、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地理函数。