前言

zabbix稳定运行一段时间之后，积累了一定量的数据。老板看运维整天没事干，让折腾一个酷炫的大屏显示。同时在这个基础上有一定的定制话需求。记录一下自己查询zabbix数据库的查询语句。

表结构

监控表

# hosts表
# 存储被监控机器的信息 ,包含模版信息
select * from hosts where hostid = '10434'\G

# items表
# 核心表之一，记录了item的所有设置
select * from items where hostid = '10434'\G

# hosts_templates表
# 存储机器和模版或模版和模版之间的关系
select * from hosts_templates \G
  
# interface表
# 存储了所有设备的IP和端口数据（实际监控设备，此表更准确）
select * from interface\G

数据存储表

# 相互关联的表
show tables like '%history%';
show tables like '%trend%'; 

# 将clock,和ns转化为可读
select itemid,from_unixtime(clock),value,ns/1000000000  from history limit 1;

报警相关表

# 核心是expression，存储报警逻辑。
select * from triggers limit 1 \G

# 根据trigger表中expression 的{12641}，通过functions表查找itemid。
select * from functions where functionid=12641;

select * from items where itemid =22189\G

实际应用

简单查询

# 找出交换机端口流量相关的item
select * from hosts where hostid = '10434'\G;

# 统计监控项的个数
SELECT count(*) FROM items WHERE hostid=10434;

# 统计和网络相关的监控项个数
SELECT count(*) FROM items WHERE hostid=10434 AND key_ LIKE '%net.if.in%' \G;

# 查找itemid 
SELECT name,snmp_oid,itemid  FROM items WHERE hostid=10434 AND key_ LIKE '%net.if.in%';

# 查看数据采集情况
select itemid,from_unixtime(clock),value,ns/1000000000  from history_uint  where itemid = 36384;

# 结果精确到分钟
select itemid,date_format(from_unixtime(clock), '%Y-%m-%d %H:%i' ), value as '36336' from history_uint where  itemid =  36336  limit 10; 

交换机流量查询

# 查找类似表
show tables like '%history%';
  
# 列出单端口的值
SELECT  date_format(from_unixtime(clock), '%Y-%m-%d %H:%i') as  time , value FROM  history_uint WHERE  itemid = 36385 ;

# 多端口同时列出
SELECT  time,
SUM(IF(`itemid`='36384', value, 0)) as '36384',
SUM(IF(`itemid`='36385', value, 0)) as '36385',
SUM(IF(`itemid`='36552', value, 0)) as '36552',
SUM(IF(`itemid`='36553', value, 0)) as '36553'
FROM
(SELECT  date_format(from_unixtime(clock), '%Y-%m-%d %H:%i') as time , value, itemid
FROM  history_uint
WHERE  itemid = 36384  or itemid = 36385 or itemid = 36552 or itemid = 36553) TEMP
GROUP BY time

# 端口求和
SELECT time1, SUM(value)  
FROM 
(SELECT  date_format(from_unixtime(clock), '%Y-%m-%d %H:%i') as time1 , value 
FROM  history_uint 
WHERE itemid IN (36552, 36553)
AND clock > 1623517200
AND clock < 1623519000
ORDER BY  time1) 
AS temp 
GROUP BY time1;