[svc]influxdb+grafana实战-各省份api访问成功率统计

简单说下需求:

统计各个省份的 3大运营商的接口访问成功率,绘图展示

数据格式

{"mobile" : "15812345608", "province": "广东", "isp": "中国电信","@timestamp" : "2017-12-06T09:23:51.244Z", "success" : "false"} 

最终入库(influxdb)的日志

示例数据:(influxdb的sql)

INSERT crawl_result,isp=中国移动,province=上海,mobile=15912345678 success="1"
INSERT crawl_result,isp=中国移动,province=上海,mobile=15912345678 success="1"
INSERT crawl_result,isp=中国移动,province=上海,mobile=15912345678 success="1"
INSERT crawl_result,isp=中国移动,province=上海,mobile=15912345678 success="1"
INSERT crawl_result,isp=中国移动,province=上海,mobile=15912345678 success="1"
INSERT crawl_result,isp=中国移动,province=上海,mobile=15912345678 success="1"
INSERT crawl_result,isp=中国移动,province=上海,mobile=15912345678 fail="0"
INSERT crawl_result,isp=中国移动,province=上海,mobile=15912345678 fail="0"
INSERT crawl_result,isp=中国联通,province=上海,mobile=15912345678 fail="0"

说下方案

第二种方案: influxdb+grafana, 好处是可灵活计算比例.表格少一些.

这个毛病在于sql语句. grafana模板有点难,如果没玩过的话. grafana需要多点点就会了,

grafana对接influxdb,无需多写什么sql.

vm 下载安装grafana

参考: 普罗+grafana监控mysql:

wget https://s3-us-west-2.amazonaws.com/grafana-releases/release/grafana-4.6.2-1.x86_64.rpm
yum localinstall grafana-4.6.2-1.x86_64.rpm -y

配置grafana导入dashboard

修改配置

cd /etc/grafana/grafana.ini
cp grafana.ini grafana.ini.default
vim grafana.ini
...
370 [dashboards.json]
371 ;enabled = false
372 enabled = true
373 path = /var/lib/grafana/dashboards
...

导入dashboard

cd /var/lib/grafana/dashboards

插件目录: /var/lib/grafana/plugins
dashboard目录: /var/lib/grafana/dashboards
日志目录: /var/log/grafana/grafana.log

启动并访问:

systemctl restart grafana-server
http://monitor_host:3000访问Grafana网页界面（缺省的帐号/密码为admin/admin）

influxdb安装

wget https://dl.influxdata.com/influxdb/releases/influxdb-1.4.2.x86_64.rpm
sudo yum localinstall influxdb-1.4.2.x86_64.rpm

当然也可以容器启动 grafana和influxdb

参考:

docker run   -d   -p 3000:3000   --name=grafana   -e "GF_SERVER_ROOT_URL=http://grafana.server.name"   -e "GF_SECURITY_ADMIN_PASSWORD=secret"   grafana/g

最终实现:

连库:

定义模板变量

配置左半部分的

配置有半部分的

SELECT count("success")  FROM "crawl_result" WHERE ("isp" = '中国移动' AND "province" =~ /^$china_mobile$/) AND $timeFilter GROUP BY "isp","province" fill(null) ORDER BY time DESC

SELECT count("success"),count("fail"),count("success")/(count("success")+count("fail")) FROM "crawl_result" WHERE ("isp" = '中国移动' AND province=~ /^$china_mobile$/) AND $timeFilter GROUP BY  "isp","province" fill(null)

最终模板效果:

sql的groupby

按照isp分类

SELECT isp,province,mobile,success,fail FROM "crawl_result" GROUP BY  "isp""

先按照isp分类,后按照province分类

SELECT isp,province,mobile,success,fail FROM "crawl_result" GROUP BY  "isp","province"

参考别人的模板是怎么做的:
Graphite Templated Dashboard:

Elasticsearch Templated Dashboard:

InfluxDB Templated Dashboard:

InfluxDB Templated Dashboard

grafana不需要写特别的语法,按照它给的sql语句器实现group by分类和order by,完全ok.