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
Advertisements
  1. #1 by Gerelt on February 11, 2010 - 2:00 am

    Thanks man.

  2. #2 by Ruben Dario on July 22, 2010 - 3:33 pm

    Muchas gracias por su articulo es muy interesante, ya lo probé y funciono correctamente, solo me queda que el tamaño del indice sigue siendo muy grande aunque no tiene ningún registro guardado en la base de datos:

  3. #3 by Elies Pavlou on March 11, 2012 - 8:35 am

    This worked like a charm. Is it possible to guide me in order to delete a user’s messages only?

    Regards,

    Elies

  4. #4 by RJD on April 5, 2012 - 3:15 pm

    Can this be done for Windows-based installs of OpenFire as well?

    • #5 by Justin on April 5, 2012 - 5:20 pm

      I don’t see why not — as long as you can execute Perl and are running MySQL on the backend.

  5. #6 by toni on April 21, 2012 - 1:46 am

    Hi,

    wow finally I found the best script for my problem. But got one question about the date range.

    With your example or with the dates you set how many days is that?

    Thanks

  6. #7 by SaBiNo on March 14, 2013 - 8:56 pm

    galera fiz uma meleca aqui e dropei a tabela ofmessagearchive
    alguem poderia me passar a estrutura dessa tabela para eu recria-la?

  7. #8 by Devan on March 28, 2013 - 3:06 am

    wow, it works..i just set the root pass and also change the days. Don’t forget to rebuild the index guys. 🙂

  8. #9 by ClaudioVT on July 18, 2013 - 2:24 am

    Worked just fine! Thanks a lot!

  1. Learn to use Search engine optimization Wp Plugin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: