User Reporting

I have had several managers come to me and ask on reports for user login/logoff activity. I know there isn’t an easy way to get a report (not like the archiving reports), but is there something I can do with the MySQL database to get the user login & logoff timestamps?

Anyone have anything to say on the topic?


I dont think you will get much help about this. The only thing i can tell, that User Summary page is showing Last Logout values, so it is storing somewhere in the database. If you go to any user’s Session Details, you will find Session Created time and date. So this is also stored somewhere in the database. So, you will have to pull that data from the database. SQL selects, maybe some php to format this data into readable information.

Hmm, I figured it was in the DB, but when I did a quick look I didn’t find anything that helpful. We are using MySQL, is there a way to read the DB in MS Access to write a custom report that pulls out the required data? I have done this with MS SQL, but that is it.

modulok wrote:

Hmm, I figured it was in the DB, but when I did a quick look I didn’t find anything that helpful.
I think in Java (and probably not only in java) dates and times are stored in a milliseconds format (from some old date like 1960). So these entries can look like 456465661233213132132. Dont know about the Access thing.

Hi All,

The reason the time is stored in milliseconds in the database doesn’t really have to do with Java rather its for consistency across the various databases that Openfire supports. Not all databases store dates in exactly the same way so by storing “dates” as milliseconds it removes the inconsistencies (of course using a persistence framework like Hibernate would eliminate that issue but that’s a different topic ).

As for the reporting question, it would probably be easier if you didn’t use Access at all and reported directly from the tables via a reporting tool like Crystal Reports or Jasper Reports. The table you’ll want to focus on is jivePresence. Remember that you’ll have to compensate for users who forget to sign out (or in).

Good luck,


OK, I checked into the JivePresense table and exported what I found. These just look like the users that are currently offline. There are a few users that are not with the company anymore, (their Active Directory account is removed) yet they are still showing the offline timestamp.

username     offlinePresence     offlineDate
cantwm          001250630180359
adamsk          001250623222325
pagana          001250693413538
peterd          001250693840444
campbl          001231537811304
reedc          001239992401125
gordoj          001250288338372
cerram          001237286621202
warhos          001248463973992
dunnw          001226958586746
dantem          001250635946968
silvaj          001250676884113
morenr          001250623143317
henegl          001239915528818
lockht          001247766478634
czapls          001250630905288
kukler          001250626154927
taylor          001250626478568
lirang          001244742339276
mktrain          001246550474588
stpclerk          001231431470171
weissm          001250629875769
riverl          001250675020827
seitzc          001243543752431
ridinr          001250274386971
weissd          001245186493452
raor          001232568321928
bickej          001250546133129
raffec          001231507299861
lareal          001249675620890
albana          001250625671336
jerezg          001231532219659
vasque          001239991557288
kanial          001240247295436
ambrub          001250350151078
sanzaa          001250683693972
minerj          001250691696922
trokat          001250196492168

Oops, sorry, I hadn’t finished my morning cup of coffee before writing what I did above so I didn’t think through all of what you were wanting to do. Openfire doesn’t track a users historical sign in/out times so unfortunately you won’t be able to write a report with the information you have. The best solution would probably be to develop a plugin that records each time a user logs in/out and then write a report using that data.

Sorry again for misleading you,