CDN仪表盘模板

前提条件

• 已采集CDN日志，详情请参见内容分发网络CDN接入LTS。
• 日志配置结构化，详情请参见设置云端结构化解析日志。

查看CDN错误分析

1. 登录云日志服务控制台，在左侧导航栏中选择“仪表盘”。
2. 在仪表盘模板下方，选择“CDN仪表盘模板 > CDN错误分析”，查看图表详情。
   • 错误域名访问Top5图表所关联的查询分析语句如下所示：

     select domain , count(*) as c where http_code > 400 group by domain order by c desc limit 5

   • 错误URI访问Top5图表所关联的查询分析语句如下所示：

     select uri , count(*) as c where http_code > 400 group by uri order by c desc limit 5

   • 错误请求状态分布图表所关联的查询分析语句如下所示：

     select http_code , count(*) as c where http_code > 400 group by http_code order by c desc

   • 错误按运营商统计图表所关联的查询分析语句如下所示：

     select ip_to_provider(client_ip) as isp , count(*) as c where http_code > 400 group by isp having ip_to_provider(client_ip) != '' order by c desc limit 10

   • 错误按客户端统计图表所关联的查询分析语句如下所示：

     select user_agent as "客户端版本", count(*) as "错误次数" where http_code > 400 group by user_agent order by "错误次数" desc limit 10

   • 错误按省份统计图表所关联的查询分析语句如下所示：

     select ip_to_province(client_ip) as province , count(*) as c where http_code > 400 and  IP_TO_COUNTRY (client_ip) = '中国' group by province order by c desc limit 50

   • 4XX错误详情图表所关联的查询分析语句如下所示：

     SELECT
           province AS "省份",
           isp AS "运营商",
           c AS "错误次数",
           round( c * 100.0 / sum( c ), 2 ) AS "错误比率(%)" 
          FROM
           (
           SELECT
            ip_to_province ( client_ip ) AS province,
            ip_to_provider ( client_ip ) AS isp,
            count(*) AS c 
           FROM
            log 
           WHERE
            http_code >= 400 
            AND http_code < 500 
           GROUP BY
            province,
            isp 
           HAVING
            (
            ip_to_provider ( client_ip )) != '' 
           ORDER BY
            c DESC 
           ) 
          GROUP BY
           province,
           isp,
           c

   • 5XX错误详情图表所关联的查询分析语句如下所示：

     SELECT
           province AS "省份",
           isp AS "运营商",
           c AS "错误次数",
           round( c * 100.0 / sum( c ), 2 ) AS "错误比率(%)" 
          FROM
           (
           SELECT
            ip_to_province ( client_ip ) AS province,
            ip_to_provider ( client_ip ) AS isp,
            count(*) AS c 
           FROM
            log 
           WHERE
                http_code >= 500
           GROUP BY
            province,
            isp 
           HAVING
            (
            ip_to_provider ( client_ip )) != '' 
           ORDER BY
            c DESC 
           ) 
          GROUP BY
           province,
           isp,
           c

   • 错误按国家统计图表所关联的查询分析语句如下所示：

     select ip_to_country(client_ip) as country , count(*) as c where http_code > 400  group by country order by c desc limit 50

查看CDN基础数据

1. 登录云日志服务控制台，在左侧导航栏中选择“仪表盘”。
2. 在仪表盘模板下方，选择“CDN仪表盘模板 > CDN基础数据”，查看图表详情。
   • 缓存命中率图表所关联的查询分析语句如下所示：

     select round(diff[1],2) as Hit_ratio, round(diff[2],2) as diff, round((diff[3]-1)*100, 2) from (select compare(Hit_ratio, 86400) as diff from (select sum(s) * 100.0/count(*) as Hit_ratio from (select case when hit_info = 'HIT' then 1 else 0 end as s from log)))

   • 下载速度图表所关联的查询分析语句如下所示：

     select round(diff[1],2) as speed, round(diff[2],2) as diff, round((diff[3]-1)*100, 2) from (select compare(speed, 86400) as diff from (select sum(response_size) * 1.0 /sum(response_time) as speed from log ))

   • 访问状态图表所关联的查询分析语句如下所示：

     select http_code , count(*) as c group by http_code order by c desc

   • 访问延时分布图表所关联的查询分析语句如下所示：

     select 
           case when response_time < 100 then '~100ms' 
           when response_time < 500 then '100~500ms' 
           when response_time < 1000 then '500ms~1s' 
           when response_time < 5000 then '1~5s' 
           when response_time < 6000 then '5~6s' 
           when response_time < 7000 then '6~7s' 
           when response_time < 8000 then '7~8s'
           when response_time < 10000 then '8~10s'
           when response_time < 15000 then '10~15s' 
           else '15s~' end as latency , 
           count(*) as cnt 
           group by latency 
           order by  cnt  

   • 请求带宽图表所关联的查询分析语句如下所示：

     select TIME_FORMAT (TIME_FLOOR(__time,'PT1M'), 'HH:mm', '+08:00') as thisdate, 
           sum(response_size) * 8/1000000000.0 as "带宽Gbit/min" 
           group by TIME_FLOOR(__time,'PT1M') 
           order by TIME_FLOOR(__time,'PT1M')

   • 访问次数/人数图表所关联的查询分析语句如下所示：

     select TIME_FORMAT (TIME_FLOOR(__time,'PT1M'), 'HH:mm', '+08:00') as thisdate, 
           count(*) as pv, APPROX_COUNT_DISTINCT(client_ip) as uv group by TIME_FLOOR(__time,'PT1M') order by TIME_FLOOR(__time,'PT1M')

   • 访问平均延时图表所关联的查询分析语句如下所示：

     select TIME_FORMAT (TIME_FLOOR(__time,'PT1M'), 'HH:mm', '+08:00') as thisdate, 
           avg(response_time) as "平均延时(ms)" group by TIME_FLOOR(__time,'PT1M') order by TIME_FLOOR(__time,'PT1M')

   • 请求命中率图表所关联的查询分析语句如下所示：

     select 
           TIME_FORMAT (TIME_FLOOR(m_time,'PT1M'), 'HH:mm', '+08:00' ) as thisdate , 
           sum(is_hit)*100.0/count(*) as hit_ratio 
           from  (select  TIME_FLOOR(__time,'PT1M') as m_time ,  case when hit_info = 'HIT' 
            then 1 else 0 end as is_hit from log ) group by m_time order by m_time

