[Svc]influxdb+grafana actual combat-statistics on the success rate of API access in each province

Simple requirements:

Statistics of the 3 major operators in each province interface access success rate, drawing display

Data format

Logs for final storage (INFLUXDB)

Sample data: (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"

Next plan

The first scenario has been implemented before the elk. The effect is not understood, there are mentions

The second scenario: Influxdb+grafana, the advantage is the flexibility to calculate proportions. There are fewer tables.

The problem is the SQL statement. Grafana template is a bit difficult, if not played. Grafana need a little more.

Grafana docking Influxdb, no need to write any more SQL.

VM Download Installation Grafana

Reference: General +grafana monitoring mysql:1190000007040144

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

Configure Grafana Import Dashboard

Modify Configuration

cd /etc/grafana/grafana.inicp grafana.ini grafana.ini.defaultvim grafana.ini...370 [dashboards.json]371 ;enabled = false372 enabled = true373 path = /var/lib/grafana/dashboards...

Import Dashboard

cd /var/lib/grafana/dashboards

Plugin directory:/var/lib/grafana/plugins
Dashboard Directory:/var/lib/grafana/dashboards
Log directory:/var/log/grafana/grafana.log

Start and access:

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

INFLUXDB Installation

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

Of course, containers can also start Grafana and Influxdb

Reference: https://hub.docker.com/r/grafana/grafana/

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

Final implementation:

Lianku:

Defining Template Variables

Configure the left half of the

A half-part configuration

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)

Final template effect:

GroupBy of SQL

Classification by ISP

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

Sort by ISP first, followed by province

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

Refer to how someone else's template is done:
Graphite Templated dashboard:http://play.grafana.org/dashboard/db/graphite-templated-nested

Elasticsearch Templated dashboard:http://play.grafana.org/dashboard/db/elasticsearch-templated

InfluxDB Templated Dashboard:http://play.grafana.org/dashboard/db/influxdb-templated-queries

InfluxDB Templated Dashboard

Grafana does not need to write special syntax, according to its SQL statement to implement the group by classification and order by, fully OK.

[Svc]influxdb+grafana actual combat-statistics on the success rate of API access in each province