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.
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 'firstname.lastname@example.org'
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.