如何通過SQL語句查詢和分析日誌 - Security Center

在Security Center日誌分析頁面，您可以對日誌進行自訂查詢與分析，查詢多種複雜情境下的日誌。本文介紹使用查詢和分析語句的方法。

概述

在風險治理 > 日誌分析頁面的查詢/分析框中，您可以對日誌進行自訂查詢和分析。日誌查詢語句由查詢文法（Search）和分析文法（Analytics）兩部分組成，中間通過|進行分隔。

在對日誌進行自訂查詢和分析時，查詢文法和分析文法都是可選項。以下是查詢文法和分析文法的說明：

查詢（Search）：查詢條件可以由關鍵詞、模糊語句、數值、區間範圍和組合條件等產生。如果查詢條件為空白或星號（*），代表對該時間段所有資料不過濾任何條件，直接對所有查詢結果進行統計。
分析（Analytics）：對查詢結果或全量資料進行計算和統計。如果分析條件為空白，代表只返回查詢結果，不做統計。

查詢文法

Log Service查詢文法支援全文檢索查詢和欄位查詢，查詢方塊支援換行顯示、文法高亮等功能。

全文檢索查詢
不需要指定欄位，直接輸入關鍵字查詢。可以用雙引號（""）包裹關鍵字，多個關鍵字之間以空格或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組合。
Log Service支援基於欄位進行更精準的查詢。
- 查詢多欄位樣本
  搜尋所有嚴重等級的安全警示的日誌。例如：
```
__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文法對日誌資料進行分析與統計。Log Service支援的文法與函數詳細資料，請參見分析概述。

分析語句中可以省略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、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函數。