powered by Jive Software

Audit logs in database

I have read some of the old posts and I noticed this one:

http://www.jivesoftware.org/forums/thread.jspa?forumID=40&threadID=10402&message ID=69736&#69736

It mentions that a planned feature in Messenger 1.1 was database auditing/searching/etc of logged data via the admin web UI. Are there any plans to implement this? I would very much enjoy having this capability, or even just an automatic facility that would directly log the chat logs to a database vs having to manually import log files. Outside of that (and a few gaim issues that are resolved by making the Windows users use the exodus client) I really enjoy this product. Thank you.

William,

Feature goals logged against 1.1 are pretty out of date. However, I agree that it’‘s still a good idea. The toughest problem we have with implementing this feature is coming up with a database schema that’'s useful. Something simple would be (not real column names):

toJID, fromJID, date, packetType, packetContents

Do you have any ideas around this?

Regards,

Matt

Matt,

I am sorry for not replying sooner.

As far as a usable schema, you suggested:

“toJID, fromJID, date, packetType, packetContents”

as a rough layout.

I would think time/date should be separate columns to make date searches easier or change the date format to seconds from 1970 (such as unix does it).

My view though is that if the schema isn’‘t exactly what people want, it can always be adjusted in a later version. I know that isn’‘t perfect, but I don’‘t think people will know exactly how they want things until they actually get to log to the database and see what works and doesn’'t.

But as long as the data goes to the database and is searchable, reports would be able to easily be generated that cannot easily be generated now on chat activity which is what businesses like having available.

William,

Yep, Java format for date is the standard thing we use. I’'ve filed this as a feature request for Jive Messenger 2.2 – JM-149.

Regards,

Matt

Admittedly, this would take up a fair amount of space, especially on a large enough user base.

Maybe in the future there will be a way via the interface to either “make a new table monthly” on an automatic basis or something else. Regardless, automatically logging to a db would be a huge benefit. Thank you for adding it to jira.

Hi there,

my intention was to give each jive user a list of his chat and message activities and

because I’m not good in programming Java I found “AutoHotkey” (http://www.autohotkey.com/), which is a open source software for automating repetitive tasks. AHK is a pretty powerful script language and the AHK-files can even be compiled to a EXE-file.

I’m testing a script which grabs the new content of the JiveMessenger Log Files (e.g. jive.audit-0.log) and parses the XML strings. The output is a csv-file named (JIVE-ID).CSV with “Date”, “from JID”, “to-JID”, “type”, “subject” and “message”. The AHK-programm is running permantly and chats and messages are logged for each user in his own csv-file. I’m planing to gererate user spezific INI-files with vCard data from the logfile entries and with that I want to send the csv-files continuous to the JiveUsers via email. Beside that I’m planing to make the AHK-software react on certain words in a subject of a message (e.g. SMS or EMAIL) and then send the message to the cell phone of the recipient, if phone number is available in the vCard, or email the message. As long as there is no loging into database this works fine for me.

When my script is testet and works stable I can translate it into English (now it’s in German) so everybody can use it and make modifications on it. Right now it’'s about 100 lines of script.

Achim

Achim,

Sounds cool! Let us know when you have something to demo.

Regards,

Matt

Working on parsing the audit logs.

What format is the timestamp in the audit logs in?

“Sat Feb 19 03:50:30 PST 2005”

MS SQL server can not cast/convert it to datetime, and I can’'t find any useful reference to this timestamp format.

I’'dlike to not have to write my own function to convert it into a format MS SQL will accept.

A description of the date format:

dow mon dd hh:mm:ss zzz yyyy

where:

  • dow is the day of the week (Sun, Mon, Tue, Wed, Thu, Fri, Sat).

  • mon is the month (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec).

  • dd is the day of the month (01 through 31), as two decimal digits.

  • hh is the hour of the day (00 through 23), as two decimal digits.

  • mm is the minute within the hour (00 through 59), as two decimal digits.

  • ss is the second within the minute (00 through 61, as two decimal digits.

  • zzz is the time zone (and may reflect daylight saving time). Standard time zone abbreviations include those recognized by the method parse. If time zone information is not available, then zzz is empty - that is, it consists of no characters at all.

  • yyyy is the year, as four decimal digits.

Thanks for the reply, though I understand the layout/logic of the time format.

I meant to ask for the name of the format or standard (ISO, RFC, etc…) that defines/describes it so i could track down a function/procedure that can convert it to something MS SQL can understand. If not I’‘ll be writing my own method to convert it, and I’'d rather go with an existed one is possible.

Unfortunately, i don’‘t know if it’‘s anything standard. That might be something we should change so that it’'s easier for people to use the dates in other applications. Do you have any suggestions for format?

Regards,

Matt

ODBC canocial - “yyyy-mm-dd hh:mm:ss” (24hour time format)

This would keep the same level of time accuracy and is supported by most (if not all) databases.

Given that a message logging database will no doubt explode in size, I’‘d like to suggest storing date and time data the DBMS’'s native TIMESTAMP format. For MySQL and PostgreSQL at least its possible to insert and retrieve a unix timestamp if time-zone support were an issue.

As for schema suggestions, I’'d appreciate a field recording how long a user spent typing a message. Perhaps even a field to track the total time they kept a message window in focus.

Agreed 100% re TIMESTAMP.

Perhaps even a field to track the total time they kept a message window in focus.

How would JM know this?

I’'ve made a test case in mysql for the audit log and a VERY simple interface in PHP for listing the audit log events for a particular user.

If you want I can post the DB schema (it’'s based on the schema discussed here) and the PHP source code…

I don’‘t want to hurry Thomas as I think he must be with a lot of work, but I need the audit log in mysql cause it’‘s a NEED for my special case. Do you know if it will be finished soon? I can give a little help if it’'s needed…

Thanks for the product, it’'s incredible

Now I’‘m making a java xml_audit_log to sql converter… (using jdom). If it’'s useful for somebody else I can post the source code…

Another one. I finished (for now) the tool for importing the audit data to the SQL. I’'ve found that would be better to have the JID separated in fields like: user/domain/resource

My db now is like this:

fromUser, fromDomain, fromResource, toUser, toDomain, toResource, date_, packetType, content

The PHP I made before worked fine, showing the last X entries for some user…

So for now I can do a cron importing from the XML audit log to the mysql. (I made a unique index in the SQL so each entry is in the DB only once).

If you want the sources, contact me.

I want it. How to find u? Thanks!

I would be interested in seeing this. I am trying to figure out how to get these XML logs into my SQL database and facing a number of the issues I see out here.

Charles