ELB7层访问中心

前提条件

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

背景信息

弹性负载均衡（Elastic Load Balance，简称ELB）是将访问流量根据分配策略分发到后端多台服务器的流量分发控制服务。弹性负载均衡可以通过流量分发扩展应用系统对外的服务能力，同时通过消除单点故障提升应用系统的可用性，支持查看和分析对七层负载均衡HTTP和HTTPS进行请求的详细访问日志记录，包括请求时间、客户端IP地址、请求路径和服务器响应等。

分析网站访问情况

1. 登录云日志服务控制台，在左侧导航栏中选择“日志管理”。
2. 在“日志应用”模块中，单击“ELB日志中心”，选择“进入仪表盘”。
3. 在仪表盘模板下方，选择“ELB仪表盘模板>ELB7层访问中心”仪表盘，查看图表详情。

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

• 获取所有负载均衡器，所关联的查询分析语句如下所示：

  select distinct(lb_name)

• 获取所有客户端IP，所关联的查询分析语句如下所示：

  select distinct(remote_addr)

• 获取所有后端服务器IP，所关联的查询分析语句如下所示：

  select distinct(upstream_addr)

• 获取所有弹性IP地址，所关联的查询分析语句如下所示：

  select distinct(eip_address) 

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

• 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(remote_addr) 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(remote_addr) as "uv" from log))

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

  select ip_to_province(remote_addr) as province, count(1) as pv where IP_TO_COUNTRY (remote_addr) = '中国'  group by province HAVING province not in ('','保留地址','*')

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

  SELECT ip_to_country(remote_addr) as country,COUNT(1) as PV GROUP BY country HAVING country not in ('','保留地址','*')

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

  select ip_to_province(remote_addr) as province, APPROX_COUNT_DISTINCT(remote_addr) as uv where IP_TO_COUNTRY (remote_addr) = '中国' group by province HAVING province not in ('','保留地址','*')

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

  select ip_to_country(remote_addr) as country, APPROX_COUNT_DISTINCT(remote_addr) as uv 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(remote_addr) as province,avg(request_time) * 1000 AS "平均延迟(ms)"WHERE  IP_TO_COUNTRY (remote_addr) = '中国'GROUP BY province HAVING province not in ('','保留地址','*'))

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

  SELECT country,round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 2 ) AS "平均延迟(ms)"FROM (SELECT ip_to_country(remote_addr) as country,avg(request_time) * 1000 AS "平均延迟(ms)" GROUP BY country HAVING country not in ('','保留地址','*'))

• 今日PV/UV图展示今日PV/UV的变化情况，所关联的查询分析语句如下所示：

  SELECT TIME_FORMAT( _time_, 'yyyy-MM-dd HH:mm:ss', '+08:00' ) as _time_,PV,UV FROM (select TIME_CEIL(TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ'),'PT600S') AS _time_ , count(1) as PV,  APPROX_COUNT_DISTINCT(remote_addr) as UV from log WHERE TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ') <= CURRENT_TIMESTAMP  and TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ') >= DATE_TRUNC( 'DAY',(CURRENT_TIMESTAMP + INTERVAL '8' HOUR)) - INTERVAL '8' HOUR group by _time_ order by _time_)

• 7日PV/UV图展示7日PV/UV的变化情况，所关联的查询分析语句如下所示：

  SELECT TIME_FORMAT( _time_, 'yyyy-MM-dd HH:mm:ss', '+08:00' ) as _time_,PV,UV FROM (select  TIME_CEIL(TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ'),'PT600S') AS _time_ , count(1) as PV,  APPROX_COUNT_DISTINCT(remote_addr) as UV from log WHERE TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ') <= CURRENT_TIMESTAMP  and TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ') >= DATE_TRUNC( 'DAY',(CURRENT_TIMESTAMP + INTERVAL '8' HOUR)) - INTERVAL '8' HOUR - INTERVAL '7' DAY group by _time_ order by _time_ )

• 区域访问TOP10(省份)图展示区域访问TOP10(省份)的变化情况，所关联的查询分析语句如下所示：

  select ip_to_province(remote_addr) as "province", count(1) as "访问次数" group by "province" HAVING "province" <> '-1' order by "访问次数" desc limit 10

• 区域访问TOP10(城市)图展示区域访问TOP10(城市)的变化情况，所关联的查询分析语句如下所示：

  select ip_to_city(remote_addr) as "city", count(1) as "访问次数" group by "city" HAVING  "city" <> '-1' order by "访问次数" desc  limit 10

• Host访问TOP10图展示Host访问TOP10的变化情况，所关联的查询分析语句如下所示：

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

• UserAgent访问TOP10图展示UserAgent访问TOP10的变化情况，所关联的查询分析语句如下所示：

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

• 设备占比(终端)图展示设备占比(终端)的变化情况，所关联的查询分析语句如下所示：

  select case when regexp_like(lower(http_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(http_user_agent), 'iphone|ipod|ios') then 'IOS' when regexp_like(lower(http_user_agent), 'android') then 'Android' else 'other' end as type , count(1) as total group by  type HAVING type != 'other'

• TOP URL图展示TOP URL的变化情况，所关联的查询分析语句如下所示：

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

• TOP 访问IP图展示TOP 访问IP的变化情况，所关联的查询分析语句如下所示：

  select remote_addr as "来源IP",ip_to_country(remote_addr) as "国家",ip_to_province(remote_addr) as "省份",ip_to_city(remote_addr) as "城市",ip_to_provider(remote_addr) as "运营商",count(1) as "PV",http_user_agent as "UserAgent采样",router_request_uri as "URL采样" group by remote_addr,http_user_agent,router_request_uri ORDER by "PV" desc