# CDN仪表盘模板
CDN（Content Delivery Network，内容分发网络）记录了所有域名（包括已删除域名，如果您开通了企业项目，则已删除域名不支持此功能）被网络用户访问的详细日志，您可以将日志接入LTS，对您的业务资源被访问情况进行详细分析。
CDN仪表盘模板支持[查看CDN错误分析]、[查看CDN基础数据]、[查看CDN用户分析]和[查看CDN热门资源]。
#### 前提条件
- 已采集CDN日志，详情请参见[内容分发网络CDN接入LTS](https://support.huaweicloud.com/usermanual-lts/lts_04_0536.html)。
- 日志配置结构化，详情请参见[设置云端结构化解析日志](https://support.huaweicloud.com/usermanual-lts/lts_0821.html)。
 
 #### 查看CDN错误分析
1. 登录[云日志服务控制台](https://console.huaweicloud.com/lts/?#/cts/manager/groups)，进入"日志管理"页面。
2. 在左侧导航栏中选择"仪表盘"。
3. 在仪表盘模板下方，选择"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. 在仪表盘模板下方，选择"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. 在仪表盘模板下方，选择"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. 在仪表盘模板下方，选择"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)
     ```