How to get monitoring data from the Zabbix database

The students who have done Zabbix know that Zabbix collects relevant monitoring data through a dedicated agent or SNMP, and then stores it in the database and displays it in real time. Zabbix monitoring data is mainly divided into the following two categories:

Historical data: Historyrelated tables, from the History_uint table can be queried to the maximum, minimum and average device monitoring items, that is, the original data storage monitoring data.

trend Data:trends related tables, trend data is Zabbix calculated data, the data is summarized from the History_uint, from Trends_uint can be viewed to monitor data hourly maximum, minimum and average traffic.

Zabbix can get historical data in two ways:

1. Get historical data from the Zabbix front desk

Viewing historical data through the Zabbix front desk is simple and can be viewed through monitoring->lastest data. You can also click the As Plain Test button in the upper right corner to save as a text file.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4A/48/wKioL1QkDOCjpLoTAAOm77_P1z4367.jpg "title=" 1.jpg " alt= "Wkiol1qkdocjplotaaom77_p1z4367.jpg"/>

2. Data obtained from the foreground is processed and two queries are limited, so you can query the data directly from the backend DB via SQL statements.    

First, you should familiarize yourself with the SQL statement select Common usage:

SELECT [All | DISTINCT] select_list [into [New_table_name]from {table_name | View_name} [[, {table2_name | view2_name} [,...]] [WHERE Serch_conditions] [GROUP by Group_by_list] [Having serch_conditions] [ORDER by Order_list [asc| DEsC]]

Description

1) The SELECT clause specifies a column in a particular table to query, which can be an asterisk, an expression, a list, and so on.

2) into clause specifies that a new table is to be generated.

3) The FROM clause specifies the table or view to query.

4) The WHERE clause is used to limit the scope and condition of the query.

5) The GROUP BY clause specifies a group query clause.

6) The HAVING clause is used to specify the conditions of the grouping clause.

7) Order by can sort the results of a query based on one or more columns, in which the column name can be used, the relative column number is used, ASC represents ascending, and Desc is the descending.

8) MySQL aggregate function: sum (), COUNT (), AVG (), Max (), AVG () are all aggregate functions, when we use aggregate functions, we usually use Group by to group by first, and then perform the operation of the aggregate function. After the operation, it is necessary to use the HAVING clause to judge, such as whether the value of the aggregate function is greater than a certain value and so on.

From the Zabbix database to query the monitoring project method, here has queried the host network card traffic as an example:

1) Find the ID of the host through the hosts table.

Mysql> Select Host,hostid from hosts where host= "WWW05"; +-------+--------+| Host | HostID |+-------+--------+|  WWW05 | 10534 |+-------+--------+1 row in Set (0.00 sec)

2) Find the main monitoring item and key and Itemid through the items table.

Mysql> Select Itemid,name,key_ from items where hostid=10534 and key_= "Net.if.out[eth0]"; +--------+---------------- -+------------------+| Itemid | name |  Key_ |+--------+-----------------+------------------+| 58860 | Send traffic: | Net.if.out[eth0] |+--------+-----------------+------------------+1 row in Set (0.00 sec)

3) through the Itemid Query host monitoring project (History_uint or Trends_uint), in units of M.

host incoming traffic:

