查询zabbix相关数据sql语句笔记
2021/7/8 19:05:48
本文主要是介绍查询zabbix相关数据sql语句笔记,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
1、查询zabbix过去一周群组告警总数
SELECT a.hostid, a.`host`, a.`status`, a.`name` AS hostnname, b.`name` AS groupname, d.`name` AS item, FROM_UNIXTIME( e.clock ) AS eventtime, e.`name`, e.severity FROM HOSTS a JOIN hosts_groups c ON a.hostid = c.hostid JOIN hstgrp b ON b.groupid = c.groupid AND b.NAME LIKE '%Oracle%' JOIN items d ON a.hostid = d.hostid AND d.flags <> 1 AND d.flags <> 2 JOIN ( SELECT itemid, triggerid FROM functions GROUP BY itemid, triggerid ) f ON d.itemid = f.itemid JOIN EVENTS e ON f.triggerid = e.objectid AND e.`value` = 1 AND yearweek( FROM_UNIXTIME( e.clock ), 1 )= yearweek( now(), 1 ) ORDER BY e.clock
2、查询zabbix所有监控指标触发器和告警等级
SELECT * FROM (SELECT a.hostid,b.`name` AS 系统名称,a.`host` AS 地址,d.`name` AS 监控项,d.key_ as 键值,d.itemid from `hosts` a,hstgrp b,hosts_groups c,items d where a.available = 1 and a.hostid =c.hostid and b.groupid = c.groupid AND b.`name` LIKE '%45-%' AND A.hostid = D.hostid AND d.key_ not LIKE '%#%' AND d.key_ not like '%IPADDRESS%' ORDER BY b.`name`,a.`host`) A LEFT JOIN (SELECT m.itemid,m.`name` as 触发器函数,m.parameter as 触发器参数,n.expression as 触发器条件, n.recovery_mode AS 恢复模式,n.recovery_expression, CASE WHEN n.priority =5 THEN '一级' WHEN n.priority =4 THEN '二级' WHEN n.priority =3 THEN '三级' WHEN n.priority =2 THEN '四级' WHEN n.priority =1 THEN '五级' WHEN n.priority =0 THEN '六级' END AS 告警等级 from functions m,`triggers`n where n.triggerid=m.triggerid -- GROUP BY itemid ) B ON A.itemid = B.itemidView Code
3、导出zabbix群组所有监控主机指标
SELECT * FROM (SELECT a.hostid,b.`name` AS 系统名称,a.`host` AS 地址,d.`name` AS 监控项,d.key_ as 键值,d.itemid from `hosts` a,hstgrp b,hosts_groups c,items d where a.available = 1 and a.hostid =c.hostid and b.groupid = c.groupid AND b.`name` LIKE '%45-%' AND A.hostid = D.hostid AND d.key_ not LIKE '%#%' AND d.key_ not like '%IPADDRESS%' ORDER BY b.`name`,a.`host`) A LEFT JOIN (SELECT m.itemid,m.`name` as 触发器函数,m.parameter as 触发器参数,n.expression as 触发器条件 from functions m,`triggers`n where n.triggerid=m.triggerid -- GROUP BY itemid ) B ON A.itemid = B.itemidView SQL
4、导出zabbix群组所有监控主机
SELECT b.`name` AS 系统名称,a.`host` AS 地址 from `hosts` a,hstgrp b,hosts_groups c -- where a.`status` = 1 WHERE a.hostid =c.hostid and b.groupid = c.groupid AND a.`status` <> 3 AND (b.`name` LIKE '%45-%') -- AND A.hostid = D.hostid AND d.key_ not LIKE '%#%' AND d.key_ not like '%IPADDRESS%' ORDER BY b.`name`,a.`host`View SQL
5、查看所有监控项by chenxiaowei
SELECT * FROM ( SELECT a.hostid, b.`name` AS 系统名称, a.`host` AS 地址, d.`name` AS 监控项, d.key_ AS 键值, d.itemid FROM `hosts` a, hstgrp b, hosts_groups c, items d WHERE a.available = 1 AND a.hostid = c.hostid AND b.groupid = c.groupid AND b.`name` LIKE '%45-%' AND A.hostid = D.hostid AND d.key_ NOT LIKE '%#%' AND d.key_ NOT LIKE '%IPADDRESS%' AND d.key_ NOT LIKE '%discovery%' ORDER BY b.`name`, a.`host` ) A LEFT JOIN ( SELECT m.itemid, m.nameparameter AS 触发器函数参数, -- m.parameter AS 触发器参数, n.expression AS 触发器条件, n.recovery_mode AS 恢复模式, CASE WHEN n.priority = 5 THEN '一级' WHEN n.priority = 4 THEN '二级' WHEN n.priority = 3 THEN '三级' WHEN n.priority = 2 THEN '四级' WHEN n.priority = 1 THEN '五级' WHEN n.priority = 0 THEN '六级' END AS 告警等级 FROM ( SELECT m.itemid, m.triggerid, CONCAT(GROUP_CONCAT( CONCAT(NAME, '(', parameter, ')') )) nameparameter FROM functions m GROUP BY m.itemid, m.triggerid ) m, `triggers` n WHERE n.triggerid = m.triggerid ) B ON A.itemid = B.itemidView SQL
6、 测试
SELECT * from `hosts` a, hstgrp b, hosts_groups c where a.hostid = c.hostid and b.groupid = c.groupid AND b.`name` like '%zabbix%' -- 10277 12265 SELECT * from HOSTS WHERE `host` = '10.24.1.101' SELECT * from items WHERE hostid = 12265 and flags <>1 AND flags <>2 SELECT * from (SELECT m.itemid,m.`name` from items m where m.hostid = 12265 and m.flags <>1 AND m.flags <>2) A LEFT JOIN (SELECT itemid, triggerid FROM functions GROUP BY itemid, triggerid) AS B ON A.itemid = B.itemid SELECT eventid,objectid,`name`,FROM_UNIXTIME(clock),severity,VALUE from `events` WHERE NAME LIKE '%10.24.1.101%' AND `value` = 1View Code
这篇关于查询zabbix相关数据sql语句笔记的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-09-28微服务架构中API版本控制的实践
- 2024-09-28AI给的和自己写的Python代码,都无法改变输入框的内容,替换也不行
- 2024-09-27Sentinel配置限流资料:新手入门教程
- 2024-09-27Sentinel配置限流资料详解
- 2024-09-27Sentinel限流资料:新手入门教程
- 2024-09-26Sentinel限流资料入门详解
- 2024-09-26Springboot框架资料:初学者入门教程
- 2024-09-26Springboot框架资料详解:新手入门教程
- 2024-09-26Springboot企业级开发资料:新手入门指南
- 2024-09-26SpringBoot企业级开发资料新手指南