#! /usr/bin/env perl

# Copyright (c) 2024, Daily Data, Inc
#
# Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following
# conditions are met:
#
#  Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
#  Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following
# disclaimer in the documentation and/or other materials provided with the distribution.
#
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
# BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT
# SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
# INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
# OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.[12]
#
# Description
# Script to clean up security log tables on WordPress sites
# Some wordpress site security suites continously add logs, but do not offer the ability to clean up entries after
# a certain period of time. This can cause database tables to grow without relief until they threaten to fill
# the database partition
# This came to light recently when the half dozen WordPress sites we manage threatened to fill the database partition
# after a few years of monitoring.
#
# Before we ran this script, the 6G partition was 81% full, and after cleaning up, that dropped to 52%, so almost a
# third of the database size was years of logs, or almost 2G. Of course, you can increase the disk space available on
# the partition, but we just wanted to keep 4 weeks of logs
#
# Designed to be run from a monthly or weekly cron job, it will look for all MySQL databases, then check each one in turn
# for the existence of the security log tables listed in @TABLES. Whichever of those tables actually exist in a given
# database will have anything over $KEEPDAYS days old removed (or be truncated entirely, see --truncate below).
#
# this is a quick script and assumes the root user has access to all MySQL functions without a password (eg via a
# root .my.cnf or unix socket auth). Modifications will need to be made on systems which are more secure.
#
# Usage:
#   cleanWPSecLogs [--keepdays=N | --truncate] [--dry-run] [--quiet]
#
#   --keepdays=N   keep N days of logs (default 28). Mutually exclusive with --truncate.
#   --truncate     empty the log tables completely instead of aging out by date. Mutually exclusive with --keepdays.
#   --dry-run      show what would be done (which databases/tables match, what SQL would run) without changing anything.
#   --quiet        suppress all non-error output.
#
# Example /etc/cron.d/ entry (note the extra "root" user field vs. a plain crontab -e), run Saturday at 1am:
#   0 1 * * 6 root /opt/sysadmin_scripts/Wordpress/cleanWPSecLogs --quiet
#
# This script is available via subversion at
# svn co http://svn.dailydata.net/svn/sysadmin_scripts/trunk/Wordpress/cleanWPSecLogs
# NOTE: the above repository is a working copy from Daily Data, which can be modified as required without notification


use strict;
use warnings;
use Getopt::Long;

my $KEEPDAYS = 28;
my $truncate = 0;
my $dryRun   = 0;
my $quiet    = 0;
my $keepdaysSet = 0;

# tables to check for/clean in each database, and the column that holds each row's age
my @TABLES = (
   { name => 'wp_itsec_logs',     column => 'timestamp' },
   { name => 'wp_itsec_lockouts', column => 'lockout_expire' },
   { name => 'wp_itsec_temp',     column => 'temp_date' },
);

GetOptions(
   'keepdays=i' => sub { $KEEPDAYS = $_[1]; $keepdaysSet = 1; },
   'truncate'   => \$truncate,
   'dry-run'    => \$dryRun,
   'quiet'      => \$quiet,
   'help'       => sub { usage(); exit 0; },
) or usage() and exit 1;

if ( $truncate && $keepdaysSet ) {
   die "Error: --truncate and --keepdays are mutually exclusive\n";
}

# make sure the mysql/mysqlshow clients are actually available before we try to use them -- if run from cron
# with a stripped-down PATH (or on a box that never had them installed), fail loudly instead of silently
# doing nothing
for my $bin ( qw( mysql mysqlshow ) ) {
   chomp( my $path = `which $bin` );
   die "Error: $bin client not found in PATH\n" unless $path;
}

sub usage {
   print "Usage: $0 [--keepdays=N | --truncate] [--dry-run] [--quiet]\n";
   print "  --keepdays=N   keep N days of logs (default $KEEPDAYS). Mutually exclusive with --truncate.\n";
   print "  --truncate     empty the log tables completely. Mutually exclusive with --keepdays.\n";
   print "  --dry-run      show what would be done without changing anything.\n";
   print "  --quiet        suppress all non-error output.\n";
}

# clean up the output from mysqlshow
# if the name does not match the regular expression
# return null, else return the matched code
sub clean {
   my $name = shift;
   # regex matches any database beginning with c## for our ISPConfig setup
   # pretty sloppy regex, but works on our system
   # NOTE: the ternary must key off the match's success, not $1 -- a failed match leaves $1 holding
   # whatever it matched last time, silently reusing the previous database name for non-matching lines
   return $name =~ m/(c\d+[a-z0-9_]+)/i ? $1 : '';
}

# returns true if $table exists in database $db
sub tableExists {
   my ( $db, $table ) = @_;
   return `mysql $db -e "show tables like '$table';"`;
}

# builds the list of SQL statements to run against a database, given which of @TABLES exist there
sub buildSQL {
   my @existing = @_;
   my @statements;
   for my $t ( @existing ) {
      if ( $truncate ) {
         push @statements, "TRUNCATE TABLE $t->{name};";
      } else {
         # delete anything over $KEEPDAYS old. timestamps appear to be UT, so will be off by some
         push @statements, "DELETE FROM $t->{name} WHERE $t->{column} < DATE_SUB(NOW(), INTERVAL $KEEPDAYS DAY);";
      }
   }
   # recovers disk space from tables. Not needed after a truncate, which already reclaims the space
   push @statements, "OPTIMIZE TABLE " . join( ', ', map { $_->{name} } @existing ) . ";" unless $truncate;
   return @statements;
}

# simple way to get list of all databases, but they need to be cleaned up afterwards
my @databases = `mysqlshow`;
for ( my $i = 0; $i < @databases; $i++ ) {
   $databases[$i] = clean( $databases[$i] );
}
#die join( "\n", @databases ) . "\n";

# process each database
for my $db ( @databases ) {
   next unless $db; # skip anything that clean() zapped

   my @existing = grep { tableExists( $db, $_->{name} ) } @TABLES;
   next unless @existing; # none of our tables are in this database

   my @statements = buildSQL( @existing );
   my $sql = join( "\n", @statements );

   if ( $dryRun ) {
      print "-- dry run: $db has " . join( ', ', map { $_->{name} } @existing ) . " --\n$sql\n" unless $quiet;
   } else {
      print "Found in $db: " . join( ', ', map { $_->{name} } @existing ) . "\n" unless $quiet;
      my $output = `mysql $db -e "$sql"`;
      print $output unless $quiet;
   }
}
1;