Add Retention Policies To Your Openfire Message Archive

I came across a challenge recently to limit the Openfire message archive to keep records for a certain period of time. Unfortunately, the default options for the Monitoring plugin (http://www.igniterealtime.org/projects/openfire/plugins/monitoring/readme.html) do not allow for any such customization. So, it was off to the MySQL database for some hands-on trimming. To better automate the process I created a Perl script to do the work for me.

ofArchiveRetention.pl

#!/usr/bin/perl

use warnings;
use DBI;

# MySQL Configuration
$host = "localhost";
$db = "openfire";
$user = "root";
$pass = "";

# Enter retention period here (in days)
$retentionPeriod = 14;

# ================================ #
# === DO NOT MODIFY BELOW HERE === #
# ================================ #

# Set Date Variables
$currentDate = time();
$modCurrentDate = $currentDate * 1000;
$modRetentionPeriod = $retentionPeriod * 86400000;
$retentionDate = $modCurrentDate - $modRetentionPeriod;

# Connect to the database
$dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass)
or die "Connection Error: $DBI::errstr\n";

# DEFINE A MySQL QUERY
$messages = "DELETE FROM ofMessageArchive WHERE sentDate <=$retentionDate";
$participants = "DELETE FROM ofConParticipant WHERE joinedDate <=$retentionDate";
$records = "DELETE FROM ofConversation WHERE startDate <=$retentionDate";

# Prepare database queries
$msgs = $dbh->prepare($messages);
$part = $dbh->prepare($participants);
$rcds = $dbh->prepare($records);

# Run database queries
$msgs->execute
or die "SQL Error: $DBI::errstr\n";
$part->execute
or die "SQL Error: $DBI::errstr\n";
$rcds->execute
or die "SQL Error: $DBI::errstr\n";

# Disonnect from the database
$dbh->disconnect();
exit(0);

Set this to run daily in cron:

0 1 * * * perl /root/ofArchiveRetention.pl