WAF访问日志中心

前提条件

• 已采集WAF日志，详情请参考WAF接入。

• 日志配置结构化，详情请参见结构化配置。

背景信息

Web应用防火墙（Web Application Firewall， WAF），通过对HTTP(S)请求进行检测，识别并阻断SQL注入、跨站脚本攻击、网页木马上传、命令/代码注入、文件包含、敏感文件访问、第三方应用漏洞攻击、CC攻击、恶意爬虫扫描、跨站请求伪造等攻击，保护Web服务安全稳定。

分析网站访问情况

1. 登录云日志服务控制台。
2. 在左侧导航栏中选择“仪表盘 ”。
3. 在仪表盘模板下方，选择“WAF仪表盘模板>WAF访问日志中心”仪表盘，查看图表详情。

WAF访问日志中心仪表盘中的重要图表说明如下所示：

• PV图表展示所选时间内的总调用次数以及与前一天同时段的趋势对比，所关联的查询分析语句如下所示：

  SELECT       CASE               WHEN       diff [ 1 ] < 1000 THEN       concat( cast( diff [ 1 ] AS VARCHAR ), ' 次' )        WHEN diff [ 1 ] < 1000 * 1000 THEN       concat( cast( round( diff [ 1 ]/ 1000, 1 ) AS VARCHAR ), ' 千次' )        WHEN diff [ 1 ] < 1000000000 THEN       concat( cast( round( diff [ 1 ]/ 1000000.0, 1 ) AS VARCHAR ), ' 百万次' )        WHEN diff [ 1 ]/ 1000.0 < 1000000000 THEN       concat( cast( round( diff [ 1 ]/ 1000 / 1000000.0, 1 ) AS VARCHAR ), ' 十亿次' ) ELSE concat( cast( round( diff [ 1 ]/ 1000.0 / 1000 / 1000 / 1000, 1 ) AS VARCHAR ), ' 万亿次' )        END AS        "VALUE"       ,       CASE WHEN diff [ 2 ]= 0 THEN 0 ELSE round( diff [ 3 ]- 1, 2 ) END AS ratio        FROM       (       SELECT       compare ( DATA, 86400 ) AS diff        FROM       ( SELECT count( 1 ) AS DATA FROM log )        )

• UV图表展示所选时间内有多少不同的ip发起了调用以及与前一天的趋势对比，所关联的查询分析语句如下所示：

  SELECT       CASE               WHEN       diff [ 1 ] < 1000 THEN       concat( cast( diff [ 1 ] AS VARCHAR ), ' 次' )        WHEN diff [ 1 ] < 1000 * 1000 THEN       concat( cast( round( diff [ 1 ]/ 1000, 1 ) AS VARCHAR ), ' 千次' )        WHEN diff [ 1 ] < 1000000000 THEN       concat( cast( round( diff [ 1 ]/ 1000000.0, 1 ) AS VARCHAR ), ' 百万次' )        WHEN diff [ 1 ]/ 1000.0 < 1000000000 THEN       concat( cast( round( diff [ 1 ]/ 1000 / 1000000.0, 1 ) AS VARCHAR ), ' 十亿次' ) ELSE concat( cast( round( diff [ 1 ]/ 1000.0 / 1000 / 1000 / 1000, 1 ) AS VARCHAR ), ' 万亿次' )        END AS        "VALUE"       ,       CASE               WHEN diff [ 2 ]= 0 THEN       0 ELSE round( diff [ 3 ]- 1, 2 )       END AS ratio        FROM       (       SELECT       compare ( DATA, 86400 ) AS diff        FROM       ( SELECT count( DISTINCT CASE WHEN sip = '-' THEN remote_ip ELSE sip END ) AS "DATA" FROM log )        )

