0

I'm trying to code (in an efficient way) a log to count how many hours a user spent on the system.

The only reliable information is the login date and time. User's often just close the window and don't log off.

What I've been doing so far is to use a jQuery call every 5 or 10 seconds to a specific endpoint to just record: user's id and the current datetime. But this is, of course, very expensive. A lot of HTTP requests (imagine for 8 hours a day how many requests) and a huge (HUGE) table.

Is there a more efficient way to count this information and present a summary of worked hours by the end of the month?

I'm currently using MySQL to log this (another table inside the same database) but I can use a different database if it's a better solution.

racquad
  • 11
  • 1
  • You're assuming that just because people have the browser window open, they are actually working... I don't know what these users are meant to be doing, but maybe there's a way to measure some actual productive output, rather than just encouraging presenteeism? For instance if you're logged onto your system, maybe they are completing some records, or doing some other work they have to save into the website? There might be a way to measure quantity / quality of that instead of just counting hours, which doesn't really tell you how effective someone is, and can also, as I've said, be gamed. – ADyson Aug 21 '20 at 09:14
  • It is not necessary to store each ping as separate row in DB. Just increase user spent time on each request. – Slava Rozhnev Aug 21 '20 at 09:14
  • "We're not robots". Ok I know that. I know that the requirement of logging hours to check for productivity does not sound reasonable. Again, forget for a while about the business model of the company and lets focus on the computing issue.Is there a better way to count logged hours rather than I'm doing (pinging the server every n seconds)? If I use a different database just for this purpose will I have any gain? – racquad Aug 21 '20 at 10:42
  • 1
    Maybe you could use WebSocket connections? With WebSockets you will know when the user connected and disconnected to the socket. From there you could go on. Of course it comes with some limitations. – Eddi Aug 21 '20 at 11:33
  • "If I use a different database just for this purpose will I have any gain"...unlikely. Restructuring the tables so it just updates a total value for that user (as a total for that day/week/whatever) would be better than logging every single ajax request, though. (For audit you _could_ also use some sort of NoSQL store to just create a raw log of requests). Also, consider whether you really need to poll every 10 seconds? How accurate does it need to be? If it's just hours worked, then surely you could round it to the nearest hour/half hour/15 minutes? Or are they literally paid by the second? – ADyson Aug 21 '20 at 12:56
  • But also the websockets suggestion is a reasonable one - although again it has an infrastructure cost because you have to maintain an open connection to every logged-on user. It would allow a greater degree of accuracy though - assuming that's actually necessary (see above discussion about rounding). There's also a slight practical downside in that PHP's existing implementations of websockets (e.g. ratchet and one or two others) are not particularly easy to work with. You could consider trying to integrate with something like Azure SignalR instead. – ADyson Aug 21 '20 at 12:59
  • Maybe you can also use javascript `onunload` & `onbeforeunload` function. I have only heard that these functions are not that reliable, but I'm not 100% sure about that https://stackoverflow.com/questions/3888902/detect-browser-or-tab-closing – Baracuda078 Aug 21 '20 at 13:18
  • @Baracuda078 you're right they are not 100% reliable - not ideal for this kind of scenario. – ADyson Aug 21 '20 at 13:20
  • @ADyson Maybe you're right. Use a NoSQL to store raw data and use MySQL just for summary. When someone reloads a page or jump to a new one inside the system this may cause a gap. The sum of all gaps may cause a difference of up to 1/2 hour. Keeping ping delay low brings the sum of gaps down but with a big impact on connection and database size too. It's really silly and it sounds like people are getting paid per sec. I also agree that there are (plenty of) other ways to measure productivity better than logged hours. Unfortunately this is not up to me. – racquad Aug 27 '20 at 08:17

0 Answers0