Solved Determine time of activity. (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 28, 2001
Messages
27,001
When I had this same goal and this same kind of problem, I quickly realized that I needed a couple of facts. One of them was whether it was EVER reasonable to log in to the DB twice from two different terminals as the same user AND from a separate window to allow two simultaneous sessions from the same terminal. I got permission from the security guy to say "NO" to both questions. My login table included username and computername from the session login and also had session start date/time and end date/time - plus the "Abnormal" flag. (If that seems a bit strict, remember I was working for the U.S. Navy at the time. Their idea of security includes some things I wouldn't have guessed.)

That table enabled me to open a recordset to the session table to find the session record with the highest start date/time (before I wrote the current record) for the username logging in again. Relatively easy. If the record had an end date/time, I was done. If not, I updated that record to (a) put the current date/time as "END" for that session and (b) set a flag in the record that said "Abnormally terminated." Once that record was done, I could then enter a new session. I made it a point to, after the fact, capture the session number (which was an autonumber for that session table.) That way, if the user was exiting normally, in the dispatcher form's Close routine I could write a simple

"UPDATE SESSIONS SET ENDSESS= Now() WHERE SESSID = " & Me.SessionID & ";"

That way I could count sessions, abnormal sessions, and the duration of normal sessions easily. There was just about NEVER a chance to capture the correct session end for an aborted session, so the question was what to do about it. Therefore, by having a way to at least know the difference between orderly and disorderly session terminations, I had very few dangling sessions. I ALSO had a supervisor's maintenance tool that I ran during scheduled maintenance when the DB was still "officially down" and thus could not be logged in. It looked for sessions with no end date/time and immediately capped those off, too.

I don't know if that will help, but it was how I approached the problem.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:15
Joined
Aug 30, 2003
Messages
36,118
My plan is to store the Logon and Logoff as TempVars and then write one "Insert Into" statement to the table. That is unless of course someone convinces me that the idea is flawed...

That still leaves you vulnerable to the issue Colin described, "where a user doesn't logout due to e.g. an illegal shut down or a power failure." In this case you wouldn't have an orphaned login, but no record at all of activity. I suppose that could be a preference in some situations, but I wouldn't go this way. I did what Pat described.
 

isladogs

MVP / VIP
Local time
Today, 23:15
Joined
Jan 14, 2017
Messages
18,186
My schools apps included code which allowed program developers and admins to check which items each person used during each login session together with the time that item/control was last touched. This was mainly used for several purposes
1. Prioritise future development priorities
2. Manage an audit of error logging...again related to development priorities
3. Manage forced logouts after a specified period of inactivity
However it had one further use - to determine an approximate logout time for sessions terminated abnormally. This was taken to be the latest activity time in that session. The missing logout times were plugged as one small part of the automated maintenance update that ran each night at around 03:00
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:15
Joined
Feb 28, 2001
Messages
27,001
The discussion about normalization is at least partly obscured by the question of "What are you storing?"

If you are storing events which can be either login or logout or other kinds of events, that table structure in post #1 works.

If you segregate login and logout from other events and put them in a separate Session table, you are storing sessions, not events, so the session isn't finished until you toss in the end date/time. Normalization DOES depend on what you are normalizing. In this case, the attributes of the session would include end date/time because that IS an attribute of a session.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:15
Joined
Apr 27, 2015
Messages
6,286
I read this article years ago, found it too geeky for me to understand and moved on...

DBG's solution gave me real world application of it and the light bulb came on. Took awhile but what else have I got to do??

Great stuff, appreciate it!
 

Users who are viewing this thread

Top Bottom