CSE层访问中心

前提条件

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

背景信息

微服务引擎（Cloud Service Engine，CSE），是用于微服务应用的云中间件，支持华为云贡献到Apache社区的注册配置中心Servicecomb引擎和开源增强的注册配置中心Nacos引擎。用户可结合其他云服务，快速构建云原生微服务体系，实现微服务应用的快速开发和高可用运维。高

分析网站访问情况

1. 登录云日志服务控制台。
2. 在左侧导航栏中选择“仪表盘 ”。
3. 在仪表盘模板下方，选择“CSE仪表盘模板>CSE层访问中心”仪表盘，查看图表详情。

CSE层访问中心仪表盘中的过滤器说明如下所示：

• 过滤上游IP，所关联的查询分析语句如下所示：

  select distinct(upstream_host)

• 过滤调用链trace_id，所关联的查询分析语句如下所示：

  select distinct(trace_id)

重要图表说明

CSE层访问中心仪表盘中的重要图表说明如下所示：

• PV对比昨日图展示PV数对比昨日的变化情况，所关联的查询分析语句如下所示：

  select diff[1] as "total", round((diff[1] - diff[2]) / diff[2] * 100, 2) as inc from(select compare( "pv" , 86400) as diff from (select count(1) as "pv" from log))

• PV对比上周图展示PV数对比上周的变化情况，所关联的查询分析语句如下所示

  select diff[1] as "total", round((diff[1] - diff[2]) / diff[2] * 100, 2) as inc from(select compare( "pv" , 604800) as diff from (select count(1) as "pv" from log))

• UV对比昨日图展示UV数对比昨日的变化情况，所关联的查询分析语句如下所示：

  select diff[1] as "total", round((diff[1] - diff[2]) / diff[2] * 100, 2) as inc from(select compare( "uv" , 86400) as diff from (select APPROX_COUNT_DISTINCT(authority) as "uv" from log))

• UV对比上周图展示UV数对比上周的变化情况，所关联的查询分析语句如下所示：

  select diff[1] as "total", round((diff[1] - diff[2]) / diff[2] * 100, 2) as inc from(select compare( "uv" , 604800) as diff from (select APPROX_COUNT_DISTINCT(authority) as "uv" from log))

• 访问量PV分布(中国)图展示中国区域内访问量PV的分布情况，所关联的查询分析语句如下所示：

  select ip_to_province(authority) as province, sum(ori_pv) as pv from (select authority, count(1) as ori_pv   group by authority   ORDER BY ori_pv desc  LIMIT 10000)  where IP_TO_COUNTRY (authority) = '中国'  group by province HAVING province not in ('','保留地址','*')

• 访问量PV分布(世界)图展示世界区域内访问量PV的分布情况，所关联的查询分析语句如下所示：

  SELECT ip_to_country(authority) as country,sum(ori_pv) as PV from (select authority, count(1) as ori_pv  group by authority   ORDER BY ori_pv desc  LIMIT 10000) GROUP BY country HAVING country not in ('','保留地址','*')

• 平均时延分布（中国）图展示中国区域内平均时延的分布情况，所关联的查询分析语句如下所示：

  SELECT province,round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 3 ) AS "平均延迟(ms)" FROM (SELECT ip_to_province(authority) as province,sum(rt)/sum(ori_pv) * 1000 AS "平均延迟(ms)" from (select authority, sum(duration) as rt,count(1) as ori_pv  group by authority   ORDER BY ori_pv desc   LIMIT 10000) WHERE  IP_TO_COUNTRY (authority) = '中国' GROUP BY province )  where province not in ('','保留地址','*')

• 平均时延分布（世界）图展示世界区域内平均时延的分布情况，所关联的查询分析语句如下所示：

  SELECT country,round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 2 ) AS "平均延迟(ms)" FROM (SELECT ip_to_country(authority) as country,sum(rt)/sum(ori_pv)  * 1000 AS "平均延迟(ms)" from (select authority, sum(duration) as rt,count(1) as ori_pv   group by authority   ORDER BY ori_pv desc LIMIT 10000) GROUP BY country ) where  country not in ('','保留地址','*')

• 今日PV/UV图展示最近一天的PV/UV数，所关联的查询分析语句如下所示：

  SELECT TIME_FORMAT( _time_, 'yyyy-MM-dd HH:mm:ss') as _time_,PV,UV FROM (select TIME_CEIL(TIME_PARSE(start_time),'PT600S') AS _time_ , count(1) as PV,  APPROX_COUNT_DISTINCT(authority) as UV from log WHERE __time <= CURRENT_TIMESTAMP  and __time >= DATE_TRUNC( 'DAY',(CURRENT_TIMESTAMP + INTERVAL '8' HOUR)) - INTERVAL '8' HOUR group by _time_ order by _time_)

• 区域访问TOP10(省份)图展示访问排名前十的省份，所关联的查询分析语句如下所示：

  select ip_to_province(authority) as "province", sum(ori_pv) as "访问次数" from (select authority, count(1) as ori_pv  group by authority  ORDER BY ori_pv desc  LIMIT 10000) group by "province" HAVING "province" <> '-1' order by "访问次数" desc limit 10

• 区域访问TOP10(城市)图展示访问排名前十的城市，所关联的查询分析语句如下所示：

  select ip_to_city(authority) as "city", sum(ori_pv) as "访问次数" from (select authority, count(1) as ori_pv  group by authority ORDER BY ori_pv desc LIMIT 10000) group by "city" HAVING  "city" <> '-1' order by "访问次数" desc  limit 10

• Host访问TOP10图展示访问排名前十的主机，所关联的查询分析语句如下所示：

  select  upstream_host as "Host", count(1) as "PV" group by "Host" order by "PV" desc limit 10

• UserAgent访问TOP10图展示访问排名前十的UserAgent，所关联的查询分析语句如下所示：

  select user_agent as "UserAgent", count(1) as "PV" group by "UserAgent" order by "PV" desc limit 10

• 设备占比(终端)图展示各终端设备的访问占比，所关联的查询分析语句如下所示：

  select case when regexp_like(lower(user_agent), 'iphone|ipod|android|ios') then '移动端' else 'PC端' end as type , count(1) as total group by  type

• 设备占比(系统)图展示各系统设备的访问占比，所关联的查询分析语句如下所示：

  select case when regexp_like(lower(user_agent), 'iphone|ipod|ios') then 'IOS' when regexp_like(lower(user_agent), 'android') then 'Android' else 'other' end as type , count(1) as total group by  type HAVING type != 'other'

• TOP URL图展示访问前十url的PV、UV及访问成功率等信息，所关联的查询分析语句如下所示：

  select path , count(1) as pv, APPROX_COUNT_DISTINCT(authority) as UV, round(sum( case when response_code < 400 then 1 else 0 end   )  * 100.0 / count(1), 2) as "访问成功率" group by path ORDER by pv desc

• TOP 访问IP图展示访问前十的IP及城市、运营商和PV等数据，所关联的查询分析语句如下所示：

  select authority as "来源IP",ip_to_country(authority) as "国家",ip_to_province(authority) as "省份",ip_to_city(authority) as "城市",ip_to_provider(authority) as "运营商",count(1) as "PV" group by authority ORDER by "PV" desc limit 100