Cleanup Zabbix Problems

You might end up like me, with zabbix problems that don’t want to dissapear, even tho you changed the actual problem. With these MySQL Scripts you can fix that. (Thanks ChatGPT)

First we need to prepare the data.

-- Change these patterns as needed
SET @hostname_pattern = '%web%';
SET @problem_name_pattern = '%CPU%';

-- Step 1: Get matching host IDs
CREATE TEMPORARY TABLE tmp_hostids (
  hostid BIGINT
);

INSERT INTO tmp_hostids (hostid)
SELECT hostid
FROM hosts
WHERE name LIKE @hostname_pattern;

-- Step 2: Get trigger IDs matching name pattern for those hosts
CREATE TEMPORARY TABLE tmp_triggerids (
  triggerid BIGINT
);

INSERT INTO tmp_triggerids (triggerid)
SELECT DISTINCT t.triggerid
FROM triggers t
JOIN functions f ON t.triggerid = f.triggerid
JOIN items i ON f.itemid = i.itemid
WHERE i.hostid IN (SELECT hostid FROM tmp_hostids)
  AND t.description LIKE @problem_name_pattern;

-- Step 3: Get active problem event IDs
CREATE TEMPORARY TABLE tmp_eventids (
  eventid BIGINT
);

INSERT INTO tmp_eventids (eventid)
SELECT p.eventid
FROM problem p
JOIN events e ON p.eventid = e.eventid
WHERE e.object = 0 AND e.source = 0
  AND e.objectid IN (SELECT triggerid FROM tmp_triggerids);

Now we wanna check what will be deleted.

SELECT * FROM problem WHERE eventid IN (SELECT eventid FROM tmp_eventids);

If this looks good, let’s delete them.

DELETE FROM problem WHERE eventid IN (SELECT eventid FROM tmp_eventids);
DELETE FROM events WHERE eventid IN (SELECT eventid FROM tmp_eventids);

To finish, we gonna cleanup what we’ve created.

DROP TEMPORARY TABLE IF EXISTS tmp_hostids;
DROP TEMPORARY TABLE IF EXISTS tmp_triggerids;
DROP TEMPORARY TABLE IF EXISTS tmp_eventids;