Viewing Cisco Jabber local history file

I had to assist a few users with an odd local chat history issue with Cisco Jabber recently where some message history went missing. In working this issue, I had to engineer a method to read the local history database file and extract the chat history. This post covers how you can open and query this file if you find yourself in a similar situation.

PLEASE NOTE: The procedure, outlined below, works with versions prior to Jabber 11.9. Version 11.9 and later, configuration and .db files are encrypted. This was part of security enhancements implemented in Jabber 11.9.

The Jabber for Windows local chat history log is just a local SQLite database file. The .DB file is located in the following directory: %LOCALAPPDATA%\Cisco\Unified Communications\Jabber\CSF\History\

Jabber for Mac likely has a similar file, I just don’t have a Mac in front of me to confirm, but check here: ~/Library/Application Support/Cisco/Unified Communications/Jabber/CSF/History/

You can use an open source SQLite tool to open this file. The one I used is called DB Browser for SQLite. There are builds available for several platforms. Download and install the build for your OS; or compile your own — it’s open source.

Once installed, launch the SqliteBrowser application and select “Open Database”. Point it at your local chat history .DB file. Once open, you can see the relatively simple table structure for the Jabber chat history.

At this point, if you’re trying to just see the chat history messages, like I was, you will want to run a SQL query. To do this, select the “Execute SQL” tab.

SQLite-ExecuteSQL

What query do you run? Well this took me a little bit of trial and error, but here is what I settled on:

select datetime(history_message.date/1000000,'unixepoch','localtime') as time, history_message.payload, history_message.sender from history_message

The dividing the time by 1000000 was the hardest part to figure out. Without it, my timestamps didn’t make sense. If you are looking for messages to/from a particular user you can edit your query to something like:

select datetime(history_message.date/1000000,'unixepoch','localtime') as time, history_message.payload, history_message.sender from history_message where history_message.sender like 'userid@example.com'

Messing around with your SQL query can yield you all sorts of information that might assist in your chat history troubleshooting.

One thing to note is that local chat history is not meant to be a compliance or legal retention mechanism. If you have those requirements, it’s best to use a 3rd party compliance server configured at your CUPS/IM&P Servers.

Hopefully this helps if you ever find you in a similar situation.

20 thoughts on “Viewing Cisco Jabber local history file

  1. As far as I know, the history file only writes as messages come in client side and won’t tell you active chats. For real-time info for several clients, you likely will need to leverage an external compliance DB. These can be setup on the CUP server admin GUI.

  2. Hi this is good tip get history at single place, but not easy readable, but this history is same history as in the chat and not what is discarded by jabar, so how to retrieve old history ?

  3. Can you tell me if messages that were deleted in the jabber client are deleted from the history file?

  4. Would you be able to open message history if I sent you a database file?

  5. @Anonymous: The DB file contains the raw chat output. It will not be in the same custom format that the Jabber client does behind the scenes according to the default or user’s custom font/display preferences.

    @Doug: yes this is the local client history file only. If the Jabber cache is cleared, you will not see the older messages. If you need server level centralized logging, you need to setup an external compliance DB on the IM&P Servers.

    @Phil: I hope the instructions were easy enough to follow here to DIY. I’m not comfortable looking at database file from someone else because there could be confidential/sensitive/personal information in these files. I don’t want to deal with that mess and honestly, I just don’t have the time. If you need some help, you can always reach out to a local Cisco partner for some Jabber expertise.

  6. When I open the file, it prompts a dialog box that requires me to enter a passphrase.

  7. I haven’t encountered this before. There may be some new parameter that allows the history file to be encrypted or something. I’d have to do some research on this one.

  8. It looks as though with 11.9 Cisco changed something in the DB file, it is now asking for that decryption phrase or passkey. Very troublesome now…thanks Cisco.

  9. I opened a ticket with Cisco, after battling with them for a little while I finally received the answer below:

    I have confirmation from development Jabber 11.9 does encrypt configuration and .db files. This is part of security enhancements implemented in Jabber 11.9.

    The intent of this security enhancement is mainly for

    1. Protecting user’s data. For example, if the device is lost, or some malware leaks the Jabber data, bad guy has no way to decrypt the files.

    2. Protecting Jabber from being hijacked. For fast login, Jabber needs to load the cached configuration files/db. Encrypting the files can prevent them from being tampered. So that, it can make sure Jabber to load correct data and connect to the correct services. Otherwise, Jabber users may see tampered data from Jabber UI, and Jabber may be hijacked to connect to wrong services and then leak the credentials.

    Cisco cannot provide a way to decrypt, as this will make the security enhacement meaningless.

    As you know, the current Jabber 11.9 documentation incorrectly states these files are not encrypted. We will update the documentation to with the correct information soon.

    I suggest using message archival and/or compliance logging to keep track of Instant Messages.

    Message archival are third party applications and not a Cisco product.

    Bummer
    -Mike

  10. hi mike, is it possible to delete or recall a particular message send via cisco jabber from the receivers pc chat history db file.

  11. If you have access to the DB file, you can certainly read it. I haven’t tried removing a particular/specific message but, in theory, since this is a database, it should be possible.

  12. Hey Mike I am having to retrieve chat history from a .db file that was created on 11.9. The file is encrypted and I do not know where the key lies (assuming C:\Users\username\AppData\Roaming\Cisco\Unified Communications\Jabber\CSF\Credentials). This is an investigation I am conducting so any direction would be much appreciated. Thank you.

  13. The keys are not directly accessible. Depending on your deployment and the situation, you could change the password for the user, login, and open their chat history from within their Jabber client. It’s messy and not easy to pull off, but A coworker has been successful doing this. The JID must match or the file will be overwritten. Good luck. Remember that none of this is TAC supported.

  14. Hey Michael..I am trying to read my own chat history and still it is asking for that decryption phrase or passkey. Any idea on how to get rid of this?

  15. If you’re using version 11.9 and later, configuration and .db files are encrypted and can no longer be read.

  16. If you’re using version 11.9 and later, configuration and .db files are encrypted and can no longer be read.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.