• 流入流量图表展示请求流量大小以及与前一天的趋势对比，所关联的查询分析语句如下所示：

  SELECT       CASE               WHEN       diff [ 1 ] < 102 THEN       concat( cast( diff [ 1 ] AS VARCHAR ), ' B' )        WHEN diff [ 1 ] < 1024 * 1024 THEN       concat( cast( round( diff [ 1 ]/ 1024, 1 ) AS VARCHAR ), ' KB' )        WHEN diff [ 1 ] < 1024 * 1024 * 1024 THEN       concat( cast( round( diff [ 1 ]/ 1024.0 / 1024, 1 ) AS VARCHAR ), ' MB' )        WHEN diff [ 1 ]/ 1024.0 < 1024 * 1024 * 1024 THEN       concat( cast( round( diff [ 1 ]/ 1024.0 / 1024 / 1024, 1 ) AS VARCHAR ), ' GB' ) ELSE concat( cast( round( diff [ 1 ]/ 1024.0 / 1024 / 1024 / 1024, 1 ) AS VARCHAR ), ' TB' )        END AS        "VALUE"       ,       CASE               WHEN diff [ 2 ]= 0 THEN       0 ELSE round( diff [ 3 ]- 1, 2 )       END AS ratio        FROM       (       SELECT       compare ( "DATA", 86400 ) AS diff        FROM       ( SELECT COALESCE ( sum( request_length ), 0 ) AS "DATA" FROM log )        )

• 网络in带宽峰值展示所选时间段内请求流量带宽峰值以及与前一天同时段的趋势对比，所关联的查询分析语句如下所示：

  SELECT       CASE               WHEN       diff [ 1 ] < 102 THEN       concat( cast( round( diff [ 1 ], 2 ) AS VARCHAR ), ' B/s' )        WHEN diff [ 1 ] < 1024 * 1024  THEN       concat( cast( round( diff [ 1 ]/ 1024, 1 ) AS VARCHAR ), ' KB/s' )         WHEN diff [ 1 ] < 1024 * 1024 * 1024  THEN       concat( cast( round( diff [ 1 ]/ 1024.0 / 1024, 1 ) AS VARCHAR ), ' MB/s' )          WHEN diff [ 1 ]/ 1024.0 < 1024 * 1024 * 1024  THEN       concat( cast( round( diff [ 1 ]/ 1024.0 / 1024 / 1024, 1 ) AS VARCHAR ), ' GB/s' )  ELSE concat( cast( round( diff [ 1 ]/ 1024.0 / 1024 / 1024 / 1024, 1 ) AS VARCHAR ), ' TB/s' )        END AS        "VALUE"       ,       CASE               WHEN diff [ 2 ]= 0 THEN       0 ELSE round( diff [ 3 ]- 1, 2 )       END AS ratio        FROM       (       SELECT       compare ( "DATA", 86400 ) AS diff        FROM       (       SELECT COALESCE       ( max( "DATA" ), 0 ) AS "DATA"        FROM       ( SELECT TIME_FLOOR(__time,'PT1M') AS dt, sum( request_length )/ 60.0 AS "DATA" FROM log GROUP BY dt  LIMIT 10000 ))        )

• 网络out带宽峰值展示所选时间段内响应流量带宽峰值以及与前一天同时段的趋势对比，所关联的查询分析语句如下所示：

  SELECT CASE WHEN diff [ 1 ] < 102 THEN concat( cast( round( diff [ 1 ], 2 ) AS VARCHAR ), ' B/s' )  WHEN diff [ 1 ] < 1024 * 1024 THEN concat( cast( round( diff [ 1 ]/ 1024, 1 ) AS VARCHAR ), ' KB/s' ) WHEN diff [ 1 ] < 1024 * 1024 * 1024 THEN concat( cast( round( diff [ 1 ]/ 1024.0 / 1024, 1 ) AS VARCHAR ), ' MB/s' ) WHEN diff [ 1 ]/ 1024.0 < 1024 * 1024 * 1024 THEN concat( cast( round( diff [ 1 ]/ 1024.0 / 1024 / 1024, 1 ) AS VARCHAR ), ' GB/s' ) ELSE concat( cast( round( diff [ 1 ]/ 1024.0 / 1024 / 1024 / 1024, 1 ) AS VARCHAR ), ' TB/s' ) END AS "value", case when diff [ 2 ]= 0 then 0 else round( diff [ 3 ]- 1, 2 ) END AS "ratio" FROM ( SELECT compare ( "DATA", 86400 ) AS diff FROM ( SELECT COALESCE ( max( bytes_out ), 0 ) AS "DATA" FROM ( SELECT time_ceil( __time,'PT1M') AS dt, sum( body_bytes_sent )/ 60.0 AS bytes_out FROM log GROUP BY dt LIMIT 10000 )))

