Help with Presence Server 9.0 and Compliance Logging to Postgres

[patwill66_2]

I recently got Presence IM up and running, version, 9.0.1, using Jabber for Windows 9.1.1.  I am required to log all conversations so I stood up a postgres server, version 9.1.  I am not having any success with getting messages to log however. 

I have gone through the database deployment guide, along with the IM guide and the regular deployment guide that has some of the persistent chat stuff in it. 

Presence has created tables and did log some of the persistent chat stuff I did using CUPC 8.6 so I know the connection is working.  The 'jm' table was created so I know the connection for compliance is good as well.  I am just not sure what I am missing as to why it wont log conversations.

I have gone through the guides a couple times each and verified everything was done.  I am not seeing any errors in the postgres log files.  I see Presence connect and then disconnect right away. 

Any help or advice with log files or where to look next would be greatly appreciated.

[Christos Georgiadis]

Hi,

Can you check the following

-- Open up postgres admin

-- Drill down to the tcmadb database (if you configured the same name as stated in the guide)

-- Then go to Schemas --> Public --> Tables

-- Located jm table and right click --> View Data --> View Top 100 Rows

Do you see anything logged ?

Thx,

Christos

[patwill66_2]

No, there are no rows. All the columns have been created but no rows.

However, there is data in the tables for the persistent chats.

So I know presence can write to the database, I am just banging my head over the compliance part.

[Christos Georgiadis]

A few more things to check

Do not turn on the following configuration items in the /data/postgresql.conf file (by default these items are commented out):

client_min_messages = log

log_duration = on

If you are running PostgreSQL version 9.1.1, you must set the following values in the postgresql.conf file:

escape_string_warning = off

standard_conforming_strings = off

Cycle XCP Router and message archiver services.

If no go, then set the message archiver service traces to DEBUG level, reproduce the issue (ie chat between to clients) and check the message archiver logs. You will see the connection attempts from CUP to postgres and also the queries to insert the IM Data to the database

You can attach them here if you see there is no security concern

HTH,

Christos

[patwill66_2]

I checked the conf file and it matches what you recommended.

I then turned on trace logging, restarted the message archiver, ensured the external database screen show it could connect and tried sending some IM's.  The logs are below.

Still, no tables have any entries. 

07:48:05.470 HDR|04/08/2013 MA_1,StandAloneCluster4e198,x-x-x-x,Detailed,9.0.1.10000-21

07:48:05.470 |Tracing initialized

07:48:05.470 |b7e7fb90| debug| MAService.cpp:206 Got db config. Configuring.

07:48:05.506 |b7e7fb90| info| ODBCConnection.cpp:202 connect dsn:afde2fdb-8cfb-4fa4-bd39-29a086c432d7 username:tcuser type:2

07:48:05.564 |b7e7fb90| debug| MAService.cpp:246 Finished DB Config.

07:48:05.564 |b7e7fb90| info| MA Service No database section found in regular config. Fetching global config from the router.

07:48:05.564 |b7e7fb90| debug| JAXConnection.cpp:1453 JAXConnection::_setState state=3 prev_state=2

07:48:05.565 |b7e7fb90| debug| JAXConnection.cpp:1230 JAXConnection::_setState state=2 prev_state=3

07:48:05.566 |b7e7fb90| debug| metrics Get Interval Record: record_id('0')

07:48:05.566 |b7e7fb90| debug| MAService.cpp:314 MAService::onConfig _state 1

07:48:05.566 |b7e7fb90| debug| MAService.cpp:271 MAService::_configStats found ma-counters

07:48:05.566 |b7e7fb90| debug| metrics Added field 'packets-received' to record 'ma-counters'

07:48:05.566 |b7e7fb90| debug| metrics Added field 'packets-sent' to record 'ma-counters'

07:48:05.566 |b7e7fb90| debug| metrics Added field 'successful-db-writes' to record 'ma-counters'

07:48:05.566 |b7e7fb90| debug| metrics Added field 'failed-db-writes' to record 'ma-counters'

07:48:05.566 |b7e7fb90| debug| metrics Added field 'packets-dropped' to record 'ma-counters'

07:48:05.566 |b7e7fb90| debug| metrics Added field 'db-queue-size' to record 'ma-counters'

07:48:05.566 |b7e7fb90| debug| metrics Record registered: timeout(60), from('ma-1.x-x-x-x'), namespace('ma-counters'), timer_id(5), record_id(0)

07:48:05.566 |b7e7fb90| debug| JAXConnection.cpp:1115 JAXConnection::_setState state=4 prev_state=2

07:48:05.566 |b7e7fb90| info| MA Service Message Archiver is connected to the router.

07:53:06.802 |b4607b90| debug| ConnectionPool.cpp:98 ConnectionPool::_heartbeat_cb

07:58:08.809 |b4607b90| debug| ConnectionPool.cpp:98 ConnectionPool::_heartbeat_cb

[Christos Georgiadis]

OK I see that the service restarts fine and I don;t see any problems in there.

So between 07:48:05:566 and 7:58:08.809 were you IM'ing between two jabber users ? If yes you should have see something like the following (taken from my lab)

16:58:03.554 |b7f35b90| debug| MAService::onPacket Recv'd Packet: from='user1@cuplab.lab/jabber_21478' id='uid:5162d78f:00007a16:0000002f' to='user2@cuplab.lab/jabber_22432' type='chat' xml:lang='en'>test for IM<thread>connect22931test for IMhttp://jabber.org/protocol/chatstates'/>

and then message archiver should have forward this to the postgresdb

If that's the case maybe you could reload the box ? It seems to be that message archiver doesn't seem to be receiving the IM so it cannot forward this to postgres. If you would put wireshark on the postgres DB (provided you are running it on windows) then you should see tcp messages which the DB queries coming from CUP. If they are not then reloading the CUP might do the trick.

HTH,

Christos

[patwill66_2]

I am not seeing any messages like that in the log files.

I have ran wireshark on the postgres server and its not receiving any packets from the presence server when users are sending IMs back and forth.  If I do a connection test, I see that traffic, just not logging for IM conversations.  This was before and after a reboot I just performed.

[kelvinffhan]

How do we actually can retrieve the message from Postgre's JM tables?

[Jaime Valencia]

For that you use any tools you like to manage the DB, like pgAdmin, database .net, etc