Wednesday 26 August 2020

Sametime 11 with MongoDB chat history query samples

Sometimes I meet end users who complain about missing/lost information in Sametime chat history. If you use Sametime with MongoDB you can run some useful queries to check/find data. I used the next queries in my test environment. Please consider character sets (iso-XXXX). To run these queries you do not need Sametime server. You need MongoDB only. MongoDB server/service must run. All components run on one Linux machine in my test environment and I did everything on that Linux machine.

*Considerations:

- You can use Mongo client and server on different machine. That case you have to add server connection parameters to the command on your client machine.

- There is no strict security in my test environment. In your environment, you may need username and password. Please remember MongoDB installation (Sametime user and password).

This query creates a json file and export all chats.

/usr/bin/mongoexport --db chatlogging --collection CONVOMAP --out /opt/mongobackup/chatlogging_CONVOMAP_26_August.json

You can create a CSV file, too to use it in MS Excel, Notepad++, etc. CSV export always requires --fields option! In this query I selected uid,name,with,msg fields.

/usr/bin/mongoexport --db chatlogging --collection CONVOMAP --fields uid,name,with,msg --type csv --out /opt/mongobackup/chatlogging_CONVOMAP_26_August.csv

To collect information for a specific user, you can create filters. In this query I am the chat initiator.

/usr/bin/mongoexport --db chatlogging --collection CONVOMAP --query "{ 'name' : 'CN=Andras Gabor Horvath/O=andrasst'}"

In this query I was invited to chat:

/usr/bin/mongoexport --db chatlogging --collection CONVOMAP --query "{ 'with' : 'CN=Andras Gabor Horvath/O=andrasst'}"

In this query I invited Daniel Szalai to chat with me:

 /usr/bin/mongoexport --db chatlogging --collection CONVOMAP --query "{ 'name' : 'CN=Andras Gabor Horvath/O=andrasst' , 'with' : 'CN=Daniel Szalai/O=andrasst' }"

*Comments: 

you can use name or uid. Some cases uid is better.

if you have character conversion issues (you can not read content) you can use character conversion tools. Examples:

file --mime-encoding chatlogging_CONVOMAP_26_August.csv 

iconv -f utf-8 -t iso-8859-1 chatlogging_CONVOMAP_26_August.csv > chatlogging_CONVOMAP_26_August_ISO_8859-1.csv

 OR

iconv -f utf-8 -t iso-8859-2 chatlogging_CONVOMAP_26_August.csv > chatlogging_CONVOMAP_26_August_ISO_8859-2.csv

No comments: