"Monitoring Service", bag from "Open Archive", request history

Yes, I installed the plugin. But where do I find the resulting xml logfiles?

I used Vacuum-IM to get the xml data using the xml console:

user1@host.tld/Vacuum-IM 10:58:54 +0 >>>>

  <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">

30

<<<< user1@host.tld/Vacuum-IM 10:58:54 +158 <<<<

  <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">

6036

6051

49

But this output is not correct as there are conversations after 20.02.2014 which are missing in the server reply. They are available using the openfire admin console.

I’m not install this plugin. In Windows, logs must be stored in: C:\Program Files (x86)\Openfire\logs\stdout.log

Give me anser of server for this command:

10000

user1@host.tld/Vacuum-IM 11:33:53 +9243 >>>>

  <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">

    <max xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">10000</max>

    <before xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)"/>

<<<< user1@host.tld/Vacuum-IM 11:33:53 +96 <<<<

  <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">

6026

6079

50

It seems that sorting by ‘start’ does not applied.

Very strange. Your first request return 16 instead 30 conversations. I suspect that there are problems with the structure of the database. Let’s try to do the following:

  1. What is the value you set in the “Server-Archiving-Archive Settings-Retrievable Messages”?

  2. Turn on Debug (Server-Server Manager-Logs-Debug-Enable)

  3. Run the query from Vacuum-IM:

30

  1. In the Debug window, locate the line “findConversations SELECT_CONVERSATIONS”, it will contain the SQL-query. Execute it to its database.

  2. Put here SQL-query and its result. And the answer from the Vacuum-IM.

I set it to 7 days:

SELECT DISTINCT ofconversation.conversationid,
ofconversation.room,
ofconversation.isexternal,
ofconversation.startdate,
ofconversation.lastactivity,
ofconversation.messagecount,
ofconparticipant.joineddate,
ofconparticipant.leftdate,
ofconparticipant.barejid,
ofconparticipant.jidresource,
ofconparticipant.nickname,
ofmessagearchive.fromjid,
ofmessagearchive.tojid
FROM ofconversation
INNER JOIN ofconparticipant
ON ofconversation.conversationid =
ofconparticipant.conversationid
INNER JOIN ofmessagearchive
ON ofconparticipant.conversationid =
ofmessagearchive.conversationid
WHERE ofconversation.startdate >= 1392666702239
AND ofconparticipant.barejid = 'user1@host.tld’
AND ( ofmessagearchive.tojid = 'user2@host.tld’
OR ofmessagearchive.fromjid = ‘user2@host.tld’ )
ORDER BY ofconversation.conversationid
LIMIT 30 offset 13

“6038”;NULL;“0”;“1392792982399”;“1392793855667”;“4”;“1392792982399”;“13927945518 03”;“user1@host.tld”;;NULL;“user1@host.tld”;“user2@host.tld”

“6038”;NULL;“0”;“1392792982399”;“1392793855667”;“4”;“1392792982399”;“13927945518 03”;“user1@host.tld”;;NULL;“user2@host.tld”;“user1@host.tld”

“6039”;NULL;“0”;“1392794816826”;“1392795083621”;“3”;“1392794816826”;“13927957518 04”;“user1@host.tld”;“5B06272E”;NULL;“user2@host.tld”;“user1@host.tld”

“6039”;NULL;“0”;“1392794816826”;“1392795083621”;“3”;“1392794816826”;“13927957518 04”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6040”;NULL;“0”;“1392803399331”;“1392803955150”;“4”;“1392803399331”;“13928047518 05”;“user1@host.tld”;;NULL;“user2@host.tld”;“user1@host.tld”

“6040”;NULL;“0”;“1392803399331”;“1392803955150”;“4”;“1392803399331”;“13928047518 05”;“user1@host.tld”;;NULL;“user1@host.tld”;“user2@host.tld”

“6041”;NULL;“0”;“1392805937653”;“1392806969437”;“61”;“1392805937653”;“1392807751 803”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6041”;NULL;“0”;“1392805937653”;“1392806969437”;“61”;“1392805937653”;“1392807751 803”;“user1@host.tld”;“5B06272E”;NULL;“user2@host.tld”;“user1@host.tld”

“6042”;NULL;“0”;“1392807832681”;“1392810583123”;“76”;“1392807832681”;“1392811351 806”;“user1@host.tld”;“5B06272E”;NULL;“user2@host.tld”;“user1@host.tld”

“6042”;NULL;“0”;“1392807832681”;“1392810583123”;“76”;“1392807832681”;“1392811351 806”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6043”;NULL;“0”;“1392812525512”;“1392812525513”;“1”;“1392812525512”;“13928131518 06”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6044”;NULL;“0”;“1392816559550”;“1392817640840”;“35”;“1392816559550”;“1392818251 803”;“user1@host.tld”;“5B06272E”;NULL;“user2@host.tld”;“user1@host.tld”

“6044”;NULL;“0”;“1392816559550”;“1392817640840”;“35”;“1392816559550”;“1392818251 803”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6045”;NULL;“0”;“1392818331830”;“1392818339922”;“3”;“1392818331830”;“13928191518 05”;“user1@host.tld”;“5B06272E”;NULL;“user2@host.tld”;“user1@host.tld”

“6045”;NULL;“0”;“1392818331830”;“1392818339922”;“3”;“1392818331830”;“13928191518 05”;“user1@host.tld”;“5B06272E”;NULL;“user1@host.tld”;“user2@host.tld”

