CDN用户分析
CDN用户分析仪表盘主要展示访问次数、访问人数、访问客户端统计、运营商次数统计、访问地区分布、有效访问用户TOP、下载量TOP用户。
背景信息
CDN(Content Delivery Network,内容分发网络)记录了所有域名(包括已删除域名,如果您开通了企业项目,则已删除域名不支持此功能)被网络用户访问的详细日志,您可以将日志接入LTS,对您的业务资源被访问情况进行详细分析。
分析网站访问情况
- 登录云日志服务控制台,在左侧导航栏中选择“仪表盘”。
- 在仪表盘模板下方,选择“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)