Useful Zabbix SQL queries
Switch branches/tags
Nothing to show
Clone or download
Pull request Compare This branch is 43 commits ahead, 28 commits behind mattiasgeniar:master.
burner1024 Merge pull request #3 from murtzsarialtun/master
add orphaned data delete and check for zabbix v3
Latest commit 833da99 Jun 7, 2018
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
README.md add note about bloating Apr 18, 2017
check-lld-triggers.my.sql add LLD triggers removal Aug 3, 2015
check-lld-triggers.pg.sql add LLD triggers removal Aug 3, 2015
check-old-data.my.sql add old data scripts for mysql; uppercase SQL words in other files Apr 28, 2014
check-old-data.pg.sql add old data scripts for mysql; uppercase SQL words in other files Apr 28, 2014
check-orphaned-data.zbx18.sql better support for Zabbix 2.x. Better output for postgres queries Apr 9, 2015
check-orphaned-data.zbx2x.sql better support for Zabbix 2.x. Better output for postgres queries Apr 9, 2015
check-orphaned-data.zbx3.sql add orphaned data delete and check for zabbix v3 Jun 6, 2018
check-unused-data.sql fix typo in unused data select Apr 29, 2014
delete-lld-triggers.my.sql add LLD triggers removal Aug 3, 2015
delete-lld-triggers.pg.sql add LLD triggers removal Aug 3, 2015
delete-old-data.my.sql fix one more typo in delete Apr 29, 2014
delete-old-data.pg.sql add check-old-data script, add parameters to delete-old-data Apr 28, 2014
delete-orphaned-data.zbx18.sql better support for Zabbix 2.x. Better output for postgres queries Apr 9, 2015
delete-orphaned-data.zbx2x.sql better support for Zabbix 2.x. Better output for postgres queries Apr 9, 2015
delete-orphaned-data.zbx3.sql add orphaned data delete and check for zabbix v3 Jun 6, 2018
delete-unused-data.sql add old data scripts for mysql; uppercase SQL words in other files Apr 28, 2014
stop-and-delete-email-alerts.sql add old data scripts for mysql; uppercase SQL words in other files Apr 28, 2014
stop-email-alerts.sql add old data scripts for mysql; uppercase SQL words in other files Apr 28, 2014

README.md

Zabbix SQL repo

This repo contains a few useful queries for Zabbix database, mostly to cleanup old and/or orphaned data.

Important notes

  • If you have a large database please note that these can take a while (a few hours is normal).
  • Use the queries on your own risk. Take backups first. The queries were (mostly) tested against Zabbix 1.8-3.0.
  • Some scripts are Mysql or Postgresql specific, they're named *.my.sql and *.pg.sql, respectively. Some are also Zabbix version specific. Filenames are self-explaining.
  • Patches are welcome.

Usage

Orphaned data

Orphaned data is history which belongs to deleted hosts and similar.

mysql zabbix < check-orphaned-data.sql
psql -A -R ' : ' -P 'footer=off' zabbix < check-orphaned-data.zbx2x.sql

mysql zabbix < delete-orphaned-data.sql
psql -A -R ' : ' -P 'footer=off' zabbix < delete-orphaned-data.zbx2x.sql

Old data

This set of queries allows you to delete all data older than a specified period. Default is 1 week for history, 3 months for trends - edit sql at your own discretion.

mysql zabbix < check-old-data.my.sql
psql -A -R ' : ' -P 'footer=off' zabbix < check-old-data.pg.sql

mysql zabbix < delete-old-data.my.sql
psql -A -R ' : ' -P 'footer=off' zabbix < delete-old-data.pg.sql

NOTE: due to the way the databases work (Mysql in particular), running these scripts won't reduce Zabbix db size if it's already bloated. You will have to dump and reload the db after that. What the scripts do is keep its size more or less constant if you run them regularly.

Unused data

This deletes all history for disabled items. May come in handy when you disable a significant number of items and no longer need the collected data.

mysql zabbix < check-unused-data.sql
psql -A -R ' : '  -P 'footer=off' zabbix < check-unused-data.sql

mysql zabbix < delete-unused-data.sql
psql -A -R ' : '  -P 'footer=off' zabbix < delete-unused-data.sql

Stop email flood

(Use stop-and-delete-email-alerts.sql if you're not interested in alert history)

sudo service zabbix-server stop
psql zabbix < stop-email-alerts.sql
sudo service zabbix-server start

LLD triggers

Zabbix will create automatically create "Mounted Filesystem Discovery" triggers, which you can't disable or delete from the web interface. These queries will allow you to delete them all at once (disk space and inodes). Inspired by.

mysql zabbix < check-lld-triggers.my.sql
psql -A -R ' : '  -P 'footer=off' zabbix < check-lld-triggers.pg.sql

mysql zabbix < delete-lld-triggers.my.sql
psql -A -R ' : '  -P 'footer=off' zabbix < delete-lld-triggers.pg.sql