goto home

mod_logdb - Log user messages to db (mnesia, mysql, pgsql now supported).

WARNING!!! This module still is under development, so backward compatibility is not guaranteed.

WARNING!!! If you are upgrading from earlier versions, make sure that you have backup of database, before updating.

Author: Oleg Palij (mailto,

Your pull requests are welcome in github (use recent XX.XX-mod_logdb branch).

    ejabberd (module development is tracking debian stable version)
    erlang mysql driver if you planning to log messages to mysql or mysql5 (revision 850 at least). Download tarball with erlang-mysql_userflag.diff patch applied.
    pgsql driver from jungerl if you planning to log messages to pgsql


  This module sniffs all the user messages send through ejabberd.
  It logs messages to selected database (mnesia, mysql, pgsql)
  Logged messages can be viewed through ejabberd web admin interface.
  Module settings and per user settings can be changed by admin/user via ad-hoc commands.
  User logging settings can be changed via web admin console.

INSTALL xx.xx (debian):

  # cd /usr/local/src
  # apt-get source ejabberd
  # cd ejabberd-14.07
  # apt-get install quilt
  # export QUILT_PATCHES=debian/patches
  # quilt push -a
  # quilt new 99_patch-mod_logdb-14.07.diff
  # quilt add rebar.config.script
  # quilt add priv/msgs/nl.msg
  # quilt add priv/msgs/pl.msg
  # quilt add priv/msgs/ru.msg
  # quilt add priv/msgs/uk.msg
  # quilt add src/gen_logdb.erl
  # quilt add src/mod_logdb.erl
  # quilt add src/mod_logdb.hrl
  # quilt add src/mod_logdb_mnesia.erl
  # quilt add src/mod_logdb_mnesia_old.erl
  # quilt add src/mod_logdb_mysql.erl
  # quilt add src/mod_logdb_mysql5.erl
  # quilt add src/mod_logdb_pgsql.erl
  # quilt add src/mod_muc_room.erl
  # quilt add src/mod_roster.erl
  # wget -q -O- | patch -p1
  # quilt refresh
  # quilt pop -a
  # apt-get build-dep ejabberd
  # apt-get install devscripts
  # debuild -uc -us

ejabberd debian package with mod_logdb should be in ../ejabberd_14.07*.deb

If you are planning to use mysql5 backend you should recompile erlang-p1-mysql package too:
  # cd /usr/local/src
  # apt-get source erlang-p1-mysql
  # cd erlang-p1-mysql-0.2014.03.10/
  # export QUILT_PATCHES=debian/patches
  # quilt new 99_multi.diff
  # quilt add src/p1_mysql_auth.erl
  # wget -q -O- | patch -p1
  # quilt refresh
  # quilt pop -a
  # apt-get build-dep erlang-p1-mysql
  # debuild -uc -us

erlang-p1-mysql debian package with multi patch applied should be in ../erlang-p1-mysql_*.deb

INSTALL xx.xx (*nix with ejabberd already installed):

  1. Download:
      Patch for ejabberd 16.04 (should be compatible from 16.04)
      Patch for ejabberd 15.07 (should be compatible from 15.06)
      Patch for ejabberd 14.07 (should be compatible up to 15.02)
      Patch for ejabberd 13.12
  2. Change dir to your ejabberd sources:
  # cd /usr/local/src/ejabberd-xx.xx
  3. clean sources (because of changes in rebar script):
  # make distclean
  4. Apply patch:
  # patch -p1 < patch-mod_logdb_xx.xx.diff
  5. Rebuild/reinstall ejabberd. Do not forget enable odbc and dbs support (./configure --enable-odbc --enable-mysql --enable-pgsql).
  6. Add to ejabberd.cfg, 'modules' section, configuration (see CONFIGURE)
  7. Restart ejabberd.


  $ git clone
  $ git checkout 13.12-mod_logdb
  $ ./
  $ ./configure --enable-odbc --enable-mysql --enable-pgsql --enable-pam --enable-zlib --enable-json --enable-iconv --enable-lager --enable-transient_supervisors
  $ make
  $ sudo cp -v ebin/mod_logdb* ${EJABBERD_PREFIX}/ebin/
  $ sudo cp ebin/mod_muc_room.beam ${EJABBERD_PREFIX}/ebin/
  $ sudo cp ebin/mod_roster.beam ${EJABBERD_PREFIX}/ebin/
  $ sudo cp priv/msgs/{nl,pl,ru,uk}.msg ${EJABBERD_PREFIX}/msgs/

INSTALL 2.x.x (*nix):

  1. Download:
      Patch for ejabberd 2.1.12-13 (works with recent erlang R15)
      Patch for ejabberd 2.1.10-11 (works with recent erlang R15)
      Patch for ejabberd 2.1.3-6 (can't be used with recent erlang R15)
      Patch for ejabberd 2.0.x (can't be used with recent erlang R15)

      Patch for mysql erlang driver if you will use mysql5 backend.
  2. Change dir to your ejabberd sources:
  # cd /usr/local/src/ejabberd-x.x.x
  3. Apply patch:
  # patch -p0 < patch-mod_logdb_x.x.x.diff
  4. Rebuild/reinstall ejabberd. Do not forget enable odbc support (./configure --enable-odbc).
  5. Add to ejabberd.cfg, 'modules' section, configuration (see CONFIGURE)
  6. Do not forget patch/rebuild/reinstall mysql erlang driver (if you will use mysql5 backend)
  7. Restart ejabberd.

INSTALL (windows):

  1. Download:
      mysql erlang driver r850 with userflags patch
      mod_logdb binary files for ejabberd 2.0.5
  2. Copy mysql erlang driver binaries '*.beam' into "C:\Program Files\ejabberd-2.0.5\lib\mysql-2009.0318\ebin"
  2. Copy mod_logdb binaries '*.beam' into "C:\Program Files\ejabberd-2.0.5\lib\ejabberd-2.0.5\ebin"
  3. Copy mod_logdb msgs files '*.msg' into "C:\Program Files\ejabberd-2.0.5\lib\ejabberd-2.0.5\priv\msgs\"
  4. Add to ejabberd.cfg, 'modules' section, configuration (see CONFIGURE)
  5. Restart ejabberd.


   Describes db parameters in format {dbs, [{db_backend_name, [db_backend_opts]}, ... ]}
   Currently supported backends:
       a) mnesia.
       b) mysql - can be used both with mysql v4 and mysql v5.
       c) mysql5 - can be used only with mysql v5. This backend (in comparison with mysql backend) move most of work to mysql server, so it should decrease load of jabber server.
       d) pgsql - uses the same technics as mysql5 backend.
   Default value - [{mnesia, []}] - only mnesia supported.

