Nagios, 2nd Edition

23.5 Maintenance

The MySQL database can, under some circumstances—depending on the number of connected systems and events passed on by the syslog—fill up very quickly. Then it is time to clean up. To do this you need to find all the entries that are older than a certain date, test whether an acknowledge exists for them, and delete them. The following SELECT statement demonstrates the principle:

user@linux:~$ mysql -u eventdb -p eventdb
mysql> SELECT * FROM events WHERE datetime < '2007-11-16'
    -> AND acknowledged;

The date is stored by MySQL in the format YYYY-mm-dd HH:MM:SS, which is why a simple string comparison works. If an admin has confirmed an entry, the acknowledged field will contain the value 1. The following simple cleanup script deletes ...