zabbix数据库获取监控数据

登录数据库

获取hostid

select host,hostid from hosts where host="test";

输出结果：

+---------------------+--------+

| host                | hostid |

+---------------------+--------+

| test                |  10218 |

+---------------------+--------+


获取itemid

select itemid,name,key_ from items where hostid=10218 and key_="net.if.out[eth0]";

输出结果：

+--------+--------------------------------+-----------------+

| itemid | name                           | key_            |

+--------+--------------------------------+-----------------+

|  32590 | Outgoing network traffic on $1 | net.if.out[eth0] |

+--------+--------------------------------+-----------------+


获取监控数据

select from_unixtime(clock) as DateTime,round(value/1024/1024,2) as Traffic_out from history_uint where itemid="32590" and from_unixtime(clock)>='2014-12-21' and from_unixtime(clock)<'2014-12-22' limit 20;

输出结果(单位M)：

+---------------------+-------------+

| DateTime            | Traffic_out |

+---------------------+-------------+

| 2014-12-21 00:00:10 |       71.48 |

| 2014-12-21 00:01:10 |       66.32 |

| 2014-12-21 00:02:10 |       66.71 |

| 2014-12-21 00:03:10 |       67.68 |

| 2014-12-21 00:04:10 |       73.14 |

| 2014-12-21 00:05:10 |       70.61 |

| 2014-12-21 00:06:10 |       67.42 |

| 2014-12-21 00:07:10 |       69.25 |

| 2014-12-21 00:08:10 |       70.07 |

| 2014-12-21 00:09:10 |       66.04 |

| 2014-12-21 00:10:10 |       71.19 |

| 2014-12-21 00:11:10 |       62.87 |

| 2014-12-21 00:12:10 |       70.46 |

| 2014-12-21 00:13:10 |       64.66 |

| 2014-12-21 00:14:10 |       59.36 |

| 2014-12-21 00:15:10 |       62.62 |

| 2014-12-21 00:16:10 |       59.48 |

| 2014-12-21 00:17:10 |       61.88 |

| 2014-12-21 00:18:10 |       67.57 |

| 2014-12-21 00:19:10 |       63.85 |

+---------------------+-------------+


获取某天网卡流量最大值，平均值，最小值

select date as DateTime,round(min(traffic)/1024/1024,2) as TotalMinOut,round(avg(traffic)/1024/1024,2) as TotalAvgOut,round(max(traffic)/1024/1024,2) as TotalMaxOut from (select from_unixtime(clock,"%Y-%m-%d") as date,sum(value) as traffic from history_uint where itemid="32590" and from_unixtime(clock)>='2014-12-21' and from_unixtime(clock)<'2014-12-22' group by from_unixtime(clock,"%Y-%m-%d %H:%i"))tmp;

输出结果(单位M)：

+------------+-------------+-------------+-------------+

| DateTime   | TotalMinOut | TotalAvgOut | TotalMaxOut |

+------------+-------------+-------------+-------------+

| 2014-12-21 |        6.30 |       97.31 |      190.53 |

+------------+-------------+-------------+-------------+


关联查询：

select HostName,date as DateTime,round(min(source)/1024/1024,2) as TotalMinOut,round(avg(traffic)/1024/1024,2) as TotalAvgOut,round(max(source)/1024/1024,2) as TotalMaxOut from (select hosts.host as HostName,from_unixtime(clock,"%Y-%m-%d") as date,sum(value) as traffic,value as source from items left outer join history_uint on items.itemid=history_uint.itemid inner join hosts on hosts.hostid=items.hostid where hosts.host="test" and items.key_="net.if.out[em1]" and from_unixtime(clock)>='2014-12-21' and from_unixtime(clock)<'2014-12-22' group by from_unixtime(clock,"%Y-%m-%d %H:%i"))tmp;

输出结果：

+---------------------+------------+-------------+-------------+-------------+

| HostName            | DateTime   | TotalMinOut | TotalAvgOut | TotalMaxOut |

+---------------------+------------+-------------+-------------+-------------+

| test                | 2014-12-21 |        6.30 |       97.31 |      190.53 |

+---------------------+------------+-------------+-------------+-------------+