Monitoring Service Logs

Hello Everyone,

My issue is pretty simple - I have the Monitoring plugin installed and wokring and have enabled archiving of group chats - However, I dont know how to access the database where the messages are being archived.

i relaise that there are the Statistics and Archiving tabs in the Openfire admin console - however, I need to continously keep an eye on the size of the log file and avoid having it grow too alrge. But where is this log file where the chats are being logged\archived? I am using Openfire 3.5.1 and have it integrated with Active Directory\LDAP for user authentication.

In the database tab I notices that I am connecting to a HSQLDB with following properties:

Database and Version:

HSQL Database Engine

1.8.0

JDBC Driver:

HSQL Database Engine Driver

JDBC Driver Version:

1.8.0

DB Connection URL:

jdbc:hsqldb:/opt/openfire/embedded-db/openfire

DB User:

SA

Transaction Support:

Yes

Transaction Isolation Level:

TRANSACTION_READ_COMMITTED

Supports multiple connections

open at once:

Yes

In read-only mode:

No

Now I use a tool called DBVisulaiser - (http://www.minq.se/products/dbvis/download/install.jsp) to view the HSQLDB databse - however I dont see any table that has the messages logged into - i just see SYSTEM_TABLES.

Any clue whats going on ??? - Where is this log where the archives are being kept?? and howcan I view my tables for the databse being used???

Any Help would be appreciatted

Have just tested that. With embedded-db everything is stored in /embedded-db directory. As the server is online openfire.log is growing while conversations are saving. After some times openfire.log contant is moved to openfire.script file (the main embedded database file). You can check for such strings INSERT INTO OFMESSAGEARCHIVE VALUES, INSERT INTO OFRRDS VALUES. I mean you can open that file with a text editor, as it’s not a normal database nad is a simple text file containing insert commands for creating database on the fly. If you want more controlling you should consider switching to normal database backend (MySQL, etc.).

Figured this one out on my own - posting it so others can benefit from this knowledge

instead of using the DBVisualiser tool - I went ahead and used the embdedded-db-viewer that comes along with Openfire in its resources directoy. This tool was ismple to use and I was capable of looking into all the tables. the tables that had the logs were ofMessageArchive and ofConversations. through this tool i was able to excute SQL Queries to delete rows that were no longer needed or ones that I have archived somewhere else.

This should be inbuilt into the opfire console - its a pain to connect to the DB through the viewer ( as this requires me to shutdown the openfire server!!!) and thendeleting items. Hopefully, the plugin developer will implement this.

Sparkster wrote:

This should be inbuilt into the opfire console - its a pain to connect to the DB through the viewer ( as this requires me to shutdown the openfire server!!!) and thendeleting items. Hopefully, the plugin developer will implement this.

This was already mentioned in one blog entrie’s comments by @Gaston Dombiak, that there was no intention to create option to delete logs. The main purpose of message archiving is auditing, and auditing should be archived.

The archiving feature was done mainly for auditing purposes. Audited information should not be deleted but instead moved to another place to store it. >Anyway, if you want to delete “old” archived data we can provide you some SQL statements. The info is store in 2-3 tables so should be quite easy to >delete.

– Gato

1 Like

Hi Wroot,

Can u please inform me, how many days of message history records are store in the ofMessageArchive table?

I don’t know. But i suppose it should store everything without date limits.