本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

# AWS WAF 日誌的範例查詢
<a name="query-examples-waf-logs"></a>

本節中有許多範例查詢使用先前建立的分割區投影資料表。請根據您的需求修改範例中的資料表名稱、資料欄值及其他變數。若要改善查詢的效能並降低成本，請在篩選條件中新增分割區資料欄。

**Topics**
+ [計數推薦網站、IP 位址或相符規則](query-examples-waf-logs-count.md)
+ [使用日期和時間的查詢](query-examples-waf-logs-date-time.md)
+ [查詢封鎖的請求或地址](query-examples-waf-logs-blocked-requests.md)

# 計數推薦網站、IP 位址或相符規則
<a name="query-examples-waf-logs-count"></a>

本節中的範例會查詢感興趣的日誌項目計數。
+ [Count the number of referrers that contain a specified term](#waf-example-count-referrers-with-specified-term)
+ [Count all matched IP addresses in the last 10 days that have matched excluded rules](#waf-example-count-matched-ip-addresses)
+ [Group all counted managed rules by the number of times matched](#waf-example-group-managed-rules-by-times-matched)
+ [Group all counted custom rules by number of times matched](#waf-example-group-custom-rules-by-times-matched)

**Example – 計算包含指定字詞的 Referrer 數量**  
以下查詢會計算在指定的日期範圍內包含 "amazon" 一詞的 Referrer 數量。  

```
WITH test_dataset AS 
  (SELECT header FROM waf_logs
    CROSS JOIN UNNEST(httprequest.headers) AS t(header) WHERE "date" >= '2021/03/01'
    AND "date" < '2021/03/31')
SELECT COUNT(*) referer_count 
FROM test_dataset 
WHERE LOWER(header.name)='referer' AND header.value LIKE '%amazon%'
```

**Example – 計算過去 10 天內符合排除規則的所有相符 IP 地址**  
以下查詢會計算過去 10 天內 IP 地址符合規則群組中排除規則的次數。  

```
WITH test_dataset AS 
  (SELECT * FROM waf_logs 
    CROSS JOIN UNNEST(rulegrouplist) AS t(allrulegroups))
SELECT 
  COUNT(*) AS count, 
  "httprequest"."clientip", 
  "allrulegroups"."excludedrules",
  "allrulegroups"."ruleGroupId"
FROM test_dataset 
WHERE allrulegroups.excludedrules IS NOT NULL AND from_unixtime(timestamp/1000) > now() - interval '10' day
GROUP BY "httprequest"."clientip", "allrulegroups"."ruleGroupId", "allrulegroups"."excludedrules"
ORDER BY count DESC
```

**Example – 依相符的次數對所有計數的受管規則進行分組**  
如果您在 2022 年 10 月 27 日之前將規則群組規則動作設定為 Web ACL 組態中的計數，則 會將 Web ACL JSON 中的覆寫 AWS WAF 儲存為 `excludedRules`。現在，將規則覆寫為計數的 JSON 設定位於 `ruleActionOverrides` 設定中。如需詳細資訊，請參閱《*AWS WAF 開發人員指南*》中的[規則群組中的動作覆寫](https://docs.aws.amazon.com/waf/latest/developerguide/web-acl-rule-group-override-options.html)。若要從新的日誌結構擷取「計數」模式下的受管規則，請查詢 `ruleGroupList` 區段中的 `nonTerminatingMatchingRules` 而非 `excludedRules` 欄位，如下列範例所示。  

```
SELECT
 count(*) AS count,
 httpsourceid,
 httprequest.clientip,
 t.rulegroupid, 
 t.nonTerminatingMatchingRules
FROM "waf_logs" 
CROSS JOIN UNNEST(rulegrouplist) AS t(t) 
WHERE action <> 'BLOCK' AND cardinality(t.nonTerminatingMatchingRules) > 0 
GROUP BY t.nonTerminatingMatchingRules, action, httpsourceid, httprequest.clientip, t.rulegroupid 
ORDER BY "count" DESC 
Limit 50
```

**Example – 依相符的次數對所有計數的自訂規則進行分組**  
下列查詢會依符合的次數，將所有計數的自訂規則分組。  

```
SELECT
  count(*) AS count,
         httpsourceid,
         httprequest.clientip,
         t.ruleid,
         t.action
FROM "waf_logs" 
CROSS JOIN UNNEST(nonterminatingmatchingrules) AS t(t) 
WHERE action <> 'BLOCK' AND cardinality(nonTerminatingMatchingRules) > 0 
GROUP BY t.ruleid, t.action, httpsourceid, httprequest.clientip 
ORDER BY "count" DESC
Limit 50
```

如需有關自訂規則和受管規則群組的日誌位置的資訊，請參閱《*AWS WAF 開發人員指南*》中的[監控和調校](https://docs.aws.amazon.com/waf/latest/developerguide/web-acl-testing-activities.html)。

# 使用日期和時間的查詢
<a name="query-examples-waf-logs-date-time"></a>

本節中的範例包括使用日期和時間值的查詢。
+ [Return the timestamp field in human-readable ISO 8601 format](#waf-example-return-human-readable-timestamp)
+ [Return records from the last 24 hours](#waf-example-return-records-last-24-hours)
+ [Return records for a specified date range and IP address](#waf-example-return-records-date-range-and-ip)
+ [For a specified date range, count the number of IP addresses in five minute intervals](#waf-example-count-ip-addresses-in-date-range)
+ [Count the number of X-Forwarded-For IP in the last 10 days](#waf-example-count-x-forwarded-for-ip)

**Example – 以人類看得懂的 ISO 8601 格式傳回時間戳記欄位**  
以下查詢會使用 `from_unixtime` 和 `to_iso8601` 函數，以人類看得懂的 ISO 8601 格式傳回 `timestamp` 欄位 (例如 `2019-12-13T23:40:12.000Z`，而非 `1576280412771`) 查詢也會傳回 HTTP 來源名稱、來源 ID 和請求。  

```
SELECT to_iso8601(from_unixtime(timestamp / 1000)) as time_ISO_8601,
       httpsourcename,
       httpsourceid,
       httprequest
FROM waf_logs
LIMIT 10;
```

**Example – 傳回過去 24 小時的記錄**  
以下查詢會使用 `WHERE` 子句中的篩選條件，傳回過去 24 小時記錄的 HTTP 來源名稱、HTTP 來源 ID 和 HTTP 請求欄位。  

```
SELECT to_iso8601(from_unixtime(timestamp/1000)) AS time_ISO_8601, 
       httpsourcename, 
       httpsourceid, 
       httprequest 
FROM waf_logs
WHERE from_unixtime(timestamp/1000) > now() - interval '1' day
LIMIT 10;
```

**Example – 傳回指定日期範圍和 IP 地址的記錄**  
以下查詢會列出指定用戶端 IP 地址的指定日期範圍內的記錄。  

```
SELECT * 
FROM waf_logs 
WHERE httprequest.clientip='53.21.198.66' AND "date" >= '2021/03/01' AND "date" < '2021/03/31'
```

**Example – 如果是指定的日期範圍，則會計算每隔五分鐘的 IP 地址數量**  
以下查詢會針對特定日期範圍，計算每隔五分鐘的 IP 地址數量。  

```
WITH test_dataset AS 
  (SELECT 
     format_datetime(from_unixtime((timestamp/1000) - ((minute(from_unixtime(timestamp / 1000))%5) * 60)),'yyyy-MM-dd HH:mm') AS five_minutes_ts,
     "httprequest"."clientip" 
     FROM waf_logs 
     WHERE "date" >= '2021/03/01' AND "date" < '2021/03/31')
SELECT five_minutes_ts,"clientip",count(*) ip_count 
FROM test_dataset 
GROUP BY five_minutes_ts,"clientip"
```

**Example – 計算過去 10 天內的 X-Forwarded-For IP 數量**  
以下查詢會篩選請求標頭，並計算過去 10 天內的 X-Forwarded-For IP 數量。  

```
WITH test_dataset AS
  (SELECT header
   FROM waf_logs
   CROSS JOIN UNNEST (httprequest.headers) AS t(header)
   WHERE from_unixtime("timestamp"/1000) > now() - interval '10' DAY) 
SELECT header.value AS ip,
       count(*) AS COUNT 
FROM test_dataset 
WHERE header.name='X-Forwarded-For' 
GROUP BY header.value 
ORDER BY COUNT DESC
```

如需有關日期和時間函數的詳細資訊，請參閱 Trino 文件中的 [Date and time functions and operators](https://trino.io/docs/current/functions/datetime.html) (日期和時間函數和運算子)。

# 查詢封鎖的請求或地址
<a name="query-examples-waf-logs-blocked-requests"></a>

本節中的範例會查詢封鎖的請求或地址。
+ [Extract the top 100 IP addresses blocked by a specified rule type](#waf-example-extract-top-100-blocked-ip-by-rule)
+ [Count the number of times a request from a specified country has been blocked](#waf-example-count-request-blocks-from-country)
+ [Count the number of times a request has been blocked, grouping by specific attributes](#waf-example-count-request-blocks-by-attribute)
+ [Count the number of times a specific terminating rule ID has been matched](#waf-example-count-terminating-rule-id-matches)
+ [Retrieve the top 100 IP addresses blocked during a specified date range](#waf-example-top-100-ip-addresses-blocked-for-date-range)

**Example – 擷取遭到指定規則類型封鎖的前 100 個 IP 地址**  
以下查詢會擷取並計算在指定日期範圍內，已遭到 `RATE_BASED` 終止規則封鎖的前 100 個 IP 地址。  

```
SELECT COUNT(httpRequest.clientIp) as count,
httpRequest.clientIp
FROM waf_logs
WHERE terminatingruletype='RATE_BASED' AND action='BLOCK' and "date" >= '2021/03/01'
AND "date" < '2021/03/31'
GROUP BY httpRequest.clientIp
ORDER BY count DESC
LIMIT 100
```

**Example – 計算來自指定國家/地區遭到封鎖的請求次數**  
以下查詢會計算來自愛爾蘭 (IE) 的 IP 地址，並遭 `RATE_BASED` 終止規則封鎖的請求次數。  

```
SELECT 
  COUNT(httpRequest.country) as count, 
  httpRequest.country 
FROM waf_logs
WHERE 
  terminatingruletype='RATE_BASED' AND 
  httpRequest.country='IE'
GROUP BY httpRequest.country
ORDER BY count
LIMIT 100;
```

**Example – 計算遭封鎖的請求次數 (依特定屬性分組)**  
以下查詢會計算遭封鎖的請求次數，並以 WebACL、RuleId、ClientIP 和 HTTP Request URI 分組。  

```
SELECT 
  COUNT(*) AS count,
  webaclid,
  terminatingruleid,
  httprequest.clientip,
  httprequest.uri
FROM waf_logs
WHERE action='BLOCK'
GROUP BY webaclid, terminatingruleid, httprequest.clientip, httprequest.uri
ORDER BY count DESC
LIMIT 100;
```

**Example – 計算與特定終止規則 ID 相符的次數。**  
以下查詢會計算與特定終止規則 ID (`WHERE terminatingruleid='e9dd190d-7a43-4c06-bcea-409613d9506e'`) 相符的次數。查詢接著會以 WebACL、Action、ClientIP 和 HTTP Request URI 將結果分組。  

```
SELECT 
  COUNT(*) AS count,
  webaclid,
  action,
  httprequest.clientip,
  httprequest.uri
FROM waf_logs
WHERE terminatingruleid='e9dd190d-7a43-4c06-bcea-409613d9506e'
GROUP BY webaclid, action, httprequest.clientip, httprequest.uri
ORDER BY count DESC
LIMIT 100;
```

**Example – 擷取指定日期範圍內遭到封鎖的前 100 個 IP 地址**  
以下查詢會擷取在指定日期範圍內，已遭到封鎖的前 100 個 IP 地址。該查詢也會列出 IP 地址遭到封鎖的次數。  

```
SELECT "httprequest"."clientip", "count"(*) "ipcount", "httprequest"."country"
FROM waf_logs
WHERE "action" = 'BLOCK' and "date" >= '2021/03/01'
AND "date" < '2021/03/31'
GROUP BY "httprequest"."clientip", "httprequest"."country"
ORDER BY "ipcount" DESC limit 100
```