• 流量带宽趋势图展示流入流量及流出流量随时间的变化情况，所关联的查询分析语句如下所示：

  SELECT       TIME_FORMAT( MILLIS_TO_TIMESTAMP( TIMESTAMP_TO_MILLIS(__time) - MOD(TIMESTAMP_TO_MILLIS(__time) , 600000)), 'HH:mm' ) AS dt,       round( sum( request_length )/ 1024.0 / 600, 2 ) AS "流入流量(KB/s)",       round( sum( body_bytes_sent )/ 1024.0 / 600, 2 ) AS "流出流量(KB/s)"    where   request_length is not null    GROUP BY       TIMESTAMP_TO_MILLIS(__time) - MOD(TIMESTAMP_TO_MILLIS(__time) , 600000)       ORDER BY       dt        LIMIT 1000

• PV/UV趋势图展示访问次数变化情况，所关联的查询分析语句如下所示：

  SELECT       TIME_FORMAT(MILLIS_TO_TIMESTAMP( TIMESTAMP_TO_MILLIS(__time) - MOD(TIMESTAMP_TO_MILLIS(__time) , 3600000)) , 'HH:mm' ) AS dt,       count( 1 ) AS PV,       APPROX_COUNT_DISTINCT (CASE WHEN sip = '-' THEN remote_ip ELSE sip       END) AS UV       FROM log       GROUP BY       TIMESTAMP_TO_MILLIS(__time) - MOD(TIMESTAMP_TO_MILLIS(__time) , 3600000)        ORDER BY       dt        LIMIT 1000

• 访问状态分布图展示不同请求响应的错误码随时间变化情况，所关联的查询分析语句如下所示：

  SELECT       TIME_FORMAT(MILLIS_TO_TIMESTAMP( TIMESTAMP_TO_MILLIS(__time) - MOD(TIMESTAMP_TO_MILLIS(__time) , 3600000)) , 'HH:mm' ) AS dt,       count( 1 ) AS cnt,       concat( cast( "response_code" / 100 AS VARCHAR ), 'XX' ) AS "status"        GROUP BY       TIMESTAMP_TO_MILLIS(__time) - MOD(TIMESTAMP_TO_MILLIS(__time) , 3600000) ,       "response_code" / 100        ORDER BY       dt DESC        LIMIT 10000

• 访问来源图展示访问IP来源再国内的省份分布，所关联的查询分析语句如下所示：

  SELECT       ip_to_province (CASE WHEN sip = '-' THEN remote_ip ELSE sip END) AS country,       count( 1 ) AS "访问次数"    where ip_to_country(CASE WHEN sip = '-' THEN remote_ip ELSE sip END) = '中国'    GROUP BY       country

• 流入流量来源（中国）图展示流入流量地区在国内的分布情况，所关联的查询分析语句如下所示：

  SELECT       ip_to_province (CASE WHEN sip = '-' THEN remote_ip ELSE sip END) AS region,       round( sum( request_length )/ 1024.0 / 1024, 4 ) AS "流入流量(MB)"  where ip_to_country(CASE WHEN sip = '-' THEN remote_ip ELSE sip END) = '中国'     GROUP BY       region

• 流入流量来源（世界）图展示流入流量地区在世界的分布情况，所关联的查询分析语句如下所示：

  SELECT       ip_to_country (CASE WHEN sip = '-' THEN remote_ip ELSE sip END) AS region,       round( sum( request_length )/ 1024.0 / 1024, 4 ) AS "流入流量(MB)"   where   request_length is not null     GROUP BY       region 

• 来源网络提供商图展示各个来源网络提供商的占比，所关联的查询分析语句如下所示：

  SELECT       ip_to_provider (CASE WHEN sip = '-' THEN remote_ip ELSE sip END) AS provider,       round( sum( request_length )/ 1024.0 / 1024.0, 3 ) AS mb_in   where   request_length is not null    GROUP BY       provider        HAVING       ip_to_provider (CASE WHEN sip = '-' THEN remote_ip ELSE sip END) != '*'        ORDER BY       mb_in DESC        LIMIT 10 

• 访问域名图展示被访问域名的访问次数，所关联的查询分析语句如下所示：

  SELECT        http_host,       count( 1 ) AS "被访问次数"        GROUP BY       http_host        ORDER BY       "被访问次数" DESC        LIMIT 30

