Tracking users login/logout times on your site

• Download demo project - 16 Kb

Introduction

Here is a script that track user login/logout times on a website. It's a simple script that I have used on some of the sites I've made. Also with this script you can see how many users are online at your site.

For this you need first a site with a 'login to enter' (member based community site). You also need a database to keep the users and the records of their login/logout times. You also need the global.asa file so you can use the Session_OnEnd event to track the time when Session.Abandon occurs or Session.Timeout expires. That is when a user hit logout or quits your application.

The Database

The database for this demo is a MS Access 2000 database (.mdb file) and it contains 2 tables: Members table and User_LogTimes table. In the Members table I keep the a minimal login information such as: user name, Password, user first name and user last name.

In the picture below you can see the design of the Members table. This table is not relevant for this article but I talked about it so you can understand the SQL queries that will be presented further in the article.

Now take a look over the design of User_LogTime table. In this table we will keep the records of the user login/logout times plus from this table we can get how many users are online on your site.

In the picture below you can see the design of the table.

This is short description of the table fields.

How this script works

login.asp

When a user will login on the site, the script in login.asp will be executed first. That's why I will begin to explain this code first.

With the user and password entered in the login form you will build a query to to get the user's id value from the Members table. That value you will store in a session variable.

' Get the user name and the password from the login form
UserName = Request.Form ("UserName")
Password = Request.Form ("Password")
...
	
...
set conn = Server.CreateObject ("ADODB.Connection")
conn.Open Application("connString")
query = "SELECT Id FROM Members WHERE UserName='" &_
         UserName & "' AND Password='" & Password &_
         "'"
' Get the user id from the database
set rs = conn.Execute (query)
...
	
...		
' set the user id value from the Members table in a session variable
Session("member") = rs("Id")

You have the user id value in the Session("member") variable. Now we have to modify all the records in the User_LogTime for this user that have the offline field set as False (that means he is marked as being online) and set that field to True. The fields changed in this way will not have a LogOut time. Normally this operation should not affect any record in the table. Records with offline set to False may exist when they were not closed properly when the user previously logged out.

Then we will insert a new record in the User_LogTime table with the id of the user, the SessionID of this user and with the time of login.

' Modify all the records from the User_LogTime corresponding to this user.
query = "UPDATE User_LogTime SET offline=True WHERE offline=False AND user_id=" &_
        session("member")
conn.Execute (query)
				
' Insert a new record in the User_LogTime table with the user_id, 
' SessionID and the login time.
query = "INSERT INTO User_LogTime (user_id, SID, Login_Time) "
query = query &_
        "VALUES (" & Session("member") & "," &_
        Session.SessionID & ",#" & now() & "#)"
conn.Execute (query)

global.asa

The code above was called when the Session began and you got the login time and wrote it in the database.

When the user hits logout then Session.Abandon will be called. If the user quit your application without hitting logout then after the session.Timeout expires Session_OnEnd will be executed. In this procedure you will update the record written in login.asp and you will update the Logout_time and the offline field to True.

Sub Session_OnEnd
	set conn = Server.CreateObject ("ADODB.Connection")
	conn.Open Application("connString")

	' Update the record when the user logout and write the logout time 
	' plus it sets the user as OFFLINE.
	query = "UPDATE User_LogTime SET Logout_Time=#" & now() & "#, offline=True "
	query = query & "WHERE offline=False AND SID=" & Session.SessionID &_
                " AND user_id=" & Session("member")
	
	conn.Execute (query)
	conn.Close
	set conn = Nothing	
End Sub

That's all there is for tracking login/logout times. You have these values in the User_LogTimes and you can display them however you want. You can take a look at how I've done this in the project demo (see the 1st pic above).

Online users

Having the offline field in the database makes this very easy. You can make an SQL query like this:

query = "SELECT DISTINCT user_id FROM User_LogTime WHERE offline=False"

Execute that query and the number of records in the recordset will be the number of your online users. Alternatively you can call

query = "SELECT COUNT(*) as NumOnline FROM User_LogTime WHERE offline=False"

To return a recordset with one record and one field ("NumOnline") that has the number of online users.

See the demo application for examples.

Remarks

• when you store SessionID in the database, data type of that field are set always to a number. If you want to compare the value from that field with the Session.SessionID then this will save you for some trouble.
• On PWS on Win95/98 this should work fine but on IIS5 remember that this Session_OnEnd will be run by IWAM_machine and not by IUSR, so set write/modify rights on the database where you will keep the login/logout times.

Happy Programming!!