SQL Query to find all triggers with status problem

[danrog]

Updated my query

I also started to see issues with my original query. I wrote this on a dev system with very little usage, so the same issues you had only showed up when we went to production with it. I've incorporated a few more columns (this is used on an external mobile phone web interface so it might be much more then what you need). I wanted to get as close to our triggers page as possible without all the extra stuff (keeps recently cleared triggers on the page for 60 seconds). Total execution time on our system ~0.1016 secs.

Also, I opted to use MAX(eventid) instead of e.clock since that would guarantee I would see the latest event for a trigger. Using e.clock works but it didn't actually pull all the other rows across properly (could be something I was doing wrong too, I am by no means a SQL programmer).

SELECT host, t.description, f.triggerid, e.acknowledged, t.value, i.lastvalue, IF(e.acknowledged=1,a.message,"N/A"), e.eventid
		FROM triggers t
		INNER JOIN functions f ON ( f.triggerid = t.triggerid ) 
		INNER JOIN items i ON ( i.itemid = f.itemid ) 
		INNER JOIN hosts h ON ( i.hostid = h.hostid ) 
		INNER JOIN events e ON ( e.objectid = t.triggerid )
		LEFT JOIN acknowledges a ON ( a.eventid = e.eventid )
		WHERE (e.eventid DIV 100000000000000) 
		IN (0) 
		AND e.object = 0 
		AND (t.value=1 OR (t.value =0 AND unix_timestamp(now()) - t.lastchange <60)) 
		AND h.status = 0 
		AND i.status = 0 
		AND t.status = 0
		AND e.eventid = (SELECT max(eventid) 
				FROM events e
				WHERE (e.eventid DIV 100000000000000) 
				IN (0) 
				AND e.object = 0 
				AND (t.value=1 OR (t.value =0 AND unix_timestamp(now()) - t.lastchange <60)) 
				AND h.status = 0 
				AND i.status = 0 
				AND t.status = 0
				AND e.objectid = t.triggerid
				)
		GROUP BY host, f.triggerid
		ORDER BY t.lastchange DESC;

If the API in 1.8.3 gets more robust, I might be dropping these custom queries all together

[bashman]

This is great, thanks for this info.

I use this query to get the latest unacknowledged events from one host group where the trigger priority is greater than "Average":

SELECT count(e.eventid) 
FROM   triggers t 
       INNER JOIN functions f ON ( f.triggerid = t.triggerid ) 
       INNER JOIN events e ON ( e.objectid = t.triggerid ) 
       INNER JOIN items i ON ( i.itemid = f.itemid ) 
       INNER JOIN hosts h ON ( i.hostid = h.hostid ) 
       INNER JOIN hosts_groups hg ON ( hg.hostid = h.hostid ) 
       INNER JOIN groups g ON ( hg.groupid = g.groupid ) 
WHERE  e.object = 0 
       AND t.value = 1 
       AND h.status = 0 
       AND i.status = 0 
       AND t.status = 0 
       AND t.priority > 2 
       AND e.acknowledged = 0 
       AND t.description <> "Alerts on {HOSTNAME}" 
       AND e.eventid = (SELECT max(eventid) 
                        FROM   events e 
                        WHERE  e.object = 0 
                               AND t.value = 1 
                               AND h.status = 0 
                               AND i.status = 0 
                               AND t.status = 0 
                               AND e.objectid = t.triggerid) 
       AND g.name ="Linux Servers"

[just4fun]

This is great, thanks for this info.

I use this query to get the latest unacknowledged events from one host group where the trigger priority is greater than "Average":

SELECT count(e.eventid) 
FROM   triggers t 
       INNER JOIN functions f ON ( f.triggerid = t.triggerid ) 
       INNER JOIN events e ON ( e.objectid = t.triggerid ) 
       INNER JOIN items i ON ( i.itemid = f.itemid ) 
       INNER JOIN hosts h ON ( i.hostid = h.hostid ) 
       INNER JOIN hosts_groups hg ON ( hg.hostid = h.hostid ) 
       INNER JOIN groups g ON ( hg.groupid = g.groupid ) 
WHERE  e.object = 0 
       AND t.value = 1 
       AND h.status = 0 
       AND i.status = 0 
       AND t.status = 0 
       AND t.priority > 2 
       AND e.acknowledged = 0 
       AND t.description <> "Alerts on {HOSTNAME}" 
       AND e.eventid = (SELECT max(eventid) 
                        FROM   events e 
                        WHERE  e.object = 0 
                               AND t.value = 1 
                               AND h.status = 0 
                               AND i.status = 0 
                               AND t.status = 0 
                               AND e.objectid = t.triggerid) 
       AND g.name ="Linux Servers"

I'm not (and probably never will be) such a SQL-guru and therefore need desperately your help: how to modify this query to get the latest unacknowledged alerts for a given "Application"? Is that possible?

