zabbix 按trigger统计告警

zhangli-s

　　需求， 要统计某人收到的不同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;