shell + go + mysql + granafa nginx日志统计（三）：数据展示

效果图

grafana 的安装

这里主要讲在Centos下的安装，其实这个网上到处是，其他系统我就不多说了。

wget https://s3-us-west-2.amazonaws.com/grafana-releases/release/grafana-4.2.0-1.x86_64.rpm
yum localinstall grafana-4.2.0-1.x86_64.rpm
#启动 granafa
systemctl start grafana-server


然后你查看，就能看到在运行了
netstat -tpnl | grep 3000 

访问 http://ip:3000 帐号密码都是 admin

接入mysql数据源

点击 add data source 输入Mysql的连接信息 这样就添加好了

线图展示

• 点击 new dashboard
• 然后添加一个新的Dashboard Row
• 再在这个Row 里添加一个新的Panel 选择 Graph

总访问量(按项目)

从ngx_access表中根据不同project（也就是不同的nginx日志文件名）取出这个项目的总访问量

SELECT  UNIX_TIMESTAMP(date) as time_sec,sum(times) as value,project as metric
FROM ngx_access
WHERE $__timeFilter(date)
group by project,date
ORDER BY date ASC

总访问量（按状态码）

从ngx_access表中根据不同code（也就是状态码）取出不同返回码的次数

SELECT
  UNIX_TIMESTAMP(date) as time_sec,
  sum(times) as value,
  code as metric
FROM edu_access
WHERE $__timeFilter(date)
group by code,date
ORDER BY date ASC

响应时间排行

表格展示

• 点击 new dashboard
• 然后添加一个新的Dashboard Row
• 再在这个Row 里添加一个新的Panel 选择 Table

ip访问次数统计

做这个出来主要是看看时段内有没有大量的恶意访问

SELECT
ip as IP,
 sum(times) as 次数
FROM ngx_ip
WHERE $__timeFilter(date)
GROUP BY ip ORDER BY 次数 DESC limit 100;|

PV（每日页面访问量）

SELECT
  date_format(date, '%Y-%m-%d') as 日期,
 sum(times) as value
FROM ngx_access
WHERE $__timeFilter(date) 
group by date_format(date, '%Y-%m-%d')
ORDER BY date_format(date, '%Y-%m-%d') asc

IP（每日独立IP访问量）

SELECT
  date_format(date, '%Y-%m-%d') as 日期,
 count(distinct ip) as value
FROM ngx_ip
WHERE $__timeFilter(date)
group by 日期
ORDER BY 日期 asc

URL（URL的访问排行榜）

用于查看哪些功能（URL）被大量使用,这个感觉还比较有用，可以帮忙知道用户到底在干些什么。某些访问多的是不是该加机器或者独立之类的。

select 
project as project,
url as url,
sum(times) as times
from ngx_access
WHERE $__timeFilter(date)
group by project,url
order by  times desc limit 100

URL（响应时间排行）

根据每个project来展示每个URL 访问时间区间

SELECT
url as Url,
 sum(xiaoyu10) as 小于10ms,
 sum(xiaoyu50) as 10ms_50ms,
 sum(xiaoyu100) as 50ms_100ms,
 sum(xiaoyu500) as 100ms_500ms,
 sum(dayu500) as 大于500ms,
 sum(xiaoyu10+xiaoyu100+xiaoyu50+xiaoyu500+dayu500) as tatol
FROM edu_res
WHERE $__timeFilter(date) and project = 'project'
GROUP BY url ORDER BY tatol DESC limit 50;

转载于:https://my.oschina.net/u/1763608/blog/1607942