Cleanup Telemetrics Zabbix

Simple Architecture Zabbix

Create script zabbix-cleanup

#!/bin/bash

systemctl stop zabbix-server

PASS=$(cat /etc/zabbix/zabbix_server.conf  | grep "^DBPassword"  | awk -F"=" '{print $2}')
mysql -uroot -p${PASS} zabbix -e "SET FOREIGN_KEY_CHECKS = 0;"
mysql -uroot -p${PASS} zabbix -e "truncate alerts;"
mysql -uroot -p${PASS} zabbix -e "truncate acknowledges;"
mysql -uroot -p${PASS} zabbix -e "truncate events;"
mysql -uroot -p${PASS} zabbix -e "truncate table history;"
mysql -uroot -p${PASS} zabbix -e "truncate table history_log;"
mysql -uroot -p${PASS} zabbix -e "truncate table history_str;"
mysql -uroot -p${PASS} zabbix -e "truncate table history_uint;"
mysql -uroot -p${PASS} zabbix -e "truncate table history_text;"
mysql -uroot -p${PASS} zabbix -e "truncate table trends;"
mysql -uroot -p${PASS} zabbix -e "truncate table trends_uint;"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM alerts WHERE NOT EXISTS (SELECT 1 FROM actions WHERE alerts.actionid = actions.actionid);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM alerts WHERE NOT EXISTS (SELECT 1 FROM events WHERE alerts.eventid = events.eventid);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM alerts WHERE NOT userid IN (SELECT userid FROM users);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM alerts WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM applications WHERE NOT hostid IN (SELECT hostid FROM hosts);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM auditlog_details WHERE NOT auditid IN (SELECT auditid FROM auditlog);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM auditlog WHERE NOT userid IN (SELECT userid FROM users);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM conditions WHERE NOT actionid IN (SELECT actionid FROM actions);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM functions WHERE NOT itemid IN (SELECT itemid FROM items);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM functions WHERE NOT triggerid IN (SELECT triggerid FROM triggers);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM graphs_items WHERE NOT graphid IN (SELECT graphid FROM graphs);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM graphs_items WHERE NOT itemid IN (SELECT itemid FROM items);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM hostmacro WHERE NOT hostid IN (SELECT hostid FROM hosts);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM items WHERE hostid NOT IN (SELECT hostid FROM hosts);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM items_applications WHERE applicationid NOT IN (SELECT applicationid FROM applications);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM items_applications WHERE itemid NOT IN (SELECT itemid FROM items);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM httpstep WHERE NOT httptestid IN (SELECT httptestid FROM httptest);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM httpstepitem WHERE NOT httpstepid IN (SELECT httpstepid FROM httpstep);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM httpstepitem WHERE NOT itemid IN (SELECT itemid FROM items);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM httptest WHERE applicationid NOT IN (SELECT applicationid FROM applications);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM maintenances_groups WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM maintenances_groups WHERE groupid NOT IN (SELECT groupid FROM groups);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM maintenances_hosts WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM maintenances_hosts WHERE hostid NOT IN (SELECT hostid FROM hosts);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM maintenances_windows WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM maintenances_windows WHERE timeperiodid NOT IN (SELECT timeperiodid FROM timeperiods);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM mappings WHERE NOT valuemapid IN (SELECT valuemapid FROM valuemaps);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM media WHERE NOT userid IN (SELECT userid FROM users);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM media WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM rights WHERE NOT groupid IN (SELECT usrgrpid FROM usrgrp);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM rights WHERE NOT id IN (SELECT groupid FROM groups);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM sessions WHERE NOT userid IN (SELECT userid FROM users);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM screens_items WHERE screenid NOT IN (SELECT screenid FROM screens);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM trigger_depends WHERE triggerid_down NOT IN (SELECT triggerid FROM triggers);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM trigger_depends WHERE triggerid_up NOT IN (SELECT triggerid FROM triggers);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM history WHERE NOT EXISTS (SELECT 1 FROM items WHERE history.itemid = items.itemid);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM history_uint WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_uint.itemid = items.itemid);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM history_log WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_log.itemid = items.itemid);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM history_str WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_str.itemid = items.itemid);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM history_text WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_text.itemid = items.itemid);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM trends WHERE NOT EXISTS (SELECT 1 FROM items WHERE trends.itemid = items.itemid);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM trends_uint WHERE NOT EXISTS (SELECT 1 FROM items WHERE trends_uint.itemid = items.itemid);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM events WHERE source = 0 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM events WHERE source = 3 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM events WHERE source = 3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM acknowledges WHERE NOT eventid IN (SELECT eventid FROM events);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM acknowledges WHERE NOT userid IN (SELECT userid FROM users);"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM acknowledges WHERE eventid IN (SELECT eventid FROM events WHERE (source = 0 OR source=3) AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers));"
mysql -uroot -p${PASS} zabbix -e "DELETE FROM acknowledges WHERE eventid IN (SELECT eventid FROM events WHERE source=3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items));"
mysql -uroot -p${PASS} zabbix -e "SET FOREIGN_KEY_CHECKS = 0;"
systemctl start zabbix-server

Execute

bash zabbix-cleanup