Timing Browsing Session

[equuelus]

Greetings..

Could anyone here tell me whether is it possible to time how long a user browse into a website.. if it is.. how..?

thanks in advance.

[harsoni]

may be u can use session info to get the time also use session_onstart and session_onend to get the time diff.
u need to include these function global.asa file..I hope this is usefull...

Sonia

[equuelus]

sonia, thanks for your reply.. unfortunately it wasn't too helpful though..

emm...what i'm trying to do is time on the very moment a user logs in... meaning a user will have to log in first before the timing function only then activates.

any ideas...

thank you..

[Active]

This is an Article from ASPTODAY.com before they went greedy and money minded.

The article is still there at
http://www.asptoday.com/articles/19991209.htm

But you have to be a Member to access it.

Here is just a copy of it :(Stripped from HTML)

Tracking Client Behaviour on Your Website
Not so long ago my boss discovered a sudden thirst for knowledge about the visitors coming to our wonderful and smoothly running business Web site: he wanted to know who was coming to the site, and how long they were staying there. My instinct was to check our IIS accessed log file, but it really didn't tell me all I wanted to know. And so I set about writing the code to collect the information I wanted.

Actually, this user information is part of the essential business of the Web server administrator, but prior to Microsoft's development of ASP technologies, it was hard, if not well nigh impossible, to trace the browser though a web site. And unfortunately, even the release of the Global.asa documentation has not clarified it's proper use for an awful lot of developers: only a few papers discuss using Global.asa to create a count of active users. So here I'm going to demonstrate a simple technique for using ASP's Global.asa together with SQL Server's stored procedure to track the browser's activity.

My Application Description and Environment
To test this application, you're going to need:

Windows NT 4.0 with IIS Server 4.0
SQL Server 7.0
And this is how the application is built up:



These are the files that you'll find discussed in this article. With a little adaptation, you can them yourself.

Global.asa - this file goes in your site's root directory, and you need to specify your SQL Server, UID, PWD, and Database to make it work.
WebSiteUsers.asp - the main asp file for demo, you need nothing to modify this file.
OpenDatabase.inc - including file for open connect to SQL Server Database. You need to specify your SQL Server, UID, PWD, and Database to make it work.
SessionOnStart.sql - stored procedure, create where your database resides.
SessionOnEnd.sql - stored procedure, create where your database resides.
SessionOnLogOff.sql - stored procedure, create where your database resides.
SessionOnProcess.sql - stored procedure, create where your database resides.
WebSiteUsers.sql - table, create where your database resides.
Global.asa - Begin here!
So, to begin at the beginning, this is Global.asa


'Global.asa
< SCRIPT LANGUAGE=VBScript RUNAT=Server >
Sub Session_OnStart
Session.Timeout=10

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Driver={SQL Server}; Server=MySQLServer;UID=MyUserID;PWD=MyPassword;Database=MyDatabase"

Set cm=Server.Createobject("ADODB.Command")
Set objRec = Server.Createobject("ADODB.Recordset")
Set cm.ActiveConnection = cn

cm.Commandtext="Execute SessionOnStart '" & Request.ServerVariables("REMOTE_ADDR") & "','" & Session.SessionID & "'"
cm.Execute
Set cm=Nothing
cn.Close
End Sub

Sub Session_OnEnd
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Driver={SQL Server}; Server=MySQLServer;UID=MyUserID;PWD=MyPassword;Database=MyDatabase"
Set cm=Server.Createobject("ADODB.Command")
Set objRec = Server.Createobject("ADODB.Recordset")
Set cm.ActiveConnection = cn

cm.Commandtext="Execute SessionOnEnd '" & session.SessionID & "','" & session.timeout & "'"
cm.Execute
Set cm=Nothing
cn.Close
End Sub
< /SCRIPT >

There are only use two session object events in use here: Session_OnStart and Session_OnEnd . Your IIS Web server automatically creates a Session object when a client request a page that does not already has a session - the Session_OnStart event occurs when the server creates a new session. The Session_OnEnd event occurs when a session is abandoned by call the Abandon method or the Session.Timeout . It should be pointed out that the Session_OnEnd events do NOT occur instantaneously, when the client leaves the site or shuts down the browser, but waits until the Session.TimeOut , which by default it set is 20 minutes. This is because the server and the client are not in constant communication: or in another words, the server dos not know when the client leaves your site or shuts down the browser.

