Drupal 8 - Selective Deletion from Watchdog Table

Published: 5/15/2019

I support a Drupal 8 implementation that has specific logging needs. Much of the site functionality comes from custom modules, and various actions taken by these modules must be logged. I accomplished this by using Drupal's dblog feature to write custom messages to the watchdog table. This is great, because I have control over the message that is written, and the logs are available within the UI for anyone who needs them. But due to the requirements of the site, these custom messages in watchdog must be kept for a long time, potentially for the life of the site. In order to keep watchdog from becoming too large, certain messages need to be removed on a regular basis without removing the important messages.

Out of the box, dblog only gives you a predefined set of options for the number of messages to keep in the database log. Even though one of those options is 1,000,000, that still doesn't fit my use case. I'm stuck with setting the option to 'All.'

Drush offers some methods for selectively removing certain entries from watchdog, but the options are not specific enough for my needs.

For this site, the best way to tackle a rapidly-growing WATCHDOG table was with SQL.

I went through each of the log types and messages and determined what could be removed through SQL DELETE FROM statements. Below are the statements and the justification I use to prune watchdog without removing the messages I need. Once my DELETE statements were written, I put them in a text file on my database server. I then wrote a shell script to connect to MySQL and execute the statements from the text file. Finally, I schedule the shell script to run through cron once per week.

Although watchdog will continue to grow, this method allows me to keep it manageable without sacrificing my logging needs.

// In my site, 403 and 404 errors are very infrequent. I review them in the admin/reports about once per week, and then they are removed.
delete from watchdog where type='page not found';
delete from watchdog where type='access denied';

// Only my admin account creates and updates blocks, menus, nodes, views, and taxonomy. With webforms, I already have the submission // data available in another table and in the UI, so I don't need to keep the log entries from these events
delete from watchdog where type='block';
delete from watchdog where type='block_content';
delete from watchdog where type='menu';
delete from watchdog where type='node';
delete from watchdog where type='taxonomy';
delete from watchdog where type='views';
delete from watchdog where type='webform';

// Cron runs every minute, and it writes an entry to watchdog every time. Three weeks of cron runs added over 34,000 entries to my
// watchdog table, and I don't need any of them long-term.
delete from watchdog where type='cron';

// Ultimate cron does some routine maintenance, but the information it writes to watchdog is not critical for my purposes.
delete from watchdog where type='ultimate_cron_lock';

// Addtional cron maintenance information that I don't need to keep.
delete from watchdog where type='database_logger';

// I have a custom cron job that runs on a regular basis and writes an entry to watchdog each time. Aside from having to troubleshoot the
// most recent run of this job, the entries are not needed.
delete from watchdog where message like 'The cron job for Timeoff Leave Type has started%';

// LDAP does some checks against our active directory, but only for authentication. Possibly, adjusting the LDAP module settings would
// eliminate these entries.
delete from watchdog where type like 'ldap%';

// In the event that I need to troubleshoot a sent message, I keep the entries from the Contact module for a while. However, these
// messages are emailed and exist in other places if advanced research is necessary. I keep 30 days worth of them.
delete from watchdog where type='contact' and from_unixtime(`timestamp`) < date_sub(now(), interval 30 day);

// Feeds were used in the early stages of the site during a time when data migration was happening. Feeds are still occasionally used, but I // don't need logs long-term. I keep 30 days worth.
delete from watchdog where type like 'feeds%' and from_unixtime(`timestamp`) < date_sub(now(), interval 30 day);

// The entries for type 'form' may be useful in some cases, but I have not found any use for them in this particular site
delete from watchdog where type='form';

// The entries for type 'php' can be important for troubleshooting; however, a few of the contributed modules I'm using write an abundance // of php warnings and notices to the watchdog table. In this case, I keep anything with a severity level of an error or worse (0-3) for 6
// months and remove everything else after 1 week.
delete from watchdog where type='php' and severity > 3 and from_unixtime(`timestamp`) < date_sub(now(), interval 7 day);
delete from watchdog where type='php' and severity < 4 and from_unixtime(`timestamp`) < date_sub(now(), interval 6 month);

// For my site, the only entries of type 'system' are related to module installs and uninstalls. I don't keep any of these logs.
delete from watchdog where type='system';

// I keep the type 'user' entries primarily for information about failed login attempts, but I only keep 30 days worth.
delete from watchdog where type='user' and from_unixtime(`timestamp`) < date_sub(now(), interval 30 day);