powered by Jive Software

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?

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

The time stamp is calculated in milliseconds that passed since 1.1.1970. To calculate the timestamp for the Jan 1st 2010 (1262304000000) you may use this Excel formular: =(DATE(2010;1;1)-DATE(1970;1;1))2460601000

The SQL statements in an earlier post do not execute on PostGRE, hence here is an alternative set of delete statements:

delete FROM ofMessageArchive
WHERE ofMessageArchive.conversationID in (select ofConversation.conversationID from ofConversation where ofConversation.startDate <= 1345420800000)

delete FROM ofConParticipant
WHERE ofConParticipant.conversationID in (select ofConversation.conversationID from ofConversation where ofConversation.startDate <= 1345420800000)

delete FROM ofConversation
WHERE ofConversation.conversationID in (select ofConversation.conversationID from ofConversation where ofConversation.startDate <= 1345420800000)

The ones below work well on MySQL:

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

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 ?

There is no documented way to delete the information from the embedded DB.

Dieses Dokument wurde aus folgender Diskussion erzeugt: Monitoring : Clearing Chat Archive History

use /opt/openfire/bin/embedded-db-viewer.sh (can need fix some path in embedded-db-viewer.rc)

it’s start GUI for HSQLDB and you can execute sql queries

Hi,

I have done the above process of deleting a message from openfire from the

database but then also user can be able read the messages of openfire.

please help me to resolve this problem.

Thanks in Advance