SQL Query to extract the currents alerts
Hello,
I think zabbix is a marvellous product and i would like to developp a program which would display (in realtime) all the currents alerts, by querying the zabbix database.
In the zabbix code, I found two queries :
The query 1 extracts all the currents triggers :
SELECT DISTINCT t.*,h.host,h.hostid FROM triggers t,functions f,items i,hosts h WHERE ((t.triggerid BETWEEN 100000000000000 AND 199999999999999))
AND NOT EXISTS ( SELECT ff.functionid FROM functions ff WHERE ff.triggerid=t.triggerid AND EXISTS ( SELECT ii.itemid FROM items ii, hosts hh WHERE ff.itemid=ii.itemid AND hh.hostid=ii.hostid AND ( ii.status<>0 OR hh.status<>0 ) ) ) AND t.status=0 AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND h.hostid=i.hostid AND (t.value IN (1))
then for each row of the query 1, another query is executed :
SELECT e.eventid, e.value, e.clock, e.objectid as triggerid, e.acknowledged FROM events e WHERE e.object=0 AND e.objectid=100100000013026 AND e.value=1 ORDER by e.object DESC, e.objectid DESC, e.eventid DESC
(in this example, 100100000013026 is equal t.triggerid)
Here is my question : is there a way to extract all the currents alerts with a unique sql query ?
PS : sorry for my bad english !!!!
Thank you very much
I think zabbix is a marvellous product and i would like to developp a program which would display (in realtime) all the currents alerts, by querying the zabbix database.
In the zabbix code, I found two queries :
The query 1 extracts all the currents triggers :
SELECT DISTINCT t.*,h.host,h.hostid FROM triggers t,functions f,items i,hosts h WHERE ((t.triggerid BETWEEN 100000000000000 AND 199999999999999))
AND NOT EXISTS ( SELECT ff.functionid FROM functions ff WHERE ff.triggerid=t.triggerid AND EXISTS ( SELECT ii.itemid FROM items ii, hosts hh WHERE ff.itemid=ii.itemid AND hh.hostid=ii.hostid AND ( ii.status<>0 OR hh.status<>0 ) ) ) AND t.status=0 AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND h.hostid=i.hostid AND (t.value IN (1))
then for each row of the query 1, another query is executed :
SELECT e.eventid, e.value, e.clock, e.objectid as triggerid, e.acknowledged FROM events e WHERE e.object=0 AND e.objectid=100100000013026 AND e.value=1 ORDER by e.object DESC, e.objectid DESC, e.eventid DESC
(in this example, 100100000013026 is equal t.triggerid)
Here is my question : is there a way to extract all the currents alerts with a unique sql query ?
PS : sorry for my bad english !!!!
Thank you very much
Comment