HOWTO: Zabbix 2.4 - Postgres SQL to reset Trigger's state

[pablos]

Hi,

I wanted to monitor my syslog's for login success and failures. I wanted one alert per attempt. I also wanted the data to clear. Using .nodata() resulted in duplicate email messages[1] - seems to be "by design."

I removed .nodata() from my Trigger definition but the issues remained on the Monitoring > Dashboard. I found some partially working SQL[2] (for MySQL) which I transformed to work with Zabbix 2.4 and PostgreSQL:

--
-- Flip a "trigger"'s "value" to 0 so it no longer appears
-- on the Monitoring > Dashboard as an issue.
--
-- The SQL below uses two filters:
--
-- o  applications.name = 'syslog'
-- o  Any item that is older than 60 (+ 60) seconds
--
UPDATE triggers
SET    value = 0
FROM   hosts
INNER  JOIN applications
   ON  hosts.hostid = applications.hostid
INNER  JOIN items
   ON  hosts.hostid = items.hostid
INNER  JOIN items_applications
   ON  items.itemid               = items_applications.itemid
   AND applications.applicationid = items_applications.applicationid
INNER  JOIN functions
   ON  items.itemid               = functions.itemid 
INNER JOIN triggers t2
   ON  functions.triggerid = t2.triggerid
WHERE  items.templateid   IS NOT NULL 
AND    applications.name   = 'syslog'
AND    triggers.value      = 1
AND    triggers.triggerid  = t2.triggerid
AND    items.mtime        <= extract(epoch from now()) + 60;

References
[1] - https://support.zabbix.com/browse/ZBX-8114
[2] - https://www.zabbix.com/forum/showthread.php?t=38398