Mysql> select from_unixtime (Clock)  as datetime,round (value/1024/1024,2)  as  Traffic_in from history_uint where itemid= "58855"  and from_unixtime (clock) >= ' 2014-09-20 '  and from_unixtime (clock) < ' 2014-09-21 '  limit 20;+---------------------+----- -------+| datetime            | traffic_ in |+---------------------+------------+| 2014-09-20 00:00:55 |        0.10 | |  2014-09-20 00:01:55 |       0.09 | |  2014-09-20 00:02:55 |       0.07 | |  2014-09-20 00:03:55 |       0.05 | |  2014-09-20 00:04:55 |       0.03 | |  2014-09-20 00:05:55 |       0.06 | |  2014-09-20 00:06:55 |       0.12 | |  2014-09-20 00:07:55 |       0.05 | |  2014-09-20 00:08:55 |       0.10 | |  2014-09-20 00:09:55 |       0.10 | |  2014-09-20 00:10:55 |       0.12 | |  2014-09-20 00:11:55 |       0.12 | |  2014-09-20 00:12:55 |       0.13 | |  2014-09-20 00:13:55 |       3.16 | |  2014-09-20 00:14:55 |       0.23 | |  2014-09-20 00:15:55 |       0.24 | |  2014-09-20 00:16:55 |        0.26 | |  2014-09-20 00:17:55 |       0.23 | |  2014-09-20 00:18:55 |       0.14 | |  2014-09-20 00:19:55 |       0.16 |+------------------- --+------------+20 rows in set  (0.82 sec)

host outgoing traffic:

Mysql> select from_unixtime (Clock)  as datetime,round (value/1024/1024,2)  as  Traffic_out from history_uint where itemid= "58860"  and from_unixtime (clock) >= ' 2014-09-20 '  and from_unixtime (clock) < ' 2014-09-21 '  limit 20;+---------------------+---- ---------+| datetime            |  traffic_out |+---------------------+-------------+| 2014-09-20 00:00:00 |         4.13 | |  2014-09-20 00:01:00 |        3.21 | |  2014-09-20 00:02:00 |        2.18 | |  2014-09-20 00:03:01 |        1.61 | |  2014-09-20 00:04:00 |        1.07 | |  2014-09-20 00:05: 00 |        0.92 | |  2014-09-20 00:06:00 |        1.23 | |  2014-09-20 00:07:00 |        2.76 | |  2014-09-20 00:08:00 |        1.35 | |  2014-09-20 00:09:00 |        3.11 | |  2014-09-20 00:10:00 |        2.99 | |  2014-09-20 00:11:00 |        2.68 | |  2014-09-20 00:12:00 |        2.55 | |  2014-09-20 00:13:00 |        2.89 | |  2014-09-20 00:14:00 |        4.98 | |  2014-09-20 00:15:00 |        6.56 | |  2014-09-20 00:16:00 |        7.34 | |  2014-09-20 00:17:00 |        6.81 | |  2014-09-20 00:18:00 |        7.67 | |  2014-09-20 00:19:00 |        4.11 |+------------- --------+-------------+20 rows in set  (0.74 sec)

Mysql> select from_unixtime (Clock, "%y-%m-%d %h:%i")  as datetime,sum (Round (value/ 1024/1024,2))  as Traffic_total from history_uint where itemid in  ( 58855,58860)   and from_unixtime (clock) >= ' 2014-09-20 ' and from_unixtime (clock) < ' 2014-09-21 '  group by from_unixtime (Clock, "%y-%m-%d %h:%i")  limit 20;+----------- -------+---------------+| datetime         | traffic_ total |+------------------+---------------+| 2014-09-20 00:00 |           4.23 | |  2014-09-20 00:01 |          3.30 | |  2014-09-20 00:02 |          2.25 | |  2014-09-20 00:03 |          1.66 | |  2014-09-20 00:04 |          1.10 | |  2014-09-20 00:05 |          0.98 | |  2014-09-20 00:06 |          1.35 | |  2014-09-20 00:07 |          2.81 | |  2014-09-20 00:08 |          1.45 | |  2014-09-20 00:09 |          3.21 | |  2014-09-20 00:10 |          3.11 | |  2014-09-20 00:11 |          2.80 | |  2014-09-20 00:12 |          2.68 | |  2014-09-20 00:13 |          6.05 | |  2014-09-20 00:14 |          5.21 | |  2014-09-20 00:15 |          6.80 | |  2014-09-20 00:16 |          7.60 | |  2014-09-20 00:17 |          7.04 | |  2014-09-20 00:18 |          7.81 | |  2014-09-20 00:19 |          4.27 |+---- --------------+---------------+20 rows in set  (1.52 sec)

