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 ( 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.


use warnings;
use DBI;

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

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

# ================================ #
# ================================ #

# 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";

$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
or die "SQL Error: $DBI::errstr\n";
or die "SQL Error: $DBI::errstr\n";
or die "SQL Error: $DBI::errstr\n";

# Disonnect from the database

Set this to run daily in cron:

0 1 * * * perl /root/
  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?



  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


    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?


  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: Logo

You are commenting using your 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: