Remove Acknowledges from Zabbix v4

zabbixv4_remove_acks.sql

-- 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;