zabbix 按trigger统计告警
需求， 要统计某人收到的不同trigger告警的数量。首先，在users表里查到他的userid，这里是35.
用命令换算时间（zabbix里面的时间是用从1970-01-01 00:00:00到现在的秒数计算的）
date -d "2013-09-02 14:00:00" +%s
这里是1385830800和1387126800
把他们带入sql中（写得丑，别笑）
select eventid , tricount , hosts.name,kjh.description , expression ,functionid , kjh.itemid , kjh.triggerid , function , parameter,kjh.hostid,hosts.host from (select eventid , tricount , ytr.description , expression ,functionid , ytr.itemid , ytr.triggerid , function , parameter,hostid from (select eventid , tricount , description , expression ,functionid , itemid , potk.triggerid , function , parameter from (select fsdaf.eventid,tricount,triggerid,description,expression from ( select eventid ,objectid ,count(objectid) as tricount from events where eventid in ( select eventid from alerts where userid = '35' and clock between '1385830800' and '1387126800') group by objectid order by count(objectid) desc) as fsdaf inner join triggers on triggerid=objectid) as potk inner join functions on potk.triggerid=functions.triggerid ) as ytr inner join items on items.itemid=ytr.itemid) as kjh inner join hosts on hosts.hostid=kjh.hostid;
-----------2014/1/8更新
1.8版本的数据库有略微不同.
把hosts.name改成hosts.host
-----------2014/1/14更新
通过action来查
select eventid , tricount , hosts.name,kjh.description , expression ,functionid , kjh.itemid , kjh.triggerid , function , parameter,kjh.hostid,hosts.host from
(select eventid , tricount , ytr.description, expression ,functionid , ytr.itemid , ytr.triggerid , function , parameter,hostid from
(select eventid , tricount , description , expression ,functionid , itemid , potk.triggerid , function , parameter from
(select fsdaf.eventid,tricount,triggerid,description,expression from
( select eventid ,objectid ,count(objectid) as tricount from events where eventid in
( select eventid from alerts where actionid = '35' and clock between '1385830800' and '1387126800')
group by objectid order by count(objectid) desc) as fsdaf
inner join triggers on triggerid=objectid) as potk
inner join functions on potk.triggerid=functions.triggerid ) as ytr
inner join items on items.itemid=ytr.itemid) as kjh
inner join hosts on hosts.hostid=kjh.hostid;
页:
[1]