zabbix通过mysql查询接口数据

 

1，首先查询需要查询主机的hositid

select host,hostid from hosts WHERE host="xxx"

2，查询主机的item

select  itemid,name,key_ from items where hostid=hostid and name regexp "流入|流出" 

3，查询该item的具体数据

select from_unixtime(clock) as DateTime,round(value/1/1,2) as Traffic_in from history_uint where itemid=item and from_unixtime(clock)>='2020-10-20' and from_unixtime(clock)<'2020-10-21' limit 20;  #原始数据
select from_unixtime(clock) as DateTime,round(value/1024/1024,2) as Traffic_out from history_uint where itemid="item" and from_unixtime(clock)>='2020-10-20' and from_unixtime(clock)<'2020-10-21' limit 20; #计算后的单位，可以除1000、1024自选

4，流量汇总

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 (流入/流出的item) and from_unixtime(clock)>='2020-09-01'and from_unixtime(clock)<'2020-10-01' group by from_unixtime(clock,"%Y-%m-%d %H:%i");