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?
Bump
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,
Ryan
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,
Ryan