# 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_07_0079.html)。
 
 #### 查看CDN错误分析
1. 登录[云日志服务控制台](https://console.huaweicloud.com/lts/?#/cts/manager/groups)，进入"日志管理"页面。
2. 在左侧导航栏中选择"仪表盘"。
3. 在仪表盘模板下方，选择"CDN仪表盘模板 \> CDN错误分析"，查看图表详情。 
   图1CDN错误分析   
   ![](https://support.huaweicloud.com/usermanual-lts/zh-cn_image_0000002498362564.png "点击放大")
   - **错误域名访问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 time_floor(__time,'PT5M') as axisData,sum(case when http_code >= 400 and http_code < 500 then 1 else 0 end) * 1.0 / count(*) as "4xx",sum(case when http_code >= 500 then 1 else 0 end) * 1.0 / count(*) as "5xx"  group by  axisData order by axisData
     ```
     
   
   - **错误请求状态分布** 图表所关联的查询分析语句如下所示：
     ```
     * | 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 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
     ```
     
   
   
   
   
 
 #### 查看CDN基础数据
1. 在仪表盘模板下方，选择"CDN仪表盘模板 \> CDN基础数据"，查看图表详情。 
   图2CDN基础数据   
   ![](https://support.huaweicloud.com/usermanual-lts/zh-cn_image_0000002530122785.png "点击放大")
   - **缓存命中率** 图表所关联的查询分析语句如下所示：
     ```
     * | 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      CASE       WHEN       diff [ 1 ] < 1024 THEN       concat( cast( diff [ 1 ] AS decimal(10,2) ) , ' KB/sec' )        WHEN diff [ 1 ] < 1024 * 1024 and diff [ 1 ] >= 1024 THEN       concat( cast( diff [ 1 ]/ 1024 AS decimal(10,2) ), ' MB/sec' )        WHEN diff [ 1 ] < 1024 * 1024 * 1024 and diff [ 1 ] >= 1024 * 1024 THEN       concat( cast(  diff [ 1 ]/ 1024 * 1024.0 AS decimal(10,2) ), ' TB/sec' )       ELSE concat( cast( diff [ 1 ]/ 1024.0 / 1024 / 1024  AS decimal(10,2) ), ' PB/sec' )        END AS "speed",   round( diff [ 2 ], 2 ) AS diff,  round(( diff [ 3 ]- 1 )* 100, 2 ) as parallel_period FROM  (  SELECT   compare ( speed, 86400 ) AS diff  FROM  ( SELECT sum( cast( response_size AS BIGINT )) * 1.0 / sum( cast( response_time AS BIGINT )) AS speed FROM log ))
     ```
     
   
   - **访问状态** 图表所关联的查询分析语句如下所示：
     ```
     * | SELECT http_code , count(*) as c group by http_code order by c desc
     ```
     
   
   - **访问延时分布** 图表所关联的查询分析语句如下所示：
     ```
     * | SELECT        case when cast(response_time as INTEGER)< 100 then '~100ms'        when cast(response_time as INTEGER)< 500  and cast(response_time as INTEGER)>= 100 then '100~500ms'        when cast(response_time as INTEGER)< 1000 and cast(response_time as INTEGER)>= 500 then '500ms~1s'        when cast(response_time as INTEGER)< 5000 and cast(response_time as INTEGER)>= 1000 then '1~5s'        when cast(response_time as INTEGER)< 6000 and cast(response_time as INTEGER)>= 5000 then '5~6s'        when cast(response_time as INTEGER)< 7000 and cast(response_time as INTEGER)>= 6000 then '6~7s'        when cast(response_time as INTEGER)< 8000 and cast(response_time as INTEGER)>= 7000 then '7~8s'       when cast(response_time as INTEGER)< 10000 and cast(response_time as INTEGER)>= 8000 then '8~10s'       when cast(response_time as INTEGER)< 15000 and cast(response_time as INTEGER)>= 10000 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(cast(response_size as bigint)) * 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(cast(response_time as bigint)) 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用户分析"，查看图表详情。 
   图3CDN用户分析   
   ![](https://support.huaweicloud.com/usermanual-lts/zh-cn_image_0000002498363042.png "点击放大")
   - **访问次数** 图表所关联的查询分析语句如下所示：
     ```
     * | SELECT CASE       WHEN       diff [ 1 ] < 1000 THEN       concat( cast( diff [ 1 ] AS decimal(10,2) ), ' 次' )        WHEN diff [ 1 ]  < 1000 * 1000 THEN       concat( cast( diff [ 1 ]/ 1000 AS decimal(10,2) ), ' 千次' )        WHEN diff [ 1 ] < 1000000000 THEN       concat( cast( diff [ 1 ]/ 1000000.0 AS decimal(10,2) ), ' 百万次' )        WHEN diff [ 1 ]/ 1000.0 < 1000000000 THEN       concat( cast(  diff [ 1 ]/ 1000.0 / 1000000 AS decimal(10,2) ), ' 十亿次' ) ELSE concat( cast(  diff [ 1 ]/ 1000.0 / 1000 / 1000 / 1000 AS decimal(10,2) ), ' 万亿次' )        END AS        "pv",  diff [ 2 ] AS diff,  round( 100 *( diff [ 3 ]- 1 ),2 ) AS parallel_period  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 group by province order by cnt desc limit 100
     ```
     
   
   - **有效访问用户TOP** 图表所关联的查询分析语句如下所示：
     ```
     * |  SELECT 
           CASE WHEN ip_to_country(client_ip)='香港' THEN concat(client_ip, ' ( Hong Kong )') 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 "用户",
           pv as "总访问数", 
           (pv - success_count) as "错误访问数" , 
           throughput as "下载总量(GB)" 
           from (
           select 
           client_ip ,
           count(*) as pv, 
           round(sum(cast(response_size as bigint))/1024.0/1024/1024.0, 1) 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, ' ( Hong Kong )') 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 "用户", 
           pv as "总访问数", 
           error_count as "错误访问数" ,
           throughput as "下载总量(GB)" from (
           select client_ip , 
           count(*) as pv, 
           round(sum(cast(response_size as bigint))/1024.0/1024/1024.0, 1) 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热门资源"，查看图表详情。 
   图4CDN热门资源   
   ![](https://support.huaweicloud.com/usermanual-lts/zh-cn_image_0000002530283117.png "点击放大")
   - **域名访问次数Top5** 图表所关联的查询分析语句如下所示：
     ```
     * | SELECT domain ,count(*) as cnt group by domain order by cnt desc limit 5
     ```
     
   
   - **域名下载流量Top5** 图表所关联的查询分析语句如下所示：
     ```
     * | SELECT domain , sum(cast(response_size as bigint)) as "下载总量" group by domain order by "下载总量" desc limit 5
     ```
     
   
   - **热门访问（URI）** 图表所关联的查询分析语句如下所示：
     ```
     * | SELECT URI ,count(*) as "访问次数" , APPROX_COUNT_DISTINCT(client_ip) as "访问人数", round(sum(cast(response_size as bigint))/1024.0/1024.0/1024.0, 2) as "下载总量(GB)" where http_code < 400 group by uri order by "访问次数" desc limit 100
     ```
     
   
   - **热门访问（来源）** 图表所关联的查询分析语句如下所示：
     ```
     * | SELECT ip_to_province(client_ip) as province , count(*) as cnt group by province order by cnt desc limit 1000
     ```
     
   
   - **全国访问次数分布统计** 图表所关联的查询分析语句如下所示：
     ```
     * | SELECT ip_to_province(client_ip) as province , count(*) as cnt group by province order by cnt desc limit 1000
     ```
     
   
   - **全国下载网速统计** 图表所关联的查询分析语句如下所示：
     ```
     * | SELECT ip_to_province(client_ip) as province , sum(cast(response_size as bigint))* 1.0 /(sum(cast(response_time as bigint))+1) as speed , count(*) as c group by province order by c desc limit 40
     ```
     
   
   - **省份统计** 图表所关联的查询分析语句如下所示：
     ```
     * | SELECT ip_to_province(client_ip) as "省份" ,count(*) as "访问次数", sum(cast(response_size as bigint))/1024.0/1024.0/1024.0 as "下载流量(GB)" , sum(cast(response_size as bigint)) * 1.0 /sum(cast(response_time as bigint)) 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(cast(response_size as bigint))* 1.0 /(sum(cast(response_time as bigint))+1) as "下载速度(KB/s)" , sum(cast(response_size as bigint))/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 ip_to_provider(client_ip) as "运营商" ,count(*) as "访问次数", round(sum(cast(response_size as bigint))/1024.0/1024.0/1024.0,2) as "下载流量(GB)" , round(sum(cast(response_size as bigint)) * 1.0 /sum(cast(response_time as bigint)) ,2 ) as "下载速度(KB/s)" group by "运营商" having ip_to_provider(client_ip) != '' order by "下载流量(GB)" desc limit 200
     ```