Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

New Member

Options for retrieving chats logged by the Presence server?

If I create my own external PostgreSQL database and tell Presence to log chats and chat room texts to the database, is there a way to retrieve them without writing my own SQL code for retrieval?  
I know there are 3rd party servers that you can offload your logging to for this purpose but I'm trying to do this without buying another product or having to enlist a SQL DBA.  

1 ACCEPTED SOLUTION

Accepted Solutions
New Member

Not really, but the SQL for

Not really, but the SQL for searching the database for a string like that is pretty straight forward.  I use two queries when I touch the compliance DB at all.  The first finds a string like your Acme, Inc and the second finds messages from a particular user;

 

select * from jm where body_string like '%Acme%'

 

and

 

select * from jm where from_jid = 'john.citizen@acmeinc.com'

 

I guess you've either got the need to just find a couple of things like "messages that contain Acme" or "messages from John", or you've got a broader need and then a third party solution is necessary.  The trick is figuring out where on the scale you are.

 

5 REPLIES
New Member

Not really.  What are you

Not really.  What are you trying to retrieve and why?  The reason you're doing this will change the answers for what you should do.

New Member

Let's say this is for a

Let's say this is for a company and they get subpoenaed or for a government office and someone submits an open records request and the customer is required to produce all conversations that contained the phrase "Acme, Inc".  Would there be a way to retrieve this without a 3rd party solution or creating your own complex SQL queries?

New Member

Not really, but the SQL for

Not really, but the SQL for searching the database for a string like that is pretty straight forward.  I use two queries when I touch the compliance DB at all.  The first finds a string like your Acme, Inc and the second finds messages from a particular user;

 

select * from jm where body_string like '%Acme%'

 

and

 

select * from jm where from_jid = 'john.citizen@acmeinc.com'

 

I guess you've either got the need to just find a couple of things like "messages that contain Acme" or "messages from John", or you've got a broader need and then a third party solution is necessary.  The trick is figuring out where on the scale you are.

 

New Member

The problem with chat is that

The problem with chat is that searching based off a phrase will only provide me with that one sentence out of a larger conversation.   Searching based off a user will give me all of their sentences but not responses to them or any context as to what conversation each sentence was a part of.  I guess the real answer is that I would have to have a 3rd party compliance server to get any use out of it. 

New Member

I wrote the below to get

I wrote the below to get message content for specified user for date range, we have a few DBs that hold messages (user, group, chat room, etc).  A bit of a hack, but my PG not up to par as it is with Oracle, Sybase and MSSQL.  I get each by direction and try to link in one line of output to show the to/from user and respective message content.


SELECT  
 MessageDateTime  AS "MessageDateTime",
 FromUser   AS "FromUser",
 ToUser   AS "ToUser",
 MsgBody   AS "MsgBody"
