SQL script to reset Triggers automatically

[mgibson]

I'm not sure if something like this has been posted before, but I was looking for a way to automatically clear triggers (specifically Window Event logs - but this will work for anything) after they reached a certain age.

Change the '0:30:00' to whatever age you want Triggers reset after, and change the "%event log%" to filter on other trigger names.

NOTE: This script creates a scheduled event in MySQL that will run every 10 minutes. Get rid of everything before and including BEGIN, and after and including END if you want to use it as a standalone script.

use zabbix;
delimiter |
CREATE EVENT `Zabbix_EventLog_Trigger_Reset` 
ON SCHEDULE EVERY 10 MINUTE 
DO 
BEGIN
	UPDATE `hosts`
	INNER JOIN `items` ON `hosts`.hostid = `items`.hostid
	INNER JOIN `functions` ON `items`.itemid = `functions`.itemid
	INNER JOIN `triggers` ON `functions`.triggerid = `triggers`.triggerid SET `triggers`.value = 0
	WHERE `items`.name LIKE "%event log%" AND `items`.templateid IS NOT NULL AND triggers.value = 1 AND (TIMEDIFF(NOW(), FROM_UNIXTIME(`items`.lastclock)) > CAST('0:30:00' AS TIME) OR `items`.lastclock IS NULL); 
END |
delimiter ;