This is a common source of confusion for both users and ASP programmers, and it's probably fair to say that most programmers don't use it correctly. Of course, if you call the Abandon method explicitly in your ASP page, say LogOff.ASP , it will destroy the Session object and releases the resources immediately. SQL Server stored procedure SessionOnStart will be called with parameter REMOTE_ADDR and SessionID when a session is started. SessionOnEnd stored procedure will be execute with the SessionID parameter, and timeout when session event Session_OnEnd on fires. I'll explain each function later.

OpenDatabase.inc - ASP inc file.
There are only two ways to manage code sharing under ASP: include files, and server-side components. Include file OpenDatebase.inc sets a shared connection to your SQL Server database that you can include into any other ASP file.

'OpenDatabase.inc
<%
Set cn = Server.CreateObject("ADODB.Connection")
Cn.Open "Driver={SQL Server}; Server=MySQLServer;UID=MyUserID;PWD=MyPassword;Database=MyDatabase"
Set cm = Server.Createobject("ADODB.Command")
Set objRec = Server.Createobject("ADODB.Recordset")
Set cm.ActiveConnection = cn
%>

SQL Server table and Stored Procedure
This code creates a table for WebSiteUsers in SQL Server 7:

'WebSiteUsers.sql
CREATE TABLE [dbo].[WebSiteUsers] (
[IP_Address] [varchar] (15) NOT NULL ,
[DateTime_Accessed] [datetime] NOT NULL ,
[DateTime_Exited] [datetime] NOT NULL ,
[Active] [char] (1) NOT NULL ,
[Session_ID] [varchar] (15) NOT NULL ,
[mi:ss] [varchar] (6) NULL
)

IP_Address refers to the user’s IP; DateTime_Accessed and DateTime_Exited encapsulates when the user came to your site and left it again; column Active = 'Y' or 'N' means active or not active respectively; Session_ID stores the user’s Session_ID ; [mi:ss] indicates the minutes and seconds of access time =DateTime_Exited-DateTime_Accessed . We also create four stored procedures in our SQL Server Database.

'SessionOnStart.sql
CREATE PROCEDURE SessionOnStart (
@IP varchar(15),
@SessionID varchar(15)
)
AS
Declare @AccessDate as datetime,
@ActiveSessionID as varchar(15)

Select @AccessDate=getdate()
Select @ActiveSessionID=""
Select @ActiveSessionID=Session_ID From WebSiteUsers Where Session_ID=@SessionID And Active ='Y'
If @ActiveSessionID<>@SessionID
BEGIN
Insert Into WebSiteUsers (IP_Address,DateTime_Accessed,DateTime_Exited,Active,Session_ID,[mi:ss])
Values (@IP, @AccessDate, @AccessDate, 'Y', @SessionID,'0:0')
END

This procedure is called when a new session is started. The user's information is entered ( IP_Address , DateTime_Accessed , SessionId , etc) into the WebSiteUsers table. Before we do that we need to check to see if there is active Session_Id=@Session ; if there isn't one, then this is a new Session_ID and a record should be inserted. Otherwise this is an old Session_ID and there is no need for a duplicate record giving an incorrect access time! Note: You don't need to check if it is from the same IP, because active Session_ID is unique.

'SessionOnProcess.sql
CREATE PROCEDURE SessionOnProcess
AS
Declare @DateTime_Exited as datetime
Select @DateTime_Exited=getdate()

Update WebSiteUsers
Set DateTime_Exited=@DateTime_Exited,
[mi:ss]=convert(varchar(3),DateDiff(ss,DateTime_Accessed,@DateTime_Exited)/60) + ":" +
convert(char(2),DateDiff(ss,DateTime_Accessed,@DateTime_Exited) -
(DateDiff(ss,DateTime_Accessed,@DateTime_Exited)/60)*60)
where Active='Y'

This procedure is pretty simple: once user clicks the Communicate to IIS Server button (you'll see this in the diagram below) the active user information on table WebSiteUsers is updated. You don't actually need to include this for every client request in your real world application, I just put it in here to give you a feel for the communication between server and browser: once you request information from IIS server, it will be communicate with your Server and recount elapse time until session.timeout is reached, or an explicit log off occurs.

--- See Part 2 --

[Active]

Part 2

--
'SessionOnLogOff.sql
CREATE PROCEDURE SessionOnLogOff (
@SessionID varchar(15)
)
AS
Declare @DateTime_Exited as datetime
Select @DateTime_Exited=getdate()

Update WebSiteUsers
Set DateTime_Exited=@DateTime_Exited,
Active='N',
[mi:ss]=convert(varchar(3),DateDiff(ss,DateTime_Accessed,@DateTime_Exited)/60) + ":" +
convert(char(2),DateDiff(ss,DateTime_Accessed,@DateTime_Exited) -
(DateDiff(ss,DateTime_Accessed,@DateTime_Exited)/60)*60)
where Session_ID=@SessionID
and Active='Y'

When you click Log Off in this application, the stored procedure is executed and calls event Session.Abandon which immediately end the session. There are two things that you might not be expecting. In the first place I've set Active='Y' , so existing user information will not be updated with any Session_ID , and in the second we've set Active='N' (No more active, finished sessions), so it will not be updated again inside Session.Timeout time.

This procedure is called when the session is ended either from a timeout or a logoff:

'SessionOnEnd.sql
CREATE PROCEDURE SessionOnEnd (
@SessionID varchar(15),
@SessionTimeOut as int
)
AS
Declare @DateTime_Exited as datetime
select @DateTime_Exited=dateadd(mi,0-@SessionTimeOut,getdate())
Update WebSiteUsers
Set DateTime_Exited=@DateTime_Exited,
Active='N',
[mi:ss]=convert(varchar(3),DateDiff(ss,DateTime_Accessed,@DateTime_Exited)/60) + ":" +
convert(char(2),DateDiff(ss,DateTime_Accessed,@DateTime_Exited) -
(DateDiff(ss,DateTime_Accessed,@DateTime_Exited)/60)*60)
where Session_ID=@SessionID
and Active='Y'

An update will not take place, however, if the user has actively clicked the Log Off button (see the SessionOnLogOFF explanation). The trick is to subtract the SessionTimeOut = Session.Timeout ; Without this, too much time will be included in the count, including the time SessionTimeOut . This happens when user shouts down the browser or go to visit another web site and doesn't return to your web site within the SessionTimeOut period.

This is pretty important if you want an accurate record of the time a visitor gets to your web site, particularly when the SessionTimeOut is large (the default being 20 minutes). You must to wait for the session to timeout before you can record the real access time. Don't be misled by claims that can be found about current active user numbers on sites - the fact is you just can't tell! Once the session has timed out, you can get the accurate record by subtracting the SessionTimeOut , and therefore calculate exactly when those users left your web site, or, more accurately, the time from when there was no more communication between that user and the IIS Server.

Another common question is: When a user visits my web site for 5 minutes, goes to another web site for 5 minutes and returns to my web site for another 5 minutes. How long is this user recorded as having spent at my website? Well - um - 15 minutes. You've gained those 5 minutes spent elsewhere in the length of stay recorded at your site as long as the visitor returns within the Session.Timeout .

There really isn't a way to know exactly when they leave your web site or when they come back, because there isn't any communication between the user and IIS server when those things happen. If you want more accurate estimate you could decrease the Session.Timeout or encourage user to use log off button.

WebSiteUsers.asp - Putting it to the test
Now to the test strategy. When you first access my web site, a new session starts and your information is logged in our database. The Session.Timeout has been set to 10 minutes. Four buttons are designed for the testing. Communicate to IIS Server updates the users last Communication to IIS Server information and it recounts session timeout. The Show My Profile button will display your access information, including the history. You can compare the results with your watch. Show Active Users will show you the current users on your web site in the session timeout period. It is just one line of code select count(*) from WebSiteUsers where Active='Y' . Easy really. And, of course, Log Off will immediately exit the web site and enter the access time in your profile. You might try going to another web site, and coming back/not coming back within the Session.Timeout , or simply closing your browser. Have a look at your profile later on and check the results.



This is the whole ASP testing code below. Hope you like it:

'WebSiteUsers.asp
<!-- #include file="OpenDatabase.inc" -->
<html>
<head><title>Who Access My Web Site for How Long?</title></head>
<body>
<B>
<p><center>Who Access My Web Site for How Long?</center></p>
<p><center>By Dr.GuoQing Hu</center></p>
<p><center>United Chambers</center></p>
</B><hr>
<%
varBtnComm = Request.form("cmdComm")
varBtnShow = Request.form("cmdShow")
varBtnActive = Request.form("cmdActive")
varBtnLogOff = Request.form("cmdLogOff")

if varBtnComm <> "" then
cm.commandtext = "exec SessionOnProcess"
cm.execute
Response.Write "Thank You for Communicate to IIS Server!" %> </BR> <%
end if
if varBtnShow <> "" then
cm.commandtext = "select * from WebSiteUsers where IP_Address='" & request.servervariables("REMOTE_ADDR") & "'"
objRec.open cm , , 0, 1

if not objRec.EOF THEN %>
<table border=1 cellspacing="0" cellpadding="0">
<tr align=center>
<td><font><small><strong>IP_Address</strong></small></font></td> <td><font><small><strong>DateTime_Accessed</strong></small></font></td>
<td><font><small><strong>DateTime_Exited</strong></small></font></td>
<td><font><small><strong>Active</strong></small></font></td>
<td><font><small><strong>Session_ID</strong></small></font></td>
<td><font><small><strong>[mi:ss]</strong></small></font></td>
</tr>
<% While not ObjRec.EOF %>
<tr align=left>
<% for i=0 to 5 %>
<td><font><small><%=ObjRec(i)%></small></font></td>
<% next %>
</tr>
<% ObjRec.MoveNext
Wend %>
</table>
<% else %>
<h5>No Profile was found.</h5>
<% end if
ObjRec.close
End if
If varBtnLogOff <> "" then
cm.commandtext="Execute SessionOnLogoff '" & session.SessionID & "'"
cm.execute

Session.abandon
Response.Write "Thank You for Log Off IIS Server!" %> </BR> <%
end if
if varBtnActive <> "" then
cm.commandtext="select count(*) from WebSiteUsers where Active='Y'"
ObjRec.open cm , , 0, 1
Response.Write "Current Active Users within Session.TimeOut = " & ObjRec(0) %> </BR> <%
ObjRec.close
end if
%>
<hr>
<%
strSQL="Select DateTime_Accessed,DateTime_Exited,[mi:ss] from WebSiteUsers where session_id='" & Session.SessionID & "' and active='Y'"
cm.commandtext = strSQl
ObjRec.open cm , , 0, 1
if not ObjRec.eof then
Response.Write "First Communication to IIS Server (SQL Server DateTime): " & ObjRec(0) %> </BR> <%
Response.Write "Last Communication to IIS Server (SQL Server DateTime): " & ObjRec(1) %> </BR> <%
Response.Write "Elapse Time Between First and Last Communication to IIS: " & ObjRec(2) %> </BR> <%
ObjRec.close
end if %>

<form method="post" action="WebSiteUsers.asp">
<table border="0" width="100%" cellspacing="1">
<tr>
<td width="100%">Please Click Following Commands.</td>
</tr>
<tr>
<td width="100%">
<input type="submit" value="Communicate to IIS Server" name="cmdComm">
<input type="submit" value="Show My Profile" name="cmdShow">
<input type="submit" value="Show Active Users" name="cmdActive">
<input type="submit" value="Log Off" name="cmdLogOff">
</td>
</tr>
</table>
</form>
</body>
</html>
Summary
I've demonstrated a way to get the statistic reports that answer those 'whose been at my site and for how long' questions using plain old ASP together with the popular connection to SQL Server stored procedure. Many web sites only maintain statistics about the number of visitors to monitor their success. But, of course, this is frequently not enough: for depending on what it is that your site does and is intended for, there are many ways of judging success. It is rare for statistics about the average time that a visitor stays at your web site, and the total time that visitors have spent there to be less important: quality over quantity

I've tried to tackle the confusion that surrounds the best use of session events. If your purpose is to give each visitor a unique ID or register each user that can be recalled or being able to provide "remember me" functionality, then try Darren Gill's DIY VB Components for ASP, or "Moving your "Anonymous" visitors to registered status using Site Server and Membership Directory Authentication" by Bill Pitzer.


--

[equuelus]

thanks for the great article active..

but there's one big problem.. i don't have sql server 7.0.. i use access for my database... is there any chances converting and adapting this article to access?

[Active]

So ...You cannot Run Stored Procedures If you are USing access.

So you have to directly do the database updating tasks
in the Session_OnStart , Session_OnEnd
Event handlers...but believe me...the session_onEnd never fires sometimes... If you can live with it...

'This is in Global.asa file
<SCRIPT LANGUAGE="VBScript" RUNAT="Server">

Sub Application_OnStart
On Error Resume Next
Application("ActiveUsers") = 0
End Sub

Sub Session_OnStart
On Error Resume Next
ip = Request.ServerVariables("REMOTE_ADDR")
ref = Request.ServerVariables("HTTP_REFERER")
Session.Timeout = 10
Dim Conn,RS,SQL
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=test"
Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "Select * from Usage Where SessionID = -1"
RS.Open SQL,Conn,1,3
RS.AddNew
RS("SessionID") = Session.SessionID
RS("IPNumber") = ip
RS("Active") = 1
RS("Started") = Now()
RS("Referrer") = ref
RS.Update
RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
Application.Lock
Application("ActiveUsers") = Application("ActiveUsers") + 1
Application.UnLock
End Sub

Sub Session_OnEnd
On Error Resume Next
Application.Lock
Application("ActiveUsers") = Application("ActiveUsers") - 1
Application.UnLock
Dim EConn,ERS,ESQL
Set EConn = Server.CreateObject("ADODB.Connection")
EConn.Open "DSN=test"
ESQL = "Select Active,Started,Ended,Duration From Usage "
ESQL = ESQL & "Where SessionID = " & Session.SessionID & ";"
Set ERS = Server.CreateObject("ADODB.Recordset")
ERS.Open ESQL,EConn,1,3
If Not (ERS.EOF Or ERS.BOF) Then
ERS("Active") = 0
ERS("Ended") = DateAdd("n",-10,Now())
ERS("Duration") = Datediff("n",ERS("Started"),ERS("Ended"))
ERS.Update
End If
ERS.Close
EConn.Close
Set ERS = Nothing
Set EConn = Nothing
End Sub

</SCRIPT>

[equuelus]

active...

emm your gloabal.asa file was great :cool:

another question please.. do you know anywhere I could get detailed information of buit-in functions for vb i.e. Datediff, DateAdd.. and all that please..

thanks.

[equuelus]

Another thing guru...

emm.. okey.. i'll tell you what i actually want to do...

i want to time the browsing session after the login and before the logoff process not during the session_onstart. Is it possible for me to call the session_onstart procedure afteronly the login process.. or i have to make a new procedure...maybe session_onloggin.. then call this procedure during the loggin process...

if this is possible.. how do i call a procedure in an asp page..

thanks.

[bhaskerg]

hey what do u mean by this "login, login" ? Is it a login screen u have developed for u'r site?

[equuelus]

yea... the website is restricted for members only..
i want to time the moment when the user logs in untill the user logs out.

[Active]

No. U can't Call the Event Handlers in Global.asa

But just copy and Paste their code in the LOgin and Logoff routines in the respective asp pages.

[Active]

DateAdd Function

Description
Returns a date to which a specified time interval has been added.
Syntax
DateAdd(interval, number, date)
The DateAdd function syntax has these parts:

Part Description
interval Required. String expression that is the interval you want to add. See Settings section for values.
number Required. Numeric expression that is the number of interval you want to add. The numeric expression can either be positive, for dates in the future, or negative, for dates in the past.
date Required. Variant or literal representing the date to which interval is added.


Settings
The interval argument can have the following values:
Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week of year
h Hour
n Minute
s Second


Remarks
You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now. To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").
The DateAdd function won't return an invalid date. The following example adds one month to January 31:

NewDate = DateAdd("m", 1, "31-Jan-95")

In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date is 31-Jan-96, it returns 29-Feb-96 because 1996 is a leap year.
If the calculated date would precede the year 100, an error occurs.

If number isn't a Long value, it is rounded to the nearest whole number before being evaluated.


DateDiff
Description
Returns the number of intervals between two dates.
Syntax
DateDiff(interval, date1, date2 [,firstdayofweek[, firstweekofyear]])
The DateDiff function syntax has these parts:

Part Description
interval Required. String expression that is the interval you want to use to calculate the differences between date1 and date2. See Settings section for values.
date1, date2 Required. Date expressions. Two dates you want to use in the calculation.
firstdayofweek Optional. Constant that specifies the day of the week. If not specified, Sunday is assumed. See Settings section for values.
firstweekofyear Optional. Constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs. See Settings section for values.



Settings
The interval argument can have the following values:
Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week of year
h Hour
n Minute
s Second


The firstdayofweek argument can have the following values:

Constant Value Description
vbUseSystem 0 Use National Language Support (NLS) API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday


The firstweekofyear argument can have the following values:

Constant Value Description
vbUseSystem 0 Use National Language Support (NLS) API setting.
vbFirstJan1 1 Start with the week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the week that has at least four days in the new year.
vbFirstFullWeek 3 Start with the first full weekof the new year.


Remarks
You can use the DateDiff function to determine how many specified time intervals exist between two dates. For example, you might use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the end of the year.
To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

If date1 refers to a later point in time than date2, the DateDiff function returns a negative number.

The firstdayofweek argument affects calculations that use the "w" and "ww" interval symbols.

If date1 or date2 is a date literal, the specified year becomes a permanent part of that date. However, if date1 or date2 is enclosed in quotation marks (" ") and you omit the year, the current year is inserted in your code each time the date1 or date2 expression is evaluated. This makes it possible to write code that can be used in different years.

When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.

The following example uses the DateDiff function to display the number of days between a given date and today:

Function DiffADate(theDate)
DiffADate = "Days from today: " & DateDiff("d", Now, theDate)
End Function