CDN Dashboard Templates

Prerequisites

• Logs have been structured. For details, see Setting Cloud Structuring Parsing.

Viewing CDN Error Analysis

1. Log in to the LTS console.
2. In the navigation pane, choose Dashboards.
3. Choose CDN Dashboard Templates under Dashboard Templates and click CDN Error Analysis to view the detailed charts.
   Figure 1 CDN Error Analysis
   
   • Top 5 Domain Names by Access Errors. The associated query and analysis statement is:

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

   • Top 5 URIs by Access Errors. The associated query and analysis statement is:

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

   • Request Errors. The associated query and analysis statement is:

     * | 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

   • Status Codes. The associated query and analysis statement is:

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

   • Errors by Carrier. The associated query and analysis statement is:

     * | 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

   • Errors by Client. The associated query and analysis statement is:

     * | SELECT user_agent as "Client version", count(*) as "Errors" where http_code > 400 group by user_agent order by "Errors" desc limit 10

   • Errors by Province. The associated query and analysis statement is:

     * | 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 Errors. The associated query and analysis statement is:

     * | SELECT    province AS "Province",      isp AS "Carrier",     c AS "Errors",      round( c * 100.0 / sum( c ), 2 ) AS "Rate (%)"      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 Errors. The associated query and analysis statement is:

     * | SELECT    province AS "Province",      isp AS "Carrier",      c AS "Errors",      round( c * 100.0 / sum( c ), 2 ) AS "Rate (%)"     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

Viewing CDN Basic Data

1. Choose CDN Dashboard Templates under Dashboard Templates and click CDN Basic Data to view the detailed charts.
   Figure 2 CDN Basic Data
   
   • Cache Hit Ratio. The associated query and analysis statement is:

     * | 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)))

   • Download Speed. The associated query and analysis statement is:

     * | 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 ))

   • Status Codes. The associated query and analysis statement is:

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

   • Latency Distribution. The associated query and analysis statement is:

     * | 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

   • Request Bandwidth. The associated query and analysis statement is:

     * | SELECT TIME_FORMAT (TIME_FLOOR(__time,'PT1M'), 'HH:mm', '+08:00') as thisdate, 
           sum(cast(response_size as bigint)) * 8/1000000000.0 as "Bandwidth (Gbit/min)" 
           group by TIME_FLOOR(__time,'PT1M') 
           order by TIME_FLOOR(__time,'PT1M')

   • Visits and Users. The associated query and analysis statement is:

     * | 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')

   • Average Latency. The associated query and analysis statement is:

     * | SELECT TIME_FORMAT (TIME_FLOOR(__time,'PT1M'), 'HH:mm', '+08:00') as thisdate, 
           avg(cast(response_time as bigint)) as "Average latency (ms)" group by TIME_FLOOR(__time,'PT1M') order by TIME_FLOOR(__time,'PT1M')

   • Request Hit Ratio. The associated query and analysis statement is:

     * | 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

Viewing CDN User Analysis

1. Choose CDN Dashboard Templates under Dashboard Templates and click CDN User Analysis to view the detailed charts.
   Figure 3 CDN User Analysis
   
   • Visits. The associated query and analysis statement is:

     * | 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) ), ' thousand' )        WHEN diff [ 1 ] < 1000000000 THEN       concat( cast( diff [ 1 ]/ 1000000.0 AS decimal(10,2) ), ' million' )        WHEN diff [ 1 ]/ 1000.0 < 1000000000 THEN       concat( cast(  diff [ 1 ]/ 1000.0 / 1000000 AS decimal(10,2) ), ' billion' ) ELSE concat( cast(  diff [ 1 ]/ 1000.0 / 1000 / 1000 / 1000 AS decimal(10,2) ), ' trillion' )        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 ))

   • Visitors. The associated query and analysis statement is:

     * | 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))

   • Visits by Client. The associated query and analysis statement is:

     * | SELECT ua as "Client" , sum(c) as "Visits" 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 "Client" order by "Visits" desc limit 100

   • Visits by Carrier. The associated query and analysis statement is:

     * | SELECT ip_to_provider(client_ip) as isp ,count(*) as "Visits" group by isp order by "Visits" desc limit 100

   • Visits by Geography. The associated query and analysis statement is:

     * | SELECT ip_to_province(client_ip) as province , count(*) as cnt group by province order by cnt desc limit 100

   • Top Valid Visitors. The associated query and analysis statement is:

     * |  SELECT 
           CASE WHEN ip_to_country(client_ip)='Hong Kong' THEN concat(client_ip, ' ( Hong Kong )') WHEN ip_to_province(client_ip)='' THEN concat(client_ip, ' ( Unknown IP )') WHEN ip_to_provider(client_ip)='Private 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 "Visitor",
           pv as "Total Visits", 
           (pv - success_count) as "Access Errors" , 
           throughput as "Total Downloads (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 Visitors by Downloads. The associated query and analysis statement is:

     * | SELECT 
           CASE WHEN ip_to_country(client_ip)='Hong Kong' THEN concat(client_ip, ' ( Hong Kong )') WHEN ip_to_province(client_ip)='' THEN concat(client_ip, ' ( Unknown IP )') WHEN ip_to_provider(client_ip)='Private 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 "Visitor", 
           pv as "Total Visits", 
           error_count as "Access Errors" ,
           throughput as "Total Downloads (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)

Viewing Popular CDN Resources

1. Choose CDN Dashboard Templates under Dashboard Templates and click Popular CDN Resources to view the detailed charts.
   Figure 4 Popular CDN Resources
   
   • 5 Most Visited Domain Names. The associated query and analysis statement is:

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

   • 5 Most Downloaded Domain Names. The associated query and analysis statement is:

     * | SELECT domain , sum(cast(response_size as bigint)) as "Total downloads" group by domain order by "Total downloads" desc limit 5

   • Popular Visits (URI). The associated query and analysis statement is:

     * | SELECT URI ,count(*) as "Visits" , APPROX_COUNT_DISTINCT(client_ip) as "Visitors", round(sum(cast(response_size as bigint))/1024.0/1024.0/1024.0, 2) as "Total Downloads (GB)" where http_code < 400 group by uri order by "Visits" desc limit 100

   • Popular Visits (Source). The associated query and analysis statement is:

     * | SELECT ip_to_province(client_ip) as province , count(*) as cnt group by province order by cnt desc limit 1000

   • Visits by Geography. The associated query and analysis statement is:

     * | SELECT ip_to_province(client_ip) as province , count(*) as cnt group by province order by cnt desc limit 1000

   • Download Speed by Geography. The associated query and analysis statement is:

     * | 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

   • Statistics by Province. The associated query and analysis statement is:

     * | SELECT ip_to_province(client_ip) as "Province" ,count(*) as "Visits", sum(cast(response_size as bigint))/1024.0/1024.0/1024.0 as "Total Downloads (GB)" , sum(cast(response_size as bigint)) * 1.0 /sum(cast(response_time as bigint)) as "Download Speed (KB/s)" group by "Province" having ip_to_province(client_ip) != '' order by "Total Downloads (GB)" desc limit 200

   • Carrier Traffic and Speed. The associated query and analysis statement is:

     * | SELECT ip_to_provider(client_ip) as isp , sum(cast(response_size as bigint))* 1.0 /(sum(cast(response_time as bigint))+1) as "Download speed (KB/s)" , sum(cast(response_size as bigint))/1024.0/1024.0/1024.0 as "Total downloads (GB)", count(*) as c group by isp having ip_to_provider(client_ip) != '' order by c desc limit 10

   • Statistics by Carrier. The associated query and analysis statement is:

     * | SELECT ip_to_provider(client_ip) as "Carrier" ,count(*) as "Visits", round(sum(cast(response_size as bigint))/1024.0/1024.0/1024.0,2) as "Total Downloads (GB)" , round(sum(cast(response_size as bigint)) * 1.0 /sum(cast(response_time as bigint)) ,2 ) as "Download Speed (KB/s)" group by "Carrier" having ip_to_provider(client_ip) != '' order by "Total Downloads (GB)" desc limit 200