“6046”;NULL;“0”;“1392883544693”;“1392884277084”;“12”;“1392883544693”;“1392884277 084”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6046”;NULL;“0”;“1392883544693”;“1392884277084”;“12”;“1392883544693”;“1392884277 084”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

“6047”;NULL;“0”;“1392884917058”;“1392885419160”;“9”;“1392884917058”;“13928860518 03”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6047”;NULL;“0”;“1392884917058”;“1392885419160”;“9”;“1392884917058”;“13928860518 03”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

“6048”;NULL;“0”;“1392888029824”;“1392888872060”;“9”;“1392888029824”;“13928896518 07”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6048”;NULL;“0”;“1392888029824”;“1392888872060”;“9”;“1392888029824”;“13928896518 07”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

“6049”;NULL;“0”;“1392890999217”;“1392891417545”;“12”;“1392890999217”;“1392892051 806”;“user1@host.tld”;;NULL;“user2@host.tld”;“user1@host.tld”

“6049”;NULL;“0”;“1392890999217”;“1392891417545”;“12”;“1392890999217”;“1392892051 806”;“user1@host.tld”;;NULL;“user1@host.tld”;“user2@host.tld”

“6050”;NULL;“0”;“1392892057438”;“1392892340986”;“7”;“1392892057438”;“13928929518 08”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6050”;NULL;“0”;“1392892057438”;“1392892340986”;“7”;“1392892057438”;“13928929518 08”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

“6051”;NULL;“0”;“1392892980381”;“1392893043922”;“3”;“1392892980381”;“13928938518 02”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

“6051”;NULL;“0”;“1392892980381”;“1392893043922”;“3”;“1392892980381”;“13928938518 02”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6052”;NULL;“0”;“1392894630725”;“1392894642082”;“2”;“1392894630725”;“13928953518 03”;“user1@host.tld”;;NULL;“user2@host.tld”;“user1@host.tld”

“6053”;NULL;“0”;“1392897606459”;“1392898349670”;“19”;“1392897606459”;“1392898951 808”;“user1@host.tld”;“382A19ED”;NULL;“user1@host.tld”;“user2@host.tld”

“6053”;NULL;“0”;“1392897606459”;“1392898349670”;“19”;“1392897606459”;“1392898951 808”;“user1@host.tld”;“382A19ED”;NULL;“user2@host.tld”;“user1@host.tld”

<<<< user1@host.tld/Vacuum-IM 20:51:42 +97 <<<<

  <set xmlns="[http://jabber.org/protocol/rsm](http://jabber.org/protocol/rsm)">

6045

6064

43

One of the bugs found.

Try this version of the plugin.

If this does not solve the problem, re-follow the recommendations: http://community.igniterealtime.org/message/236393#236393
Monitoring_1.4.0_patched_2.zip (1727195 Bytes)

Will give it a try. What did you change?

I changed the SQL-query, this change allows you to skip duplicate conversations that appear when table ofMessageArchive joined to table ofConversation:

public static final String SELECT_CONVERSATIONS = "SELECT DISTINCT " + "ofConversation.conversationID, " + "ofConversation.room, " + "ofConversation.isExternal, " + "ofConversation.startDate, " + "ofConversation.lastActivity, " + "ofConversation.messageCount, " + "ofConParticipant.joinedDate, " + "ofConParticipant.leftDate, " + "ofConParticipant.bareJID, " + "ofConParticipant.jidResource, " + "ofConParticipant.nickname, "

+ "case when ofConParticipant.bareJID=ofMessageArchive.fromJID then ofMessageArchive.fromJID else ofMessageArchive.toJID end as fromJID, "

+ "case when ofConParticipant.bareJID=ofMessageArchive.toJID then ofMessageArchive.fromJID else ofMessageArchive.toJID end as toJID "

  • "FROM ofConversation "

  • "INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID "

  • “INNER JOIN ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID”;

It seems to work! At least for vacuum-im, in xabber the messages contains wrong timestamps, but I think this is related to xabber.

Thank you very much!

Very glad it all worked out.

Thank you too for testing

“By the way, this plugin will generate an error when used in conjunction with MS SQL Server, because this database does not understand commands “LIMIT” and “OFFSET”. With PostgreSQL it’s work perfectly.”

True …so what whould be the solution for people like me who use sql server 2008 R2

I wish that the developers paid attention to it. I’ll try to fix it.

There are no developers

There are only people like you doing your best to make it all work for all of us

I would be greatful if you provide some workaround…thanks

OK, I solved the problem with “MS SQL Server”, but this plugin will only work with the version of “MS SQL 2005” and above.

I corrected the 2 bugs:

  1. The bug related to ‘LIMIT-OFFSET’ (for “MS SQL Server” has replaced another expression, other databases will use ‘LIMIT-OFFSET’).

  2. The bug related to expression “DISTINCT” and field ‘ofMessageArchive.body’ (“DISTINCT” can not work with the type “ntext”, so I changed it to “nvarchar(max)”. http://technet.microsoft.com/en-us/library/ms187993(v=sql.90).aspx).

I hope it will work
Monitoring_1.4.0_patched_3.zip (1729919 Bytes)
Monitoring_1.3.2-rc1_patched_3.zip (1541499 Bytes)

1 Like

Thank you so much …The pluging worked.

Now what? These fixes will be included in the official version?

P.S. Slightly corrected file to create tables for MS SQL Server when you first install the plugin. For those who have updated to patch 3 is not critical.
Monitoring_1.4.0_patched_4.zip (1729949 Bytes)
Monitoring_1.3.2-rc1_patched_4.zip (1541547 Bytes)

Yes. If you can create a patch file on the latest SVN, that will help a lot