Problem with orphaned or broken data in Zabbix

[mvanthiel]

Hey Folks,

Since a few days, I'm trying to remove some orphaned or broken data in Zabbix. I added a Template about Systemd Service Discovery on Linux.
I then added these template to a few hosts, and it's working. But then I realized that one host where added with the wrong IP, so I changed the IP in the config of the host, and deleted the discovered items. But a few of these items already alerted a Problem.
The Problem is now staying open.

I even deleted the whole host, removed the template from the host etc. but the Problems still stay open, with no Host...
Then I tried a little more "hardcore" methods, and tried to delete the data directly from the database. I found a few scripts on git who were promising.

https://github.com/burner1024/zabbix-sql

https://github.com/burner1024/zabbix-sql/blob/master/delete-lld-triggers.my.sql

https://github.com/burner1024/zabbix-sql/blob/master/delete-old-data.my.sql

https://github.com/burner1024/zabbix-sql/blob/master/delete-orphaned-data.zbx3.sql

https://github.com/burner1024/zabbix-sql/blob/master/delete-unused-data.sql

https://github.com/burner1024/zabbix-sql/blob/master/stop-and-delete-email-alerts.sql

But after these scripts ran, All of the Problems, items etc were gone except these ones. So the scripts deleted all the data that weren't orphaned at all.

Now I have a backup from before these scripts run, so I can get back the deleted data at least. But I still need to get rid of these "Ghost" Problems.

Does anyone have an Idea or Solution to these?

[mvanthiel]

I had still no luck in deleting these entries. Does nobody know how to delete these data?
These entries suck, I want to see actual problems, not ghosts.
edit: I'm using the current Zabbix version 4.0 and MySQL Database with mysql partition configured like this: https://zabbix.org/wiki/Docs/howto/mysql_partition
despite the github commands and the database partitioning cleanup and maintenance scripts I also tried to explicitly delete the entries with a command like this:

UPDATE triggers SET value=0, error='' WHERE error LIKE '%Zabbix was restarted%';

But this doesn't help either. A Select statement doesn't even pick up the triggers in the first screenshot.

MariaDB [zabbix]> select * from triggers where error LIKE '%wuauserv%';
Empty set (0.009 sec)

See the first entry of the screenshot.
So any further suggestions or anything?

Here is a Forum Post that maybe displays the same Problem and is still unsolved too: https://www.zabbix.com/forum/zabbix-...triggers-stuck

[mvanthiel]

I finally was able to delete the Problems out of the database.
Here is how:
I searched for the correct table where the problems are stored. then I selected a time (clock) between the first and the last problem I need to delete, and defined the first word of all problems so no other data in these time will be deleted, then I changed the severity of all "to delete" data in Zabbix to "not classified" in DB it will be severity= 0 and in the end I sorted the output by clock descending.
After I got the exact output of that 11 entries that I needed to delete, I just deleted this 11 entries by eventid.

SELECT * FROM problem WHERE clock BETWEEN 1548930000 AND 1548960000 AND NAME LIKE "%Service%" and severity = 0 order BY clock desc;
/* Betroffene Zeilen: 0  Gefundene Zeilen: 11  Warnungen: 0  Dauer von 1 Abfrage: 0,016 Sek. */
SHOW CREATE TABLE `zabbix`.`problem`;
DELETE FROM `zabbix`.`problem` WHERE  `eventid`=317709;
DELETE FROM `zabbix`.`problem` WHERE  `eventid`=317708;
DELETE FROM `zabbix`.`problem` WHERE  `eventid`=317707;
DELETE FROM `zabbix`.`problem` WHERE  `eventid`=317639;
DELETE FROM `zabbix`.`problem` WHERE  `eventid`=317638;
DELETE FROM `zabbix`.`problem` WHERE  `eventid`=317637;
DELETE FROM `zabbix`.`problem` WHERE  `eventid`=317636;
DELETE FROM `zabbix`.`problem` WHERE  `eventid`=317635;
DELETE FROM `zabbix`.`problem` WHERE  `eventid`=317634;
DELETE FROM `zabbix`.`problem` WHERE  `eventid`=317633;
DELETE FROM `zabbix`.`problem` WHERE  `eventid`=317388;