nginx日志采集 mysql_shell + go + mysql nginx日志统计 （三） ：数据的展示

效果图

woshitu.png

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的连接信息 这样就添加好了

grafana-datasource.jpg

线图展示

点击 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;