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.
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.
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).
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,