读取zabbix数据库中的网络流量数据

读取zabbix数据库中的网络流量数据 （1）首先，根据主机名找到hostid

select  host,hostid  from  hosts  where  host= "Zabbix server" ;
mysql> select  host,hostid  from  hosts  where  host= "Zabbix server" ;
+---------------+--------+
| host          | hostid |
+---------------+--------+
| Zabbix server |  10084 |
+---------------+--------+
1 row in set (0.00 sec)

（2）根据hostid和key值找到对应的itemid

select  itemid, name ,key_  from  items  where  hostid=10084  and  key_= "line.utilization[192.168.100.20,200000000,ifHCInOctets.369099151,ifHCOutOctets.369099151]" ;
mysql> select  itemid, name ,key_  from  items  where  hostid=10084  and  key_= "line.utilization[192.168.100.20,200000000,ifHCInOctets.369099151,ifHCOutOctets.369099151]" ;
+--------+-------------------------+-------------------------------------------------------------------------------------------+
| itemid | name                    | key_                                                                                      |
+--------+-------------------------+-------------------------------------------------------------------------------------------+
|  39621 | BJ_AliCloud_utilization | line.utilization[192.168.100.20,200000000,ifHCInOctets.369099151,ifHCOutOctets.369099151] |
+--------+-------------------------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（3）通过itemid查询主机的监控项目（history_uint或者trends_uint)，单位为%

select  from_unixtime(clock)  as  DateTime,round(value/1024/1024,2)  as  BJ_AliCloud_utilization  from  history_uint  where  itemid= "39621"  and  from_unixtime(clock)>= '2022-01-29 15:00'  and  from_unixtime(clock)< '2022-01-29 16:00'  limit 20;
mysql> select  from_unixtime(clock)  as  DateTime,round(value/1024/1024,2)  as  BJ_AliCloud_utilization  from  history_uint  where  itemid= "39621"  and  from_unixtime(clock)>= '2022-01-29 15:00'  and  from_unixtime(clock)< '2022-01-29 16:00'  limit 20;
+---------------------+-------------------------+
| DateTime            | BJ_AliCloud_utilization |
+---------------------+-------------------------+
| 2022-01-29 15:00:21 |                    0.00 |
| 2022-01-29 15:01:21 |                    0.00 |
| 2022-01-29 15:02:21 |                    0.00 |
| 2022-01-29 15:03:21 |                    0.00 |
| 2022-01-29 15:04:21 |                    0.00 |
| 2022-01-29 15:05:21 |                    0.00 |
| 2022-01-29 15:06:21 |                    0.00 |
| 2022-01-29 15:07:21 |                    0.00 |
| 2022-01-29 15:08:21 |                    0.00 |
| 2022-01-29 15:09:21 |                    0.00 |
| 2022-01-29 15:10:21 |                    0.00 |
| 2022-01-29 15:11:21 |                    0.00 |
| 2022-01-29 15:12:22 |                    0.00 |
| 2022-01-29 15:13:21 |                    0.00 |
| 2022-01-29 15:14:21 |                    0.00 |
| 2022-01-29 15:15:21 |                    0.00 |
| 2022-01-29 15:16:21 |                    0.00 |
| 2022-01-29 15:17:21 |                    0.00 |
| 2022-01-29 15:18:21 |                    0.00 |
| 2022-01-29 15:19:21 |                    0.00 |
+---------------------+-------------------------+
20 rows in set (0.49 sec)

（4）查询一天中主机流量的最大值，最小值和平均值

select  date  as  DateTime,round(min(utilization)/1024/1024,2)  as  TotalMinIN,round(avg(utilization)/1024/1024,2)  as  TotalAvgIN,round(max(utilization)/1024/1024,2)   as  TotalMaxIN  from  (select  from_unixtime(clock, "%Y-%m-%d")  as  date , sum(value)  as  utilization  from  history_uint  where  itemid= "39621"  and  from_unixtime(clock)>= '2022-01-29 15:00'  and  from_unixtime(clock)< '2022-01-29 16:00'  group  by  from_unixtime(clock, "%Y-%m-%d %H:%i")) tmp;

mysql> select  date  as  DateTime,round(min(utilization)/1024/1024,2)  as  TotalMinIN,round(avg(utilization)/1024/1024,2)  as  TotalAvgIN,round(max(utilization)/1024/1024,2)   as  TotalMaxIN  from  (select  from_unixtime(clock, "%Y-%m-%d")  as  date , sum(value)  as  utilization  from  history_uint  where  itemid= "39621"  and  from_unixtime(clock)>= '2022-01-29 15:00'  and  from_unixtime(clock)< '2022-01-29 16:00'  group  by  from_unixtime(clock, "%Y-%m-%d %H:%i")) tmp;
+------------+------------+------------+------------+
| DateTime   | TotalMinIN | TotalAvgIN | TotalMaxIN |
+------------+------------+------------+------------+
| 2022-01-29 |       0.00 |       0.00 |       0.00 |
+------------+------------+------------+------------+
1 row in set (0.03 sec)

注意事项：

（1） 如果mysql数据库中@@GLOBAL.sql_mode、@@SESSION.sql_mode设置为ONLY_FULL_GROUP_BY模式就会和之前 group  by语法不兼容，需要去掉。

查看这两个模式：

去除 ONLY_FULL_GROUP_BY

（2）(value/1024/1024,2)，这个地方要注意，是根据采集的数据进行运算，并保留小数位数