Hello everyone,
I thought Monitoring Service will do this for me but it seems like it is storing everything in a text or xml file. For security reasons I need to store all the conversations on SQL and from openfire consule SQL is already configured. Please advice.
Thanks,
Mike
The monitoring service stores its data in the database. If you have XML file conversation logs, you have other logging options enabled. Spark has its own XML history. Openfire has its own XML logs as well via Message Audit Policy under Server Settings. Disable these if you like.
Thank you for your post.
I’m a little lost here. I’m attaching a screen shot for my Archiving options. All I need to do is to store all the conversations on my SQL table.
Thanks,
Mike
Openfire Admin Console_ Archive Settings1.pdf (461990 Bytes)
That are the settings for the monitoring plugin, to disable the build in audit log of openfire goto Server -> Server Settings -> Message Audit Policy.
Then click on “Disable Message Auditing” and save.
The build in logging should be disable from now on.
That option is already disabled. My question is how can I store conversations to SQL table?
Thanks,
Mike
The ofMucConversationLog table stores room conversations provided that logging of conversations is enabled in the admin interface.
I don’t believe that there is an option to log personal conversations… thankfully
You already do, the settings show in your PDF shows that the monitoring plugin logs the conversations.
How can I enable this feature as my ofMucConversationLog table is currently empty.
Thanks,
Mike
Logging of 1to1 chats is possible, you just have to enable it.
Even the build in audit logging is able to log privat chats.
But it’s not storing the conversation anywhere in my sql database. I looked through every single table and no conversation in there.
I know it is possible but it doesn’t send these conversation to SQL!
Sorry, you’re right the monitoring plugin comes with scripts in the directory “database”, it looks like a table ofMessageArchive gets created for storing chat transactions. (I am not helping here, sorry I see you checked all of the tables already).
None of my SQL tables has any data for conversation including ofMessageArchive.
Under C:\Program Files\Openfire\plugins\monitoring\database I see import_sqlserver.sql and monitoring_sqlserver.sql.
import_sqlserver.sql has the following content:
TRUNCATE TABLE ofConversation;
INSERT INTO ofConversation
(conversationID, room, isExternal, startDate, lastActivity, messageCount)
SELECT conversationID, room, isExternal, startDate, lastActivity, messageCount
FROM entConversation;
TRUNCATE TABLE ofConParticipant;
INSERT INTO ofConParticipant
(conversationID, joinedDate, leftDate, bareJID, jidResource, nickname)
SELECT conversationID, joinedDate, leftDate, bareJID, jidResource, nickname
FROM entConParticipant;
TRUNCATE TABLE ofMessageArchive;
INSERT INTO ofMessageArchive
(conversationID, fromJID, toJID, sentDate, body)
SELECT conversationID, fromJID, toJID, sentDate, body
FROM entMessageArchive;
TRUNCATE TABLE ofRRDs;
INSERT INTO ofRRDs
(id, updatedDate, bytes)
SELECT id, updatedDate, bytes
FROM entRRDs;
monitoring_sqlserver.sql has the following content:
/* $Revision$ /
/ $Date$ */
INSERT INTO ofVersion (name, version) VALUES (‘monitoring’, 0);
CREATE TABLE ofConversation (
conversationID BIGINT NOT NULL,
room NVARCHAR(1024) NULL,
isExternal TINYINT NOT NULL,
startDate BIGINT NOT NULL,
lastActivity BIGINT NOT NULL,
messageCount INT NOT NULL,
CONSTRAINT ofConversation_pk PRIMARY KEY (conversationID)
);
CREATE INDEX ofConversation_ext_idx ON ofConversation (isExternal);
CREATE INDEX ofConversation_start_idx ON ofConversation (startDate);
CREATE INDEX ofConversation_last_idx ON ofConversation (lastActivity);
CREATE TABLE ofConParticipant (
conversationID BIGINT NOT NULL,
joinedDate BIGINT NOT NULL,
leftDate BIGINT NULL,
bareJID NVARCHAR(255) NOT NULL,
jidResource NVARCHAR(255) NOT NULL,
nickname NVARCHAR(255) NULL
);
CREATE INDEX ofConParticipant_conv_idx ON ofConParticipant (conversationID, bareJID, jidResource, joinedDate);
CREATE INDEX ofConParticipant_jid_idx ON ofConParticipant (bareJID);
CREATE TABLE ofMessageArchive (
conversationID BIGINT NOT NULL,
fromJID NVARCHAR(1024) NOT NULL,
toJID NVARCHAR(1024) NOT NULL,
sentDate BIGINT NOT NULL,
body NTEXT
);
CREATE INDEX ofMessageArchive_con_idx ON ofMessageArchive (conversationID);
CREATE TABLE ofRRDs (
id NVARCHAR(100) NOT NULL,
updatedDate BIGINT NOT NULL,
bytes IMAGE NULL,
CONSTRAINT ofRRDs_pk PRIMARY KEY (id)
);
Thanks,
Mike
Ok so you have the table ofMessageArchive but it is empty?
http://www.igniterealtime.org/projects/openfire/plugins/monitoring/readme.html
Could it be the last paragraph?
“Chat archiving is enabled by default. However, only information about who is communicating and at what time is stored unless chat transcript archiving is enabled. To enable chat transcript archiving or group chat archiving, you will need to log into the admin console and go to:
Server --> Archiving --> Archiving Setting”
I think here’s where I am lost from the Monitoring Service readme:
Log into your database, switch to the Openfire’s database as you configured during setup (you can find this information in conf/openfire.xml if you don’t remember it).
The monitoring plugin stores in the database by default. If it is not there must be some other issue here. What version of Openfire, and what version of the monitoring plugin are you running?
Openfire 3.6.0a and Monitoring Service 1.1.0.
Is this a new install? What flavor of database? Sorry if this in the thread, just call me lazy.
It is a new install. Thx
Hi Mike,
The pdf you posted shows that you’ve enabled Openfire’s auditing feature, which is different than the monitoring plugins message archiving feature. Please see the attached screen capture. Is this the feature you’ve enabled? If so, and you’re not seeing any records are you seeing errors in your log files (visible by logging into the Openfire Admin Console and navigating Server -> Server Manager -> Logs).
Thanks,
Ryan