查看CDN用户分析

1. 登录云日志服务控制台，在左侧导航栏中选择“仪表盘”。
2. 在仪表盘模板下方，选择“CDN仪表盘模板 > CDN用户分析”，查看图表详情。
   • 访问次数图表所关联的查询分析语句如下所示：

     select diff[1] as pv,diff[2] as diff, round(100*(diff[3]-1), 2) from (select compare(pv, 86400) as diff from (select count(*) as pv from log))

   • 访问人数图表所关联的查询分析语句如下所示：

     select diff[1] as uv, diff[2] as diff, round((diff[3]-1)*100, 2) from (select compare(uv , 86400) as diff from (select APPROX_COUNT_DISTINCT(client_ip) as uv from log))

   • 访问客户端统计图表所关联的查询分析语句如下所示：

     select ua as "客户端" , sum(c) as "访问次数" from (select case when strpos(ua , 'iphone') > 1 then 'iphone' when strpos(ua, 'ipad') > 1 then 'ipad' when strpos(ua, 'android') > 1 then 'android' when strpos(ua, 'windows') > 1 then 'windows' when strpos(ua , 'mac') > 1 then 'mac' when strpos(ua, 'linux') > 1 then 'linux' else ua end as ua , c from (select count(*) as c , lower(user_agent) as ua from log group by ua order by c desc limit 2000) ) group by "客户端" order by "访问次数" desc limit 100

   • 运营商次数统计图表所关联的查询分析语句如下所示：

     select ip_to_provider(client_ip) as isp ,count(*) as "访问次数" group by isp order by "访问次数" desc limit 100

   • 访问地区分布图表所关联的查询分析语句如下所示：

     select ip_to_province(client_ip) as province , count(*) as cnt where IP_TO_COUNTRY (client_ip) = '中国' group by province HAVING province not in ('','保留地址','*') order by cnt desc limit 100

   • 有效访问用户TOP图表所关联的查询分析语句如下所示：

     SELECT  CASE    WHEN ip_to_country(client_ip) = '上海' THEN concat(client_ip, ' ( shanghai )')    WHEN ip_to_province(client_ip) = '' THEN concat(client_ip, ' ( Unknown IP )')    WHEN ip_to_provider(client_ip) = '内网IP' THEN concat(client_ip, ' ( Private IP )')    ELSE concat(      client_ip,      ' ( ',      ip_to_country(client_ip),      '/',      ip_to_province(client_ip),      '/',      CASE        WHEN ip_to_city(client_ip) = '-1' THEN 'Unknown city'        ELSE ip_to_city(client_ip)      END,      ' ',      ip_to_provider(client_ip),      ' )'    )  END AS client,  pv as "总访问数",  (pv - success_count) as "错误访问数",  round( CASE WHEN "throughput" > 0 THEN "throughput" ELSE 0 END, 1 ) AS "下载总量(GB)"from  (    select      client_ip,      count(*) as pv,      sum(response_size) / 1024.0 / 1024 / 1024.0 AS throughput,      sum(        CASE          WHEN http_code < 400 THEN 1          ELSE 0        END      ) AS success_count     from      log     group by      client_ip     order by      success_count desc    limit      100  )

   • 下载量TOP用户图表所关联的查询分析语句如下所示：

     SELECT CASE WHEN ip_to_country(client_ip)='上海' THEN concat(client_ip, ' ( shanghai )') WHEN ip_to_province(client_ip)='' THEN concat(client_ip, ' ( Unknown IP )') WHEN ip_to_provider(client_ip)='内网IP' THEN concat(client_ip, ' ( Private IP )') ELSE concat(client_ip, ' ( ', ip_to_country(client_ip), '/', ip_to_province(client_ip), '/',        CASE WHEN ip_to_city(client_ip)='-1' THEN 'Unknown city' ELSE ip_to_city(client_ip) END, ' ',ip_to_provider(client_ip), ' )') END AS client,        pv as "总访问数",        error_count as "错误访问数" ,      round( CASE WHEN "throughput" > 0 THEN "throughput" ELSE 0 END, 1 ) AS "下载总量(GB)"      from (      select client_ip ,        count(*) as pv,        sum(response_size)/1024.0/1024/1024.0 AS throughput ,        sum(CASE WHEN http_code > 400 THEN 1 ELSE 0 END) AS error_count        from log        group by client_ip       order by throughput desc        limit 100)

查看CDN热门资源

1. 登录云日志服务控制台，在左侧导航栏中选择“仪表盘”。
2. 在仪表盘模板下方，选择“CDN仪表盘模板 > CDN热门资源”，查看图表详情。
   • 域名访问次数Top5图表所关联的查询分析语句如下所示：

     select domain ,count(*) as cnt group by domain order by cnt desc limit 5

   • 域名下载流量Top5图表所关联的查询分析语句如下所示：

     select domain , sum(response_size) as "下载总量" group by domain order by "下载总量" desc limit 5

   • 热门访问（URI）图表所关联的查询分析语句如下所示：

     select uri as URI, "访问次数", "访问人数", round( CASE WHEN "下载总量(GB)" > 0 THEN "下载总量(GB)" ELSE 0 END, 2 ) AS "下载总量(GB)" from (        select uri ,count(*) as "访问次数" , APPROX_COUNT_DISTINCT(client_ip) as "访问人数", sum(response_size)/1024.0/1024.0/1024.0 as "下载总量(GB)" where http_code < 400 group by uri order by "访问次数" desc limit 100)

   • 热门访问（来源）图表所关联的查询分析语句如下所示：

     select refer_domain as "来源",c as "次数",uv as "人数", round(c * 100.0 / sum(c), 2) as  "百分比%"  from (select refer_domain as refer_domain,count(*) as c,APPROX_COUNT_DISTINCT(client_ip) as uv from log where refer_domain != '' group by refer_domain order by c desc limit 100 ) GROUP BY refer_domain, c, uv

   • 全国访问次数分布统计图表所关联的查询分析语句如下所示：

     select ip_to_province(client_ip) as province , count(*) as cnt where IP_TO_COUNTRY (client_ip) = '中国' group by province HAVING province not in ('','保留地址','*') order by cnt desc limit 1000

   • 全国下载网速统计图表所关联的查询分析语句如下所示：

     select province, round( CASE WHEN "speed" > 0 THEN "speed" ELSE 0 END, 3 ) AS "speed" from (select ip_to_province(client_ip) as province , sum(response_size)* 1.0 /(sum(response_time)+1) as "speed" , count(*) as c where IP_TO_COUNTRY (client_ip) = '中国' group by province HAVING province not in ('','保留地址','*') order by c desc limit 40)

   • 省份统计图表所关联的查询分析语句如下所示：

     select ip_to_province(client_ip) as "省份" ,count(*) as "访问次数", sum(response_size)/1024.0/1024.0/1024.0 as "下载流量(GB)" , sum(response_size) * 1.0 /sum(response_time) as "下载速度(KB/s)" group by "省份" having ip_to_province(client_ip) != '' order by "下载流量(GB)" desc limit 200

   • 运营商流量和速度图表所关联的查询分析语句如下所示：

     select ip_to_provider(client_ip) as isp , sum(response_size)* 1.0 /(sum(response_time)+1) as "下载速度(KB/s)" , sum(response_size)/1024.0/1024.0/1024.0 as "下载总量(GB)", count(*) as c group by isp having ip_to_provider(client_ip) != '' order by c desc limit 10

   • 运营商统计图表所关联的查询分析语句如下所示：

     select "运营商", "访问次数", round( CASE WHEN "下载流量(GB)" > 0 THEN "下载流量(GB)" ELSE 0 END, 2 ) AS "下载流量(GB)",        round( CASE WHEN "下载速度(KB/s)" > 0 THEN "下载速度(KB/s)" ELSE 0 END, 2 ) AS "下载速度(KB/s)" from (      select ip_to_provider(client_ip) as "运营商" ,count(*) as "访问次数", sum(response_size)/1024.0/1024.0/1024.0 as "下载流量(GB)" , sum(response_size) * 1.0 /sum(response_time) as "下载速度(KB/s)" group by "运营商" having ip_to_provider(client_ip) != '' and "运营商" not in ('*') order by "下载流量(GB)" desc limit 200)