Skip to content

Instantly share code, notes, and snippets.

@hluaces
Created June 11, 2019 11:35
Show Gist options
  • Save hluaces/2da216848320b9d8b9fedb63bf821e53 to your computer and use it in GitHub Desktop.
Save hluaces/2da216848320b9d8b9fedb63bf821e53 to your computer and use it in GitHub Desktop.
Remove Acknowledges from Zabbix v4
-- Héctor Luaces Novo
-- SQL query to remove ACKs from Zabbix events.
-- It is name-based but it can be easily tweaked to filter in some other ways.
-- Please note that this might cause damage to your database. Be sure to have a backup.
-- Usage:
-- Copy this in an editor, change the NAME_PATTERN to switch your needs.
-- If everything ran successfully, commit the transaction. Otherwise, do a rollback
-- The pattern for your mail triggers
SET @NAME_PATTERN = 'Mail queue%';
-- Extra measure to prevent accidental damage
SET autocommit = 0;
START TRANSACTION;
-- UN-ACK the problem
UPDATE zabbix.problem
SET acknowledged = 0
WHERE 1=1
AND name LIKE @NAME_PATTERN
;
-- Un-ACK the associated event
UPDATE zabbix.events
SET acknowledged = 0
WHERE 1=1
AND eventid IN (
SELECT eventid
FROM zabbix.problem
WHERE name LIKE @NAME_PATTERN
);
-- Delete the ACK itself
DELETE FROM zabbix.acknowledges
where 1=1
AND eventid IN (
SELECT eventid
FROM zabbix.problem
WHERE name LIKE @NAME_PATTERN
);
-- This is just a query to check that everything is OK
SELECT zp.eventid,za.acknowledgeid,za.userid,zp.name,zp.acknowledged,ze.acknowledged,
za.message
FROM zabbix.problem zp
LEFT JOIN zabbix.events ze ON ze.eventid = zp.eventid
LEFT JOIN zabbix.acknowledges za ON zp.eventid = za.eventid
WHERE 1=1
AND zp.name LIKE @NAME_PATTERN
ORDER BY zp.acknowledged
;
-- If everything goes OK execute this:
-- commit;
-- If shit hit the fan execute this:
-- rollback;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment