SQL Query to find all triggers with status problem

[AtomcMan]

I require an SQL query to return the records in the zabbix database that show the triggers that are in a problem state. This is exactly the same as what is displayed in the 'Last 20 issues' on the dashboard page. I do not have any trigger dependencies setup so it shouldn't be as complex.

The events tables seems to be the one to look at but it gives some false positives (equating to the records that have flashing 'OK' values)

[mrogers-9898]

I've been messing with this myself...

Its a bit all over the place, i'm sure with a bit of work it could be one query...

I get a list of the HostIDs I wish to check for trigger status, then with that HostID I check for triggers with,

select i.itemid, f.triggerid, t.description
from items i
left join hosts h on i.hostid = h.hostid
left join functions f on i.itemid = f.itemid
left join triggers t on f.triggerid = t.triggerid
where h.hostid = *HostID*
and t.status = 0
and i.status = 0

I then use the f.triggerid from that last query on

Select priority
from triggers
where triggerid = *triggerid*
and value <> 0

I also check to see if its been acknowledged or not with the same triggerid (notice the query uses objectid not triggerid - strange)

SELECT acknowledged
FROM events e
where objectid = *triggerid*
order by clock desc
limit 1

That last query appears really dodgey to my eyes, but with myself having limited SQL-fu thats as far as I've gotten....

[danrog]

This is what we use for a mobile version of the triggers page (includes last value for those triggers that have {ITEM.LASTVAUE} as part of the description.

$query= 'SELECT DISTINCT host, t.description, f.triggerid, e.acknowledged, t.value, i.lastvalue '.
' FROM triggers t'.
' INNER JOIN functions f ON ( f.triggerid = t.triggerid )'.
' INNER JOIN items i ON ( i.itemid = f.itemid )'.
' INNER JOIN hosts ON ( i.hostid = hosts.hostid )'.
' INNER JOIN events e ON ( e.objectid = t.triggerid )'.
' WHERE (e.eventid DIV 100000000000000)'.
' IN (0)'.
' AND (e.object-0)=0'.
' AND (t.value=1 OR (t.value =0 AND unix_timestamp(now()) - t.lastchange <60))'.
' AND hosts.status =0'.
' AND i.status =0'.
' AND t.status =0'.
' GROUP BY f.triggerid'.
' ORDER BY t.lastchange DESC';

[mrogers-9898]

nice query, danrog!

I see your SQL-fu is strong.

[AtomcMan]

Thanks heaps for that It works nicely.

I did find I needed to remove the line ' WHERE (e.eventid DIV 100000000000000)'. otherwise I got no results

[danrog]

AtomcMan, out of curiosity, what type of DB are you using?

[AtomcMan]

We are using MySQL on CentOS 5.4

[danrog]

Hmm....odd that "WHERE (e.eventid DIV 100000000000000)" didn't work for you. I use MySQL as well and all my custom queries use similar where conditions. Oh well, as long as the rest works for you.

[mrogers-9898]

How would you adjust the query to find only active triggers that have not been acknowledged?

[melpheos]

How would you adjust the query to find only active triggers that have not been acknowledged?

AND e.acklowledged = 0


strangly enough it was working last week for me but it seems the change to summertime has screwed everything up and it's not working anymore :-/

Now i get same results with e.acknowledged = 0 and = 1...

Need to find what is wrong with the query

[mrogers-9898]

thanks! that was pretty clear once you pointed it out

I appear to get consistent results with changing the e.acknowledged value to 0/1.

[exkg]

Hi,

Also is helpfull click in option debug (to right) in ZABBIX GUI ... is possible see all queries used in each page.


[]s,
Luciano

[melpheos]

thanks! that was pretty clear once you pointed it out

I appear to get consistent results with changing the e.acknowledged value to 0/1.

i think my database has been somewhat corrupted by the summer time change because i can't get the result i had before

[melpheos]

OK it was the database that was a bit broken after change to summertime with trigger activated.

After deleting the trigger and deleting the events directly in the DB it went back to normal.

As a bonus, here is a correction of the query
put

AND e.object=0

instead of (e.object-0)=0 (which does nothing at all but at a useless calculation)

The query takes .03 seconds instead of......... damn that's long seconds...

Edit : still a small problem. When a trigger is active and has been acknowledged but the same trigger has been active in the past but resolved and was not acknowledged, you still receive results with this query. You can either make an acknowledge on the whole db from time to time or we need to find another query. Working on it and keep you posted

[melpheos]

Finally after some more work i got what i need

SELECT DISTINCT host, f.triggerid, e.acknowledged, priority 
FROM triggers t 
INNER JOIN functions f ON ( f.triggerid = t.triggerid ) 
INNER JOIN items i ON ( i.itemid = f.itemid ) 
INNER JOIN hosts ON ( i.hostid = hosts.hostid ) 
INNER JOIN events e ON ( e.objectid = t.triggerid ) 
WHERE (e.eventid DIV 100000000000000) 
IN (0) 
AND e.object = 0 
AND t.value=1 
AND hosts.status = 0 
AND i.status = 0 
AND t.status = 0 
AND e.acknowledged = 0 
AND e.clock > (SELECT DISTINCT max(e.clock) 
FROM triggers t 
INNER JOIN functions f ON ( f.triggerid = t.triggerid ) 
INNER JOIN items i ON ( i.itemid = f.itemid ) 
INNER JOIN hosts ON ( i.hostid = hosts.hostid ) 
INNER JOIN events e ON ( e.objectid = t.triggerid ) 
WHERE (e.eventid DIV 100000000000000) 
IN (0) 
AND e.object = 0 
AND t.value=1 
AND hosts.status = 0 
AND i.status = 0 
AND t.status = 0 
AND e.acknowledged = 1 
)
ORDER BY priority DESC;