Monitoring : Clearing Chat Archive History

Hello everybody,

I would like to know if there is a way to clear or reset the chat archive within the monitoring plugin. How bout the stats?

I am using 3.5.1 with the monitoring plug-in.

Thank you all in advance !

,j

Hey Rockets,

You can use the following statements to delete “old” chat archiving data. Replace 1192224791872 with whatever timestamp you want.

DELETE ofMessageArchive.* FROM ofMessageArchive, ofConversation
WHERE ofMessageArchive.conversationID = ofConversation.conversationID
AND ofConversation.startDate <= 1192224791872; DELETE ofConParticipant.* FROM ofConParticipant, ofConversation
WHERE ofConParticipant.conversationID = ofConversation.conversationID
AND ofConversation.startDate <= 1192224791872; DELETE ofConversation.* FROM ofConversation
WHERE ofConversation.startDate <= 1192224791872;

Regarding statistics there is no need to delete old data since we are not storing historical data in a sequential manner. Instead we are using an RRD database that keeps always the same size. It is around 16K per file no matter the history we are keeping. Yes, I know…it is some kind of black magic. RRD is cool.

Regards,

– Gato

If im using MS-SQL server where does the archive logs saved? in the database?

and what table? thank you!

Hey radicalx,

They are stored in the tables that I reference in the above SQL statements.

Regards,

– Gato

Thanks for the reply… i see it now… i just did double check on the sql server

and found out the name of the table… thanks…

my mistake i did not double check it… cool

so i made an attachment here…

The following query will return all messages older than 1 month

select * FROM ofMessageArchive o

where sentDate/1000 < unix_timestamp(now())-2629743

Just make it Delete and your good. Next I’m going to work on this running every night so I know there’s only 30 days of message history.

DELETE FROM ofMessageArchive o

where sentDate/1000 < unix_timestamp(now())-2629743

How can i execute those queryies on embedded database ?

Can someone please clarify the command to delete table lines older than 30 days listed above? My browser shows the command as

“select * FROM ofMessageArchive o where sentDate/1000 < unix_timestamp(now())-2629743”. My sql server is throwing an error because of the “<” which looks like an ascii translation.

Any clarification would be appreciated. Has anyone been able to cronjob this on a unix system? I’m also planning on doing that.

Thanks!

Regards,

Daniel

To answer my own stupid quesiton, the command would be:

“select * FROM ofMessageArchive o where sentDate/1000 < unix_timestamp(now())-2629743”.

Hi,

can any one help me in retrieve and remove the archieve messages from client hand, i am stuck on it and no getting any api or way, that how can we access that archieved messages at client side with the help of smack library. Any type of help will be appriciate.

Thanks.

Hi! I finded a way, for db newbie or without any knowledge about db, to clear out the whole message archive from embedded database. I Used a DbVisualiser pro(trial) to edit openfire.script. Make a back-up before copy to windows.

Userid is SA, password empty, use database type HSQLDB, driver HSQLDB emdedded, version 1.8.0

Copied from my linux server(after stoped openfire server) to windows folder(via FileZilla thru sftp), opened in dbvisualizer, and empty rows from Public/Table-

  1. OFCONPARTICIPANT/DATA

  2. OFCONVERSATION/DATA

  3. OFMESSAGEARCHIVE/DATA
    OPTIONAL:

  4. OFMUCCONVERSATIONLOG/DATA
    Saved, and copy this one with empty rows. Start openfire service and voila, db has decreased size, and logs as well.

Good luck!

We are getting from slow response time when message archives are getting read one query take likes 30 seconds.
If i want to clear complete message archive (delete all old messages only, while keeping everything else like Users) would this command be good enough?

  1. Stop Openfire and backup DB

  2. Run the following query to delete all messages:

    DELETE FROM ofMessageArchive

Do i need to delete conversation IDs also ?