Reporting: Top 10 triggers

[abGeopostuk]

We are currently using zabbix 1.6.4 and I were wondering if there was a way of creating more detailed reports.

In the version we use there is a report containing the top 100 most active triggers but many of these triggers are simply just changes in state. Some of our trigger alerts are turned off at specific times of night and as a result are not needed for our report.

We would like to create a report containing the top 10, 15 or even 20 true alerting triggers, a report we could run monthly and see which of our systems have been the worst offenders. So any help with this would be great!

I'm not sure if there are any SQL commands that can be used to get this data from the Zabbix DB...??

Thanks for any help in advance

[jamesh]

Bump...I'm interested in this too. Anyone have the SQL that runs? I could probably sort out how to exclude "Not classified" severity and only show particular host groups myself.

[jamesh]

Answered my own question. This gives you the week view:

SELECT h.name,t.description,t.priority,count(distinct e.eventid) AS cnt_event FROM triggers t,events e,functions f, items i,hosts h,hosts_groups hg WHERE t.triggerid=e.objectid AND e.source=0 AND e.object=0 AND e.clock>UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 day)) AND t.flags IN ('0','4') AND f.triggerid = t.triggerid AND i.itemid = f.itemid AND i.hostid = h.hostid AND h.hostid = hg.hostid GROUP BY e.objectid ORDER BY cnt_event desc LIMIT 100 OFFSET 0;

And this excludes "Not classified" severity and filters to a specific host group id (replace nn with your host group id):

SELECT h.name,t.description,t.priority,count(distinct e.eventid) AS cnt_event FROM triggers t,events e,functions f, items i,hosts h,hosts_groups hg WHERE t.triggerid=e.objectid AND e.source=0 AND e.object=0 AND e.clock>UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 day)) AND t.flags IN ('0','4') AND t.priority >= 1 AND f.triggerid = t.triggerid AND i.itemid = f.itemid AND i.hostid = h.hostid AND h.hostid = hg.hostid AND hg.groupid=nn GROUP BY e.objectid ORDER BY cnt_event desc LIMIT 100 OFFSET 0;

[jamesh]

Also...top 20 flappiest hosts:

SELECT h.name,count(distinct e.eventid) AS cnt_event FROM triggers t,events e,functions f, items i,hosts h,hosts_groups hg WHERE t.triggerid=e.objectid AND e.source=0 AND e.object=0 AND e.clock>UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 day)) AND t.flags IN ('0','4') AND f.triggerid = t.triggerid AND i.itemid = f.itemid AND i.hostid = h.hostid AND h.hostid = hg.hostid GROUP BY h.hostid ORDER BY cnt_event desc LIMIT 20;