How to get a top user list?

I am using openfire 3.5.1 on a linux server.

The statistics page about the server is nice, but I am looking for statistics about users. Which are the top users by messages or by size (who use the server to transfer files most); Who send more messages ? Who receives more messages ?

Anyone knows a way to get those answers ? At least, how to find who is the top user in number of messages ?

Would be nice if I could select a date range to get those numbers too.

Any ideas ?

I could write simple programs using scripts in perl/php/bash.

Here’s a real quick SQL query I whipped up:

openfire=# SELECT SUM(messagecount) AS total_messagecount, barejid FROM(

openfire(# SELECT barejid, messagecount FROM

openfire(# ofconparticipant JOIN ofconversation

openfire(# ON ofconparticipant.conversationid = ofconversation.conversationid

openfire(# ORDER BY messagecount DESC

openfire(# ) AS fart

openfire-# GROUP BY barejid

openfire-# ORDER BY total_messagecount DESC

openfire-# LIMIT 3;

It selects the top three jids by total count of messages. I’m no SQL expert, so if that query makes your DB server catch on fire … well, you’ve been warned.

Oh, that’s on PostgreSQL.

Cool ! very cool wellwhoopdedooo ! Thanks !

But how to get this using the embebbed database ?

We are a small company with just 50 employees and our openfire server run nice just using the embebbed java database.

May be I must ask How can I access the embebbed java to issue SQL commands ?

anyway, your answer put me on the right trail. thanks.

There was a stats plugin developed by (@Ryan Graham) a while ago. It was a commercial one. Now i dont see info about this on their site. Probably it’s not developed anymore. But you can try contacting him.

May be I must ask How can I access the embebbed java to issue SQL commands ?


you need to shutdown Openfire before you can access the database. In the openfire/bin/extra directory is the embedded-db viewer which allows you to access the database while Openfire is not running.


Thanks for the hint. I just sent an e-mail to ryan asking for help or directions.

I hope he could help me.