5) queries the maximum, minimum, and average value of host traffic during the day.

Mysql> select date as datetime,round (min (traffic)/2014/1024,2)  as TotalMinIN, Round (AVG (traffic)/1024/1024,2)  as totalavgin,round (max (traffic)/1024/1024,2)   as  totalmaxin from  (Select from_unixtime (Clock, "%y-%m-%d")  as date,sum (value)  as  traffic from history_uint where itemid in  (58855,58860)   and  from_unixtime (clock) >= ' 2014-09-20 '  and from_unixtime (clock) < ' 2014-09-21 '  group  by from_unixtime (Clock, "%y-%m-%d %h:%i")  )  tmp;+------------+------------+---------- --+------------+| datetime   | totalminin | totalavgin |  totalmaxin |+------------+------------+------------+------------+| 2014-09-20 |        0.01 |       4.63 |      191.30 |+------------+------------+------------+------------+1 row in set  (1.74 sec) 

Mysql> select from_unixtime (Hi.clock, "%y-%m-%d %h:%i")  as datetime,g.name as  group_name,h.host as host, hi.value as cpu_avg_idle from hosts_groups  hg join groups g on g.groupid = hg.groupid join items  i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid  Join history hi on  i.itemid = hi.itemid where g.name= ' Shanghai Engine Room--project test '  and i.key_= ' system.cpu.util[,idle] '  and  from_unixtime (clock) >= ' 2014-09-24 '  and from_unixtime (Clock) < ' 2014-09-25 '  group by h.host,from_unixtime (Hi.clock, "%Y- %m-%d %h:%i ")  limit 10;+------------------+----------------------------+----------+------------ --+| datetime         | group_name                  | host     |  cpu_avg_idle |+------------------+----------------------------+----------+--------------+|  2014-09-24 00:02 |  Shanghai Computer Room--Project test          |  testwb01 |      94.3960 | |  2014-09-24 00:07 |  Shanghai Computer Room--Project test          |  testwb01 |      95.2086 | |  2014-09-24 00:12 |  Shanghai Computer Room--Project test          |  testwb01 |      95.4308 | |  2014-09-24 00:17 |  Shanghai Computer Room--Project test          |  testwe01 |      95.4580 | |  2014-09-24 00:22 |  Shanghai Computer Room--Project test          | testwb01 |      95.4611 | |  2014-09-24 00:27 |  Shanghai Computer Room--Project test          |  testwb01 |      95.2939 | |  2014-09-24 00:32 |  Shanghai Computer Room--Project test          |  testwb01 |      96.0896 | |  2014-09-24 00:37 |  Shanghai Computer Room--Project test          |  testwb01 |      96.5286 | |  2014-09-24 00:42 |  Shanghai Computer Room--Project test          |  testwb01 |      96.8086 | |  2014-09-24 00:47 |  Shanghai Computer Room--Project test          |  testwb01 |      96.6854 |+------------------+----------------------------+----------+--------------+10 rows in set  (0.75 sec) 

7) Other SQL statements related to Zabbix.

The query host has been added but the monitoring host has not been turned on:

Select host from the hosts where Status=1;

Query the value of Nvps:

Mysql> SELECT Round (SUM (1.0/i.delay), 2) as QPS from items i,hosts h WHERE i.status= ' 0 ' and I.hostid=h.hostid and H.stat us= ' 0 ' and i.delay<>0; +--------+| QPS |+--------+| 503.40 |+--------+1 row in Set (0.11 sec)

Summary: Through the SQL statement can query out any monitoring project data, and at the end of the SQL statement through into outfile '/tmp/zabbix_result.txt ' directly to the results of the query to the system, in the script to send query results to the specified user, To realize the process of automated query, there are few articles on the Internet to introduce Zabbix database query, we hope to help you.

This article is from the "Simple Dreamer" blog, please be sure to keep this source http://sfzhang88.blog.51cto.com/4995876/1558254

How to get monitoring data from the Zabbix database