FROM
(
 SELECT 
  TO_CHAR(tb2.sent_date,'YYYY-MM-DD HH24:MM:SS') AS MessageDateTime,
  tb2.from_jid     AS FromUser,
  tb2.To_jid     AS ToUser,
  tb2.body_string     AS MsgBody
 FROM dblink(' user=FakeLinkUser
   password=FakeLinkUserPW  
   dbname=impcompliancepub', 'SELECT from_jid, to_jid, sent_date, body_string, direction, body_len FROM public.jm') 
  AS tb2(from_jid varchar(3017), to_jid varchar(3017), sent_date timestamp, body_string text, direction varchar(1), body_len int) 
  WHERE TO_CHAR(tb2.sent_date,'YYYY-MM-DD') between '2017-05-01' and '2017-08-10'
  AND split_part(tb2.from_jid,'@',1) = 'UserWODomainName'
  AND upper(tb2.direction) = 'O'
  AND tb2.body_len > 0
 UNION ALL
 SELECT 
  TO_CHAR(tb4.sent_date,'YYYY-MM-DD HH24:MM:SS') AS MessageDateTime,
  tb4.from_jid     AS FromUser,
  tb4.To_jid     AS ToUser,
  tb4.body_string     AS MsgBody
 FROM dblink(' user=FakeLinkUser
   password=FakeLinkUserPW  
   dbname=impcompliancesub', 'SELECT from_jid, to_jid, sent_date, body_string, direction, body_len FROM public.jm') 
  AS tb4(from_jid varchar(3017), to_jid varchar(3017), sent_date timestamp, body_string text, direction varchar(1), body_len int) 
  WHERE TO_CHAR(tb4.sent_date,'YYYY-MM-DD') between '2017-05-01' and '2017-08-10'
  AND split_part(tb4.from_jid,'@',1) = 'UserWODomainName'
  AND upper(tb4.direction) = 'O'
  AND tb4.body_len > 0
 UNION ALL
 SELECT 
  TO_CHAR(tb6.sent_date,'YYYY-MM-DD HH24:MM:SS') AS MessageDateTime,
  tb6.from_jid     AS FromUser,
  tb6.To_jid     AS ToUser,
  tb6.body_string     AS MsgBody
 FROM dblink(' user=FakeLinkUser
   password=FakeLinkUserPW  
   dbname=persistantchatpub', 'SELECT from_jid, to_jid, sent_date, body_string, direction, body_len FROM public.jm') 
  AS tb6(from_jid varchar(3017), to_jid varchar(3017), sent_date timestamp, body_string text, direction varchar(1), body_len int) 
  WHERE TO_CHAR(tb6.sent_date,'YYYY-MM-DD') between '2017-05-01' and '2017-08-10'
  AND split_part(tb6.from_jid,'@',1) = 'UserWODomainName'
  AND upper(tb6.direction) = 'O'
  AND tb6.body_len > 0
 UNION ALL
 SELECT 
  TO_CHAR(tb8.sent_date,'YYYY-MM-DD HH24:MM:SS') AS MessageDateTime,
  tb8.from_jid     AS FromUser,
  tb8.To_jid     AS ToUser,
  tb8.body_string     AS MsgBody
 FROM dblink(' user=FakeLinkUser
   password=FakeLinkUserPW  
   dbname=persistantchatsub', 'SELECT from_jid, to_jid, sent_date, body_string, direction, body_len FROM public.jm') 
  AS tb8(from_jid varchar(3017), to_jid varchar(3017), sent_date timestamp, body_string text, direction varchar(1), body_len int) 
  WHERE TO_CHAR(tb8.sent_date,'YYYY-MM-DD') between '2017-05-01' and '2017-08-10'
  AND split_part(tb8.from_jid,'@',1) = 'UserWODomainName'
  AND upper(tb8.direction) = 'O'
  AND tb8.body_len > 0
) A
UNION
SELECT  
 MessageDateTime  AS "MessageDateTime",
 FromUser   AS "FromUser",
 ToUser   AS "ToUser",
 MsgBody   AS "MsgBody"
FROM
(
 SELECT 
  TO_CHAR(tb2.sent_date,'YYYY-MM-DD HH24:MM:SS') AS MessageDateTime,
  tb2.from_jid     AS FromUser,
  tb2.To_jid     AS ToUser,
  tb2.body_string     AS MsgBody
 FROM dblink(' user=FakeLinkUser
   password=FakeLinkUserPW  
   dbname=impcompliancepub', 'SELECT to_jid, from_jid, sent_date, body_string, direction, body_len FROM public.jm') 
  AS tb2(to_jid varchar(3017), from_jid varchar(3017), sent_date timestamp, body_string text, direction varchar(1), body_len int) 
  WHERE TO_CHAR(tb2.sent_date,'YYYY-MM-DD') between '2017-05-01' and '2017-08-10'
  AND split_part(tb2.to_jid,'@',1) = 'UserWODomainName'
  AND upper(tb2.direction) = 'I'
  AND tb2.body_len > 0
 UNION ALL
 SELECT 
  TO_CHAR(tb4.sent_date,'YYYY-MM-DD HH24:MM:SS') AS MessageDateTime,
  tb4.from_jid     AS FromUser,
  tb4.To_jid     AS ToUser,
  tb4.body_string     AS MsgBody
 FROM dblink(' user=FakeLinkUser
   password=FakeLinkUserPW  
   dbname=impcompliancesub', 'SELECT to_jid, from_jid, sent_date, body_string, direction, body_len FROM public.jm') 
  AS tb4(to_jid varchar(3017), from_jid varchar(3017), sent_date timestamp, body_string text, direction varchar(1), body_len int) 
  WHERE TO_CHAR(tb4.sent_date,'YYYY-MM-DD') between '2017-05-01' and '2017-08-10'
  AND split_part(tb4.to_jid,'@',1) = 'UserWODomainName'
  AND upper(tb4.direction) = 'I'
  AND tb4.body_len > 0
 UNION ALL
 SELECT 
  TO_CHAR(tb6.sent_date,'YYYY-MM-DD HH24:MM:SS') AS MessageDateTime,
  tb6.from_jid     AS FromUser,
  tb6.To_jid     AS ToUser,
  tb6.body_string     AS MsgBody
 FROM dblink(' user=FakeLinkUser
   password=FakeLinkUserPW  
   dbname=persistantchatpub', 'SELECT to_jid, from_jid, sent_date, body_string, direction, body_len FROM public.jm') 
  AS tb6(to_jid varchar(3017), from_jid varchar(3017), sent_date timestamp, body_string text, direction varchar(1), body_len int) 
  WHERE TO_CHAR(tb6.sent_date,'YYYY-MM-DD') between '2017-05-01' and '2017-08-10'
  AND split_part(tb6.to_jid,'@',1) = 'UserWODomainName'
  AND upper(tb6.direction) = 'I'
  AND tb6.body_len > 0
 UNION ALL
 SELECT 
  TO_CHAR(tb8.sent_date,'YYYY-MM-DD HH24:MM:SS') AS MessageDateTime,
  tb8.from_jid     AS FromUser,
  tb8.To_jid     AS ToUser,
  tb8.body_string     AS MsgBody
 FROM dblink(' user=FakeLinkUser
   password=FakeLinkUserPW  
   dbname=persistantchatsub', 'SELECT to_jid, from_jid, sent_date, body_string, direction, body_len FROM public.jm') 
  AS tb8(to_jid varchar(3017), from_jid varchar(3017), sent_date timestamp, body_string text, direction varchar(1), body_len int) 
  WHERE TO_CHAR(tb8.sent_date,'YYYY-MM-DD') between '2017-05-01' and '2017-08-10'
  AND split_part(tb8.to_jid,'@',1) = 'UserWODomainName'
  AND upper(tb8.direction) = 'I'
  AND tb8.body_len > 0
) A
ORDER BY "MessageDateTime" ASC;

240
Views
0
Helpful
5
Replies