Example -
 {dbs, [{pgsql, [{user, "postgres"},
        {password, "pass"},
        {server, "serv"},
        {port, 5432},
        {db, "logdb"},
        {schema, "test"}]}]}
Example - 
 {dbs, [{mysql5, [{user, "root"},
        {password, "pass"},
        {server, "serv"},
        {port, 3306},
        {db, "logdb"}]}]}
Example - 
 {dbs, [{mysql, [{user, "root"},
        {password, "pass"},
        {server, "serv"},
        {port, 3306},
        {db, "logdb"}]}]}]}
Example - 
 {dbs, [{mnesia, []}]}

   Defines domains served by ejabberd for which you want to log messages in format {vhosts, [{"vhost_name", db_backend_name}, ... ]}
     Each db_backend_name must be described in dbs section.
   Does not have default value.
   Example - {vhosts, [{"", mysql5}, {"", mnesia}, {"",mysql5}]}
   Example - {vhosts, [{"", mysql}]}

   Ignore messages sent to or from JIDs/Domains in the list.
   Domains must start with @.    Default value - [].
   Example - {ignore_jids, ["", ""]}

    This defines how to process groupchat messages.
    possible values:
     all - to log all groupchat messages
     send - to log only sent by user messages
     none - to disable logging of groupchat messages (recommended value, use mod_muc_log for groupchat logging)
   Default value - none.
   Example - {groupchat, send}

   For automatic purging of messages older than specified value.
    possible values:
     never - do not perform automatic purging of messages
     Number - perform daily automatic purging of messages older than Number (in days)
   Default value - never.
   To keep only "today's" messages set Number to 0.
   Example - {purge_older_days, 4}

   Defines if module will store messages by defaut.
    possible values:
     true - log messages by default
     false - do not log messages by default
   Default value - true.
   Example - {dolog_default, false}

   Defines time interval for module to poll db for user settings.
    possible values:
     0 - disable polling
     Number - poll database every Number (in seconds)
   This can be set to 0 if you are not planning to directly edit db and you are not using module in clustered environment.
   Default value - 10.
   Example - {poll_users_setting, 0}


