CDN用户分析

前提条件

• 已采集CDN日志，详情请参见CDN接入。
• 日志配置结构化，详情请参见结构化配置。

背景信息

CDN（Content Delivery Network，内容分发网络）记录了所有域名（包括已删除域名，如果您开通了企业项目，则已删除域名不支持此功能）被网络用户访问的详细日志，您可以将日志接入LTS，对您的业务资源被访问情况进行详细分析。

分析网站访问情况

1. 登录云日志服务控制台，在左侧导航栏中选择“仪表盘”。
2. 在仪表盘模板下方，选择“CDN仪表盘模板>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图展示有效访问用户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用户图展示下载量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)