查询被阻止的请求或地址

本部分中的示例查询被阻止的请求或地址。

• Extract the top 100 IP addresses blocked by a specified rule type

• Count the number of times a request from a specified country has been blocked

• Count the number of times a request has been blocked, grouping by specific attributes

• Count the number of times a specific terminating rule ID has been matched

• Retrieve the top 100 IP addresses blocked during a specified date range

例

– 提取被指定规则类型阻止的前 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

例

– 计算来自指定国家/地区的请求被阻止的次数

以下查询针对来自属于爱尔兰 (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;

例

– 计算请求被阻止的次数，按特定属性分组

以下查询计算请求被阻止的次数，并按照 WebACL、RuleId、ClientIP 和 HTTP 请求 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;

例

– 计算特定终止规则 ID 匹配的次数

以下查询计算特定终止规则 ID 匹配的次数 (WHERE terminatingruleid='e9dd190d-7a43-4c06-bcea-409613d9506e')。然后，查询按照 WebACL、操作、ClientIP 和 HTTP 请求 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;

例

– 检索指定日期范围内被阻止的前 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