cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
1185
Views
0
Helpful
5
Replies
Highlighted
Enthusiast

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
Highlighted

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.

 

View solution in original post

5 REPLIES 5
Highlighted
Beginner

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.

Highlighted

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?

Highlighted

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.

 

View solution in original post

Highlighted

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. 

Highlighted

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;

Content for Community-Ad