[Dev help] DB query - return OK and NOK Triggers

[zabbix_zen]

Hi.

I've been asked to prepare an HTML Red/green like report for scheduled interventions.

I created a bunch of OnDemand collected zabbix_sender items and thought about using the data already stored in Zabbix for that, problem is,
I'm not being able to get both the OK and NOT triggers.descriptions
(The HostGroup, Hosts, items, triggers were specifically created for that end, the 'i.lastclock >=' part is to guarantee the selected items were refreshed since the OnDemand collection start)

SELECT DISTINCT t.triggerid,t.status,t.description, i.lastclock,t.value,h.host,h.hostid
FROM triggers t,hosts h,items i,functions f, hosts_groups hg
WHERE f.itemid=i.itemid
AND h.hostid=i.hostid
AND hg.hostid=h.hostid
AND t.triggerid=f.triggerid
AND h.status=0
AND t.status IN ('PROBLEM', 'OK')
AND i.lastclock >= UNIX_TIMESTAMP(NOW() - INTERVAL 9 MINUTE)
AND h.hostid IN (select hostid from hosts_groups where groupid = 21
ORDER BY h.host, i.lastclock DESC


Do I really need to confront against history.clock instead of items.lastclock? That table is way bigger...
Can anyone correct my query ?

[zabbix_zen]

Anyone?

Should be a variation of the function used in Trigger's '"Name" in
Configuration | Hosts | Triggers

But how can I do it ..?

[Aly]

SELECT DISTINCT t.triggerid,t.status,t.description, i.lastclock,t.value,h.host,h.hostid
FROM triggers t,hosts h,items i,functions f, hosts_groups hg
WHERE f.itemid=i.itemid
AND h.hostid=i.hostid
AND hg.hostid=h.hostid
AND t.triggerid=f.triggerid
AND h.status=0
AND t.status IN ('PROBLEM', 'OK')
AND i.lastclock >= UNIX_TIMESTAMP(NOW() - INTERVAL 9 MINUTE)
AND h.hostid IN (select hostid from hosts_groups where groupid = 21
ORDER BY h.host, i.lastclock DESC

This is incorrect SQL query, here is proper one:

SELECT DISTINCT t.triggerid,t.status,t.description, i.lastclock,t.value,h.host,h.hostid
FROM triggers t,hosts h,items i,functions f, hosts_groups hg
WHERE f.itemid=i.itemid
AND h.hostid=i.hostid
AND hg.hostid=h.hostid
AND t.triggerid=f.triggerid
AND h.status=0
AND t.value IN (0, 1)
AND t.status=0
AND i.lastclock >= UNIX_TIMESTAMP(NOW() - INTERVAL 9 MINUTE)
AND h.hostid IN (select hostid from hosts_groups where groupid = 21
ORDER BY h.host, i.lastclock DESC

[zabbix_zen]

Thanks Aly,
you're a saint.

I'd kiss you if you were a girl !
:)