Thanks a lot,
Christian

[bashman]

Hi Christian,

I haven't tryed it, but it could be something like this:

SELECT count(e.eventid) 
FROM   triggers t 
       INNER JOIN functions f ON ( f.triggerid = t.triggerid ) 
       INNER JOIN events e ON ( e.objectid = t.triggerid ) 
       INNER JOIN items i ON ( i.itemid = f.itemid ) 
       INNER JOIN hosts h ON ( i.hostid = h.hostid ) 
       INNER JOIN hosts_groups hg ON ( hg.hostid = h.hostid ) 
       INNER JOIN groups g ON ( hg.groupid = g.groupid ) 
	   INNER JOIN applications a ON ( a.hostid = h.hostid )
WHERE  e.object = 0 
       AND t.value = 1 
       AND h.status = 0 
       AND i.status = 0 
       AND t.status = 0 
       AND t.priority > 2 
       AND e.acknowledged = 0 
       AND t.description <> "Alerts on {HOSTNAME}" 
       AND e.eventid = (SELECT max(eventid) 
                        FROM   events e 
                        WHERE  e.object = 0 
                               AND t.value = 1 
                               AND h.status = 0 
                               AND i.status = 0 
                               AND t.status = 0 
                               AND e.objectid = t.triggerid) 
       AND a.name ="PING"

Write your application name instead of "PING".

[just4fun]

Hi Christian,

I haven't tryed it, but it could be something like this:

SELECT count(e.eventid) 
FROM   triggers t 
       INNER JOIN functions f ON ( f.triggerid = t.triggerid ) 
       INNER JOIN events e ON ( e.objectid = t.triggerid ) 
       INNER JOIN items i ON ( i.itemid = f.itemid ) 
       INNER JOIN hosts h ON ( i.hostid = h.hostid ) 
       INNER JOIN hosts_groups hg ON ( hg.hostid = h.hostid ) 
       INNER JOIN groups g ON ( hg.groupid = g.groupid ) 
	   INNER JOIN applications a ON ( a.hostid = h.hostid )
WHERE  e.object = 0 
       AND t.value = 1 
       AND h.status = 0 
       AND i.status = 0 
       AND t.status = 0 
       AND t.priority > 2 
       AND e.acknowledged = 0 
       AND t.description <> "Alerts on {HOSTNAME}" 
       AND e.eventid = (SELECT max(eventid) 
                        FROM   events e 
                        WHERE  e.object = 0 
                               AND t.value = 1 
                               AND h.status = 0 
                               AND i.status = 0 
                               AND t.status = 0 
                               AND e.objectid = t.triggerid) 
       AND a.name ="PING"

Write your application name instead of "PING".

Sorry for following up so late, was busy with lots of other things...

I've tried this now, but it won't work. It's giving always false-positives.

If, for example, a hostgroup "g1" has applications "a1" and "a2" and application "a1" has a failed item, the above query also returns the same number of items when querying for application "a2"....

[bashman]

Christian I've done some testing and it seems to work for me.

I'll do more testing and I'll give you my testing feedback.

[just4fun]

thanks a lot for your help!!

for clarification I'll attach the result of some tests I did here.

first 2 queries simply count the event-ids (first for application "Filesystem" and the second one for application "OS").
the following 2 queries are like the above but don't count, instead they do a "select * ..."

hope this helps a little...

[rgs1973]

and about last acknowledged

Hi all,

the last query is using first acknowledge inserted in table acknowledges. But if I've two or more values in this table. How can I get the last entry to that specific event id?

Does anybody know?

10ks.

[mrogers-9898]

I've come back to this SQL script again for some modification and since last round I'm a "little" more SQL wise; but one point on the SQL script is perplexing.

WHERE (e.eventid DIV 100000000000000) IN (0)

That line; is it saying: do not want any eventid that exceeds 100000000000000?

I'm curious as to why this line is there at all.

My relatively small Zabbix install has been running for 4 years and only has 4947393 eventIDs.

additionally; if my diagnosiss of that section is correct; how come it is structued

(eventid DIV 100000000000000) IN(0)

vs

eventid < 100000000000000

[fdeco]

Availability for a period

I need get the availability for a host on a period?

Anyone can help? Using Zabbix 2.0.4

[glardz95]

hello,



i have five table in a mariadb database, i want to select last value of each name table,



i do this sql command to have host, name and severity,

i tried this sql command :
select host, e.name, e.severity from hosts left join items i on (i.hostid = hosts.hostid) inner join functions f on (f.itemid = i.itemid) inner join triggers t on (t.triggerid = f.triggerid) INNER JOIN events e ON (e.objectid = t.triggerid) group by hosts.hostid;
i want last host, name and severity, i want this result in sql :