[How-To] Save Chat log

Hi Community,

This is my first post doing a how to for the community so bare with me. Many of us need to record chat history for audit/review purposes. Although at the end the it’s a really straight forward process finding out the pieces and make them work was a challenge that took few days to figure out. I share this how-to to make it easier to people who need this functionality.

First you have to realize that everything is based on Prosody. You will have to do several changes and installs to make it work.

This works for Prosody .11 or newer

  1. Database option

First of all the recommended database to use is MariaDB (not MySQL). The reason is that prosody creates two tables automatically.

±------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-----------±-----±----±--------±------+
| host | text | NO | MUL | NULL | |
| user | text | NO | | NULL | |
| store | text | NO | | NULL | |
| key | text | NO | | NULL | |
| type | text | NO | | NULL | |
| value | mediumtext | NO | | NULL | |
±------±-----------±-----±----±--------±------+

AND

±--------±-----------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±--------±-----------±-----±----±--------±---------------+
| sort_id | int | NO | PRI | NULL | auto_increment |
| host | text | NO | MUL | NULL | |
| user | text | NO | | NULL | |
| store | text | NO | | NULL | |
| key | text | NO | | NULL | |
| when | int | NO | | NULL | |
| with | text | NO | | NULL | |
| type | text | NO | | NULL | |
| value | mediumtext | NO | | NULL | |
±--------±-----------±-----±----±--------±---------------+

As you notice prosody use “reserved words (key,when,with)” that makes some SQL statements fail… I figure out his after reading lots of sql errors that make no sense until i saw the reserved words… once i installed MariaDB everything started to work. So MySQL is not an option if you want to use prosody with database storage.

The instructions to install mariaDB depends on the OS flavor your are using… but there are plenty of docs around. Just make sure the following:

a)Create a database called “prosody”
b)Create a user that has admin privileges to database “prosody”

  1. In order to make possible to prosody communicate with the database you need to install the LUA-DBI library. You can check more info about this here :

https://prosody.im/doc/modules/mod_storage_sql

example in Debian:

apt install lua-dbi-postgresql

3)Configure prosody to be persistent.

Prosody has to be configured to use sql storage instead of memory or Internal (text files). This configuration is done in the .cfg.lua in Debian the path is:

/etc/prosody/config.d/.cfg.lua

Look for the area "Component “conference.” “muc” and change “storage” the following

storage = “sql”
sql = {
driver = “PostgresSQL”;
database = “prosody”;
port = 3306; – For databases connecting over TCP
username = “yourdatabaseuser”; – The username to authenticate to the database
password = “yourdatabasepassw”; – The password to authenticate to the database
}

  1. In the same are "Component “conference.” “muc” add in the area modules the mum_mam plugin it should look like this:

modules_enabled = {
“muc_meeting_id”;
“muc_domain_mapper”;
“muc_mam”;
“token_verification”;
}

  1. There are options of the module “muc_man” that have to be present check for options
    mod_muc_mam – Prosody IM but the most important is

    muc_room_default_persistent = true
    sql_manage_tables = true

The default is FALSE so what happens is that the messages are stored in the database only during the conference is active. Once everybody logoff the messages get erased from the database as well.

  1. Restart PROSODY and look logs for errors.

There are plenty of options to make the messages available for a period of time (1 week, 1 month, always) , the amount on messages show as history in case you reuse a conference room name etc etc.

As i said this is not something too hard but at least for me was a challenge.

Hope this helps

2 Likes