I need a custom SQL query

[radamand]

I need a custom SQL query out of the zabbix DB but I dont know the schema well enough to write it myself, i'm hoping someone here can help?

I need a list of all events that generated an email notification (not recoveries) over the past month, ordered by time/date, and including the trigger description.

Can someone here help with this please??

[sarathyme]

Plz see if this helps

SELECT t.description DESCRIPTION, 
       a.sendto SENDTO, 
       From_unixtime(a.clock, '%Y-%m-%d %H.%i.%s') DATE
FROM   triggers t, 
       events e, 
       alerts a, 
       media_type m 
WHERE  a.mediatypeid = m.mediatypeid 
       AND e.source = 0 
       AND e.objectid = t.triggerid 
       AND e.eventid = a.eventid 
       AND m.description = 'Email' 
       AND e.VALUE = 0 
       AND e.clock > unix_timestamp('2010-09-01 00:00:00') 
ORDER BY e.clock;

The data about notifications are stored in table alerts

When the trigger changes status it creates an event.

An event can have multiple alerting channel (media types)
and for each channel of delivery there could be multiple delivery ends


You may need to change the time in the above query.
m.descriptino='Email' filters out only emails. (what u see in media types screen)

e.value=0 filters only problem notifications
if you want to have recovery notification also take that condition out.

Regards
Vijay

[radamand]

excellent, thank you!

one question, e.value = 0? what is event.value?

[bashman]

http://www.zabbix.com/forum/showthread.php?t=16038

[bashman]

I need to get the trigger duration, the time a trigger has been "on" the last month.