Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: View Logged Users

    I'm trying to develop a basic user log, just to log when people log in and out of a database. On startup a form is opened that stays open till the database closes. I have the OnOpen event write a record to a table to record log on, and again on the OnClose to record log off. This all works fine and it gets stored in a table like:

    tblUserActivity
    fldID - autogen prim key
    fldOSUserName - string, from Dev Ashish's fOSUserName
    fldMachineName - string, from Dev Ashish's fOSMachineName
    fldActivity - string, either 'Log On' or 'Log Off'
    fldTimeStamp - datetime, using now()

    Again, this all works just fine. But now I'm trying to create a query to show me all users whose most recent Log On is more recent than their most recent Log Off. This is what I have so far:

    Code:
    SELECT tblUserActivity.fldOSUserName, tblUserActivity.fldMachineName, tblUserActivity.fldActivity, tblUserActivity.fldTimeStamp
    FROM tblUserActivity
    WHERE ((SELECT MAX(tblUserActivity.fldTimeStamp) FROM tblUserActivity WHERE (tblUserActivity.fldActivity='Log On')) > (SELECT MAX(tblUserActivity.fldTimeStamp) FROM tblUserActivity WHERE (tblUserActivity.fldActivity='Log Off')));
    But this returns all the records as far as I can tell. I know this system isn't perfect, but I think if I can get the query to work it should be robust enough to handle any hard crashes or anything of that sort (after the user logs in and out properly again). Any ideas what I'm doing wrong with the query?
    Last edited by nckdryr; 06-30-09 at 20:36.
    Me.Geek = True

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im not convinced that that query will return anythign usefull, as it will always return a row for each user

    what you could do is write a row indicating when a user logs on. and update that row when the user logs off. if the row is not updated then defacto there has been a system crash. if you store the logon id as (say) a global variable then the update is straightforward

    you could write the logon function so it checks to see if there is an open logon already, and if so mark that record as a problem and create a new logon row
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    So for anyone who's following this, here's the solution I finally came up with:

    Create the table as outlined in Post #1. Create two queries:
    Code:
    qryUsersLastLogOn
    
    SELECT tblUserActivity.fldOSUserName, tblUserActivity.fldActivity, Max(tblUserActivity.fldTimeStamp) AS LastLogOn
    FROM tblUserActivity
    WHERE tblUserActivity.fldActivity = 'Log On'
    GROUP BY tblUserActivity.fldOSUserName, tblUserActivity.fldActivity
    ORDER BY Max(tblUserActivity.fldTimeStamp);
    Code:
    qryUsersLastLogOff
    
    SELECT tblUserActivity.fldOSUserName, tblUserActivity.fldActivity, Max(tblUserActivity.fldTimeStamp) AS LastLogOff
    FROM tblUserActivity
    WHERE tblUserActivity.fldActivity = 'Log Off'
    GROUP BY tblUserActivity.fldOSUserName, tblUserActivity.fldActivity
    ORDER BY Max(tblUserActivity.fldTimeStamp);
    Then create another query based off of these two:
    Code:
    qryUsersActive
    
    SELECT qryUsersLastLogOn.fldOSUserName, qryUsersLastLogOn.LastLogOn
    FROM qryUsersLastLogOn LEFT JOIN qryUsersLastLogOff ON qryUsersLastLogOn.fldOSUserName = qryUsersLastLogOff.fldOSUserName
    WHERE (IIF(IsNull(qryUsersLastLogOn.LastLogOn),0,qryUsersLastLogOn.LastLogOn)) > (IIF(IsNull(qryUsersLastLogOff.LastLogOff),0,qryUsersLastLogOff.LastLogOff));
    Run the third query to get the active users.

    I think this could probably be condensed into a single query with sub-queries, but this works at least.


    To answer your question healdem, this query only returns records where the last Log On is more recent than the last Log Off (at least based on my limited testing). This also gives the added benefit of having a permanent record of who logged on to a database at what time and from what machine. You can also use it to track other vital information by having a different entry in the Activity field of the table.

    Me.Geek = True

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by healdem
    what you could do is write a row indicating when a user logs on. and update that row when the user logs off. if the row is not updated then defacto there has been a system crash. if you store the logon id as (say) a global variable then the update is straightforward

    you could write the logon function so it checks to see if there is an open logon already, and if so mark that record as a problem and create a new logon row
    Oh yea, so if a User does have a crash and it doesn't log the Log Off properly, this system should be able to handle this once the User Logs On and the Logs Off properly again.
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •