zabbix中的sql

 

#select *from hosts where hostid=10322
#select * from items where hostid=10322 and key_ like 'net.if%' and `name` like '%0/0/1%'
#select * from history_uint where itemid in (33129,32944) and clock=1597119605
#select ROUND(value/1000/1000,2) as '0/0/1-in' from history_uint where clock=1597119605 and itemid=33129
#select FROM_UNIXTIME(clock) as 'date' ,ROUND(value/1000/1000,2) as '0/0/1-in' from history_uint where itemid=33129 limit 3

查询占库大小：

select table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) AS Size_in_MB from information_schema.TABLES  where table_schema = 'zabbix5' order by Size_in_MB desc limit 10;

 

#查询主机的hostid
select * from hosts where name like '成都电信%'

#查询主机key_和别名
select * from items where hostid=10362 and name like "%10GE1/0/1%"

#zabbix查询主机接口的value
select from_unixtime(clock) as DateTime,value,ns from history_uint where itemid=35318 order by clock desc limit 10;
select from_unixtime(clock) as DateTime,round(value/1024/1024,2) as Traffic_in from history_uint where itemid=35318 and from_unixtime(clock)>='2020-12-20' and from_unixtime(clock)<'2020-12-21' limit 10

select itemid,from_unixtime(clock) as time,value from history_uint where itemid=34355 and clock >= unix_timestamp('2021/03/21 00:00:00') and clock <= unix_timestamp('2021/03/31 00:00:00') 

#关联查询

select from_unixtime(a.clock) as data ,a.in as `in`, b.out as `out` from (
(
select clock, `value` as `in` from history_uint where itemid = 34192 and from_unixtime(clock)>'2021/1/3' and from_unixtime(clock)<"2021/1/4"
) as a left join (select clock, `value` as `out` from history_uint where itemid = 34355 and from_unixtime(clock)>'2021/1/3' and from_unixtime(clock)<"2021/1/4") as b on a.clock = b.clock)