• 响应最慢的URL图展示响应时间最慢的URL，所关联的查询分析语句如下所示：

  SELECT http_host AS "网站",url_extract_path (COALESCE ( url, '/' )) AS URL,sum( request_time )/ count( 1 ) AS "响应时间(毫秒)",count( 1 ) AS "访问次数" GROUP BY http_host, url ORDER BY "响应时间(毫秒)" DESC  LIMIT 100

• 访问最多的客户端图展示所选时间段内访问次数最多ip所属地区，所关联的查询分析语句如下所示：

  SELECT  ip AS "客户端",  client AS "地理网络",  concat(  cast( (CASE WHEN pv IS NULL THEN 0 ELSE pv END) AS VARCHAR ),  ' (',  cast( case when  head_pv  = 'null'  then 0 else (case when head_pv > 0 then head_pv else 0 end) end AS VARCHAR ),  '/',  cast( case when  get_pv  = 'null'  then 0 else (case when get_pv > 0 then get_pv else 0 end) end AS VARCHAR ),  '/',  cast( case when  put_pv  = 'null'  then 0 else (case when put_pv > 0 then put_pv else 0 end) end AS VARCHAR ),  '/',  cast( case when  post_pv  = 'null'  then 0 else (case when post_pv > 0 then post_pv else 0 end) end AS VARCHAR ),  '/',  cast( case when  delete_pv  = 'null'  then 0 else (case when delete_pv > 0 then delete_pv else 0 end) end AS VARCHAR ),  '/',  ')'   ) AS "PV (Head, Get, Put, Post, Delete方法)",  error_count AS "错误访问次数"   FROM  (  SELECT  ip,  client,  sum( CASE WHEN "method" = 'PUT' AND "status" < 400 THEN pv ELSE 0 END ) AS put_pv,  sum( CASE WHEN "method" = 'GET' AND "status" < 400 THEN pv ELSE 0 END ) AS get_pv,  sum( CASE WHEN "method" = 'POST' AND "status" < 400 THEN pv ELSE 0 END ) AS post_pv,  sum( CASE WHEN "method" = 'DELETE' AND "status" < 400 THEN pv ELSE 0 END ) AS delete_pv,  sum( CASE WHEN "method" = 'HEAD' AND "status" < 400 THEN pv ELSE 0 END ) AS head_pv,  sum( throughput ) AS throughput,  sum( pv ) AS pv,  sum( CASE WHEN "status" < 400 THEN 1 ELSE 0 END ) AS error_count   FROM  (  SELECT  CASE     WHEN  sip = '-' THEN  remote_ip ELSE sip   END AS ip,  "method",  CASE     WHEN ip_to_country ( CASE WHEN sip = '-' THEN remote_ip ELSE sip END )= '上海' THEN  '中国上海'   WHEN ip_to_province ( CASE WHEN sip = '-' THEN remote_ip ELSE sip END )= '*' THEN  '未知IP'   WHEN ip_to_provider ( CASE WHEN sip = '-' THEN remote_ip ELSE sip END )= '内网IP' THEN  '内网IP' ELSE concat(  ip_to_country ( CASE WHEN sip = '-' THEN remote_ip ELSE sip END ),  '/',  ip_to_province ( CASE WHEN sip = '-' THEN remote_ip ELSE sip END ),  '/',  CASE     WHEN ip_to_city ( CASE WHEN sip = '-' THEN remote_ip ELSE sip END )= '*' THEN  ' ' ELSE ip_to_city ( CASE WHEN sip = '-' THEN remote_ip ELSE sip END )   END,  ' ',  ip_to_provider ( CASE WHEN sip = '-' THEN remote_ip ELSE sip END ))   END AS client,  sum( CASE WHEN "response_code" < 400 THEN 1 ELSE 0 END ) AS pv,  round( sum( request_length )/ 1024.0 / 1024, 1 ) AS throughput,  "response_code" AS "status"   FROM     log  GROUP BY  ip,  client,  "method",  "response_code"   ORDER BY  pv DESC,  client,  "method"   LIMIT 1000  )   GROUP BY  ip,  client   ORDER BY  pv DESC   )   LIMIT 100