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
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 for this demo is a MS Access 2000 database (.mdb file) and it contains 2 tables:
Members table and
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.
|- ||Its the primary key of this table to uniquely identify each record|
|- ||The id of the user from the |
Members table which will be shown in a picture below
|- ||The |
Session.SessionID of each session opened on the site when a user runs for the first time any page in your application.
|- ||The time when the user login on the site.|
|- ||The time when the user logout off the site.|
|- ||Boolean value which is used to tell how many user are online on the site.|
How this script works
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
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")
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=" &_
' 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() & "#)"
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_OnEnd will be executed.
In this procedure you will update the record written in
you will update the
Logout_time and the
offline field to
set conn = Server.CreateObject ("ADODB.Connection")
' 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")
set conn = Nothing
That's all there is for tracking login/logout times. You have these values in the
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).
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.
- 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
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.