Monday, 22 December 2014

Jabber IM Chat Log

The best way to log IM chats for Jabber is to use the server side chat logging to a PostgreSQL database, but for those of you who haven’t deployed this feature there is a limited workaround for getting at a user’s chat history...

Jabber for Windows will create a history of the last 99 messages in an IM conversation. When a conversation reaches 100 messages the client will loop and overwrite the history. The IM history is stored in a local database file in the user’s local machine profile:

C:\Users\[userid]\AppData\Local\Cisco\Unified Communications\Jabber\CSF\History

The .db file is simply an SQLite database file, and can be opened in any SQLite editor, such as DB Browser for SQLite.
Look at the history_participant table to find usernames in the jid field, if there are multiple entries with the same number in the item field then these are the conversation participants.
The history_item table lists details of a particular call/conversation, the item field from the history_participant table identifies the id in the history_item table.
The history_message table contains IM messages (with HTML tags) in the payload field, the date & sender fields let you identify when & who sent the message. The item field links to the id field from the history_item table and the item field from the history_participant table.

4 comments:

  1. This has been a great help but what I am running into now is that the database file will not update. So I am only seeing messages that are old and none that are recent. What would cause this database to not be updated I thought it would be automatic every day?

    ReplyDelete
    Replies
    1. Jabber shouldn't stop logging messages locally unless it is unable to write to the file (e.g. permissions) or local IM logging has been disabled via configuration files:


      true

      Delete
  2. How many days are deleted voice messages kept for?

    ReplyDelete
  3. Is there a guide on how to extract the chat history with the SQL DB browser software? Much appreciated TQ

    ReplyDelete