This allows all users to change their logging preferences via ad-hoc commands:
  {access, mod_logdb, [{allow, all}]}.
This allows admins to change module/users settings via ad-hoc commands:
  {access, mod_logdb_admin, [{allow, admin}]}.


  You have two domains and You want to log messages for into mysql5 and messages for into mnesia

YAML, indentation matters (recent XX.XX versions):
      mnesia: []
        server: "serv"
        port: 3306
        db: "logdb"
        user: "user"
        password: "passwd"
      "": mysql5
      "": mnesia
    groupchat: half
    dolog_default: true
      - ""
      - ""
      - ""

Erlang (old 2.x.x versions):
  {modules, [
    {mod_logdb, [
      {dbs, [
        {mysql5, [
          {user, "root"},
          {password, "pass"},
          {server, "serv"},
          {port, 3306},
          {db, "logdb"}
        {mnesia, []}
      {vhosts, [{"", mysql5}, {"", mnesia}]},
      {ignore_jids, ["", "", ""]},
      {groupchat, none},
      {purge_older_days, 365},
      {dolog_default, true},
      {poll_users_settings, 5}
    %% Other modules ...

ejabberdctl COMMANDS:

  vhost you-vhost-here copy_messages backend [date] - you can copy messages from given backend to current backend.
    E.g. - you initially used mysql backend and now you want to switch to pgsql backend. Make sure that both backends described in module config dbs section, and reconfigure module to use pgsql backend, restart module/server, then try:
        $ ejabberdctl ejabberd@your-server vhost you-vhost copy_messages mysql
    If you want to switch from mod_logmnesia to mod_logdb - add to dbs - {mnesia_old, []}, restart module/server and then try:
        $ ejabberdctl ejabberd@your-server vhost you-vhost copy_messages mnesia_old
    Copy progress will be shown in ejabberd logs.
    This feature was not 100% tested, so be carefull.

  vhost your-vhost-here rebuild-stats - in some rare (I hope :)) cases it is possible that statistics become corrupted, this command rebuilds statistics for vhost.


     * removed binding to offline_message hook for ejabberd 2.1.3 becouse of EJAB-1193 (causes doubling offline messages)
     * pgsql: added error msg when plpgsql is not installed into db
     * pgsql: added support for newest pgsql driver from jungerl
     * show only user nick (as in roster, if defined) on webadmin while viewing user messages
     * reworked rebuild_all_stats to do work in background with minimum number of locks
     * fixed delete_nonexistent_stats when there is no messages tables yet
     * do drop_user in separate database connection
     * properly close database connection on module restart/crash
     * code cleanup
     * bugfix: scheduled purging old records task does not work
     * bugfix: rebuild stats for only not NULL ext records in mysql5 backend (thanks to Zbyszek Zolkiewski)
     * fixed bug with logging messages for many vhosts into single mysql5 or pgsql db backend
     * reworked web interface for 2.0.2 to use webadmin hooks
     * a lot of bugfixes
     * added option to remove user messages archive on user removal
     * changed structure of stats table (table will be upgraded automaticaly, but make sure that you have database backup)
     * optimizations to mysql/pgsql database structure
     * bugfix (added escaping of resources)
     * module was published


Ad-hoc interface.

Open the web interface (http(s)://server:port/admin/) in your favourite browser.

Go to http(s)://server:port/admin/server/you_vhost_name/
You should see 'Users Messages' link in the bottom of links list.

'Users Messages' link shows statistic about logged messages for virtual host by date.

After pointing to concrete date you should see day statistic about logged messages for virtual host.

Point on user to see logged messages for this user at choosen date.

Or you can point your browser to user properties:
You should see 'Messages' link in the bottom of links list.

'Messages' link shows statistic about logged messages for this user.

In user roster you can set per contact user settings.