cloudflare waf日志常用分析语句(日志服务)

查看来自各个网络运营商的访问者的流量分布的SQL:

* | select ip_to_provider(ClientIP) as provider, round(sum(EdgeResponseBytes)/1024.0/1024.0, 3) as mb_in group by provider having ip_to_provider(ClientIP) <> '' order by mb_in desc limit 10

统计攻击来源国家及次数：

WAFAction:drop OR simulate OR challenge allow | SELECT ip_to_country(ClientIP) as country, count(1) as "攻击次数" group by country

攻击来源省份：

WAFAction:drop OR simulate OR challenge allow|SELECT ip_to_province(ClientIP) as province, 
         count(1) as "攻击次数" 
         group by province

最新攻击日志(来源ip、国家、userAgent、waf rule message)

WAFAction:drop OR simulate OR challenge allow|select EdgeEndTimestamp, ClientIP, ClientRequestUserAgent,WAFRuleMessage,ip_to_country(ClientIP) AS Country order by WAFRuleMessage desc limit 100

查询每5分钟被攻击的次数：

WAFAction:drop OR simulate OR challenge allow|select from_unixtime(__time__ - __time__% 300) as dt, 
         count(1) as PV
      group by dt 
      order by dt 
      limit 1000

攻击者热力分布图：

WAFAction:drop OR simulate OR challenge allow|SELECT ip_to_geo(ClientIP) as geo, 
         count(1) as "攻击次数" 
         group by geo
         limit 10000