Options for retrieving chats logged by the Presence server?

[kylebrogers]

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.  

[Geordie Guy]

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.

[kylebrogers]

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?

[Geordie Guy · Accepted solution]

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.

[kylebrogers]

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. 

[pamar66]

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;