Login/Logout

[Guest]

I have a log file that records users logging in and out of an application.
Occassionally, the application crashes and I don't get a logout entry in the
log file.
I want to write a query that will show me all of the logins, with their
associated logouts, and where there is no associated logout, then I want to
retrieve a null.

My data looks similiar to:

userid event event_time
aaaaa Log in 2/27/06 15:05:30
bbbbb Log in 2/27/06 15:06:25
bbbbb Log out 2/27/06 16:18:03
aaaaa Log in 2/28/06 09:00:00
aaaaa Log out 2/28/06 12:28:00

What I would like to get back from my query is:

userid Log in Log out
aaaa 2/27/06 15:05:30
bbbb 2/27/06 15:06:25 2/27/06 16:18:03
aaaa 2/28.06 09:00:00 2/28/06 12:28:00

Thanks for your help

 

[Duane Hookom]

It's too bad you can't specify that a log in and log out "pair" will always
be on the same date.

 

[Guest]

Duane,

Actually, the application is being used internationally, so date/times are
Zulu time, so they frequently will span multiple dates.

Dale

 

[Guest]

I just ran into this same problem. Maybe my solution can be helpful to you.
In my case I am using a table called "History" (it logs more than just log-in
and log-out). To simplify things, let's say it has PersonID, Timestamp, and
ActionID where ActionID = 1 is LogIn and ActionID = 2 is LogOut. I then
created a report that would show for each user, a sorted history of their
logins and logouts.

The basic idea is to match each login time to the minimum logout time that
is greater than the login time (Date/Time values in Access are treated as
doubles when using min() and logical operators).

The report consists of two queries. The sub-query consists of joining the
history table to itself on PersonID; I renamed the tables HistoryIn and
HistoryOut. In the where clause I have HistoryIn.ActionID = 1 and
HistoryOut.ActionID = 2. In other words, the HistoryIn table has all login
times and the HistoryOut table has all log out times. The next clause is for
HistoryIn.Timestamp <= HistoryOut.Timestamp. This clause matches all login
times of a person to all logout times that occured after that particular
login times.

The only trick left is to make another query that groups on login time and
matches it with the minimum logout time from above (the sub-query described
above matches ALL possible logout times for a given login time; the correct
logout time per login time is the earliest one).

If that was confusing, maybe the SQL won't be (or at least you can adapt it
easier):

SELECT TimeStamps.PersonID, TimeStamps.HistoryIn.Timestamp AS TimeIn,
Min(TimeStamps.HistoryOut.Timestamp) AS TimeOut, [TimeOut]-[TimeIn] AS
TotalTime, Format([TotalTime],"hh:nn:ss") AS TotalTimeFormatted
FROM

(SELECT HistoryIn.PersonID, HistoryIn.Timestamp, HistoryOut.Timestamp
FROM History AS HistoryIn INNER JOIN History AS HistoryOut ON
(HistoryIn.PersonID = HistoryOut.PersonID)
WHERE HistoryIn.ActionID=1 AND HistoryOut.ActionID=2 AND
HistoryIn.Timestamp<=HistoryOut.Timestamp
) AS TimeStamps
GROUP BY TimeStamps.PersonID, TimeStamps.HistoryIn.Timestamp;


Regards,
Robbie

 

[John Spencer]

UNTESTED SQL

I'm betting that this is as slow as can be if it runs and doesn't blow up.

SELECT USERID, Event_Time as LoginTime,
(SELECT Min(T.Event_Time)
FROM TableA as T
WHERE T.UserID = TableA.UserID
AND T.Event="Log Out"
AND T.Event_Time > TableA.Event_Time
AND T.Event_Time <
NZ((SELECT Min(X.Event_Time)
FROM TableA as X
WHERE X.USERID = TableA.UserID
AND X.Event = "Log In"
AND X.Event_time > TableA.Event_Time),#1/1/9999#) ) as LogOutTime
FROM TableA
WHERE TableA.Event = "Log In"

 

[Guest]

That untested SQL looks pretty close to the (partially tested) SQL I posted
earlier with the exception that John's uses a subquery in the WHERE clause
and mine does one in the FROM clause. I'm not positive (and am interested in
feedback), but I think doing it how I did in the WHERE clause is more
efficient because the query need only be evaluated once (could be wrong).

Robbie

 

[Dale Fye]

Thanks, guys.

I finally created four queries to handle this, but when I look at your code
John, I see bits and pieces of each of the four queries mentioned below.

1. The first one just identified all of the logins
2. The second joins two copies of Q1 to map each user login with their next
login
3. The third identifies all of the logouts
4. The last joins #2 to #3 to identify whether there is a logout between
the two consecutive logins. It looks something like the following

SELECT Q2.UserID, Q2.Login, Q3.Logout
FROM Q2 Left Join Q3
ON Q2.Userid = Q3.UserID
AND Q2.Login < Q3.Logout
AND Q2.NextLogin > Q3.Logout

Surprisingly, this runs relatively quickly (15 seconds) against a recordset
containing about 300,000 log entries.