|
An Overview of Server Usage Statistics
Web server administrators often need server usage statistics. They want to know:
- Who visited the site
- When did the visitor arrive and leave
- What pages did the visitor request.
There are a lot of tools on the market, but they are either very expensive, don’t provide the information you need, or aren’t customizable. So why not to try to write you own tool - especially if you have some experience in ASP?
We will discuss different types of user and request logging, but we’ll focus on one that you can easily implement and customize using ASP.
Note: In this article, “page requests” and “page hits” are synonymous.
Usage Tracking and Possible Implementations
Server usage tracking can be implemented in four ways:
- The statistics application reads information from server log, created directly by the Web service. This type of tracking is the fastest because most of the work is done by the WWW service itself. It stores information in a file or database on the fly and the application simply queries for the information you need. However, in this case you are restricted to the information gathered by the WWW service.
- The second way is to write you own ISAPI filter. This is a good solution because you can log whatever you want and use whatever you want. And it's also rather fast. But writing ISAPI filters like this is not a simple task - even for experienced C++ programmers.
- The third method we discuss is the banner style system. The idea is to insert on each page a small picture (a banner) with the SRC parameter pointing to a CGI/ASP program (even on another server!). When somebody requests the page, the browser automatically requests this CGI/ASP, which usually collects information from the request and outputs a picture (or simply redirects to a picture file). Of course, this method is a little bit slower than the previous one but it's easier to implement. (A good example of implementing this system is www.hitbox.com statistics service.)
- The last method was introduced at http://www.vallin.com and is widely used at http://www.microsoft.com. The idea is to change all of the links within the server to point not directly to the pages but via an ASP/CGI application with the destination as a parameter - like “/scripts/mylog.asp?go=home.htm”. This method requires modifying almost all of the pages on the server. Therefore, it isn’t suitable for previously developed sites or for use with visual HTML editing tools. This method has been fully discussed already so we won’t deal with it in this article.
Problems and Solutions
Now let's discuss what information we can gather with these methods.
Usually the information consists of the date and time, client IP address, requested page URL, bytes sent, status, etc. For some purposes this is enough. But this information is requests (page hits) oriented. That is, you get information about requests - not users. Usually you wish to track users! You may think that you can query user-oriented information using the IP address. The IP address is not reliable because many people use proxy servers and therefore requests from different users may have the same client-IP.
The most convenient way to separate one user from another is with cookies. Unfortunately only few Web servers (like IIS 4) can log cookies by themselves. Even if your server can, you need to find a way to write this cookie. Therefore, the method one (from above) is not suitable.
You can use cookies with the second method, but we have already discussed the difficulty writing ISAPI filters.
Fortunately, ASP internally supports cookies. So let's see how Active Server Pages can help us.
You could rename all of the HTML pages on your server to ASP files and add a few lines of code to each (using further processing much like in second method). But this may significantly decrease the speed of your server. It also requires changing your pages.
Or, you can follow the third idea. In this article we will focus on that method.
The second part of implementing statistics is, of course, displaying reports. There is no big difference among the above methods in this area. All of them simply query information from a text file or database. This part can be easily implemented with ASP. The scheduling of the report creation may vary. Reports can be generated once an hour or day and stored in an HTML file or generated on request. The first way is the fastest but it doesn't provide up-to-date information and the reports usually can’t be customized. The second method is slow but provides real information. We will use this second solution. Of course, you can combine both of these ways and provide reports caching etc.
Implementing a Banner Style System
In the previous section, we said that we could use the "banner style" of implementing statistics. Let's look at this method more closely.
Once again, the idea is to insert on each page a small picture (banner) with the SRC parameter pointing to a CGI/ASP program (even on another server!). When somebody requests the page, the browser automatically requests this CGI/ASP script which usually collects information from the request and outputs a picture (or simply redirects to a picture file). This picture can be added manually or automatically. You may wonder how the script knows what page was requested. The trick is to use HTTP_REFERER server variable.
Let’s prepare the server. Create a virtual directory called /log. All ASP scripts from this application should be located in this virtual directory.
Now we need a picture to display. It can be site logo, a "hosted by" logo, or a simply tiny invisible GIF consisting of one transparent pixel. In this article, we don’t want to modify the appearance of our pages so we’ll use an empty picture. Let's call it nop.gif. You can save this file in /log directory (assumed in this article) or in another location (change the URL below in this case).
We also need an ASP program that will log the requests. Let’s call it checker.asp. For the time being, it will consist of just one line:
Listing 2.1. checker.asp
<% Response.Redirect("nop.gif") %>
Now we need to insert a tag like <IMG SRC="/log/checker.asp?" HEIGHT=0 WIDTH=0> on each page that you want to log on your server. You can insert the tag manually by adding this line somewhere on each page or automatically using the Document Footer property implemented in IIS 4. Let’s take the automatic route by creating a file called footer.htm.
Listing 2.2 footer.htm
<IMG SRC="/log/checker.asp?" WIDTH=0 HEIGHT=0>
If you use IIS4, do the following in Microsoft Management Console (MMC):
- Select you Web site and open its Properties.
- Select the Documents tab
- Check Enable Document Footer and browse for footer.htm.
- Click OK
- Find the virtual directory /log (you previously created).
- Open the Properties of this directory and select the Documents tab.
- Uncheck Enable Document Footer.
- Click OK and save your changes
Unfortunately, IIS adds the footer only to HTML files and not to ASP. But this is the dynamic nature of ASP, so you need to add this tag manually to your ASP files. (Of course you could write one small inc file and add it to all you scripts using server-side includes.)
Note: If you wish to use a visible picture, change the HEIGHT and WIDTH parameters in the IMG tag above. You can also make it part of your page design and in which case it would be better to add the tag manually.
Also, we need to tell the browser not to cache this picture. Otherwise, it will ask for picture only once. To do this we need to add a few HTTP Headers. This can be done easily with IIS4. Do the following in MMC:
- Find the virtual directory /log (you have created before).
- Open the Properties of this directory and select the HTTP Headers tab.
- Check Enable Content Expiration and select Expire Immediately.
- In Custom HTTP Headers add a header with the name pragma and content no-cache
- Click OK and save your changes.
Note: Some older browsers don’t understand content expiry so they don't want to reload the picture every time. Sometimes adding a question mark ("?") at the end of SRC property helps to tell them that this is not a real picture but a script and that they should not cache it.
Now you can test your work by browsing some pages on your server (except files from /log directory). But you won’t see any difference - just your pages as before. To see the changes look at the page source from browser. You’ll notice a small line with an IMG tag near the end of page.
The ASP Solution for User Tracking
At this point we can add a few lines of code into checker.asp before the call to Response.Redirect. This code saves the request information such as the time, requested page, and remote IP address. But we’ll go even further because we want to track users and not just hits.
The idea to track users is implemented via the Session object in ASP. From the Session_OnStart and Session_OnEnd events we can find out when the user came and when he left. Also, all page hits within this period from this user can be separated from others using the Session scope variable! All we need is to create a global.asa file in our /log directory to handle this event. Also note that we have placed the checker.asp file in the same directory - so in terms of ASP, this file is part of the /log application. It can share information from the Session and Application objects together with all files in this directory. All requests to all pages from your server (or even from another non-IIS server) are gathered in this application.
You can see that the information is collected in three stages. The first stage is when a user first comes to your site. This stage is controlled in the Session_OnStart event in the global.asa file. Here, you can create records, store all user-specific information such as the date, time, remote IP, host, browser type, etc. The second stage is completed every time the user accesses pages. This stage is controlled in checker.asp where you can store request specific information such as the request time, page URL and method. The last stage is completed when a user leaves your site, in the Session_OnEnd event. Here you can write the time and date and close the record.
Note: We can’t say exactly when a user leaves. We can only assume that he did after some period of inactivity. You can control this period with Session.Timeout property.
We we’ll also add another stage, which is responsible for application initialization, in Application_OnStart event.
Now we have grasped the idea so let's start writing code.
Implementing a Simple Logging Application
In this section, we we’ll write a rather simple ASP application that will be able to log information about users and requests using the previously discussed method and store the information in a database. Let's start with creating the database.
Preparing the Database
In this article we will use an MS Access database. This is a good solution for a small server, but for large one you should use SQL Server or another powerful ADO-compatible database.
Open MS Access and create a database (let's call it log.mdb). The structure of our database is very simple - just two tables.
Note: You don’t need to save this database in the /log directory.
Table Users
|
Field Name |
Type |
Description |
|
UserID |
Autonumber (primary key) |
Automatically generated user ID |
|
RemoteAddr |
Text (15) |
User's IP address |
|
RemoteHost |
Text (50) |
User's host name |
|
Start |
Date/Time |
Came |
|
End |
Date/Time |
Left |
|
Browser
|
Text (3)
|
Browser, with version (e.g. IE4, NN3, Unk0) |
|
X-Forward
|
Text (50)
|
Real IP (when user uses a proxy)
|
Table Requests
|
Field Name
|
Type
|
Description
|
|
RequestID
|
Autonumber (primary key)
|
Automatically generated request ID
|
|
UserID
|
Long integer
|
Link with Users table
|
|
Time
|
Date/Time
|
Page was accessed
|
|
URL
|
Text (100)
|
Page URL
|
|
Method
|
Text (10)
|
Request method (e.g. GET, POST)
|
Save the tables and close the database. Now, we need to register this database with DSN. To do this:
- Open ODBC Data Source Administrator from Control Panel.
- Select System DSN tab.
- Click Add... select Microsoft Access driver and click Finish.
- Type LogDB in field Data Source Name.
- Find your database with Select button.
- Click OK.
Now our database is ready to use.
Initialization and Shutdown
The Global.asa file is responsible for your application initialization and shutdown. We’ll also use it for user tracking (but not page hits).
During application initialization we will cache the database connection and one commonly used recordset. Caching helps us to dramatically increase the speed of application. Initialization is implemented in Application_OnStart event.
Listing 3.1 global.asa (Application Object)
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
SUB Application_OnStart
Application.lock
' Cache database connection
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "LogDB"
Set Application("ConnectionObject") = objConn
' Cache mostly used recordset
Set objRst = Server.CreateObject("ADODB.RecordSet")
'adOpenDynamic
objRst.CursorType = 2
'adLockOptimistic
objRst.LockType = 3
'adCmdTable
objRst.Open "Requests", objConn, , , &H0002
Set Application("RequestsRst") = objRst
Application(“visitors”) = 0
Application.unlock
END SUB
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
SUB Application_OnEnd
END SUB
</SCRIPT>
Note: We use numbers instead of the pre-defined constants from adovb.inc because IIS doesn’t use SSI for global.asa by default. Corresponding constants from this file are given in the comments. ASP gurus may advise you to use a type library, but that’s a subject for another article.
Important! In this article we assume that ADO objects in your system are free-threaded. If they are not (or you aren't sure) please run makfre15.bat usually located in Program Files/Common Files/system/ado.
In the code above we create a connection object, open our database, and store the connection object in an Application object for future use. We open and store the table Requests with a dynamic cursor and optimistic lock.
We left the Application_OnEnd event empty because it is called only when Web server is stopped. IIS will do all the work of closing the database connections by itself.
In this application we will also track how many users are currently connected. We use the application scope variable visitors.
A Few Words About Proxy-Servers and Real IPs
Many ISPs offer their customers the use of a proxy-server. This may dramatically increase the speed of browsing and downloading. However, when such a user accesses your pages, his or her IP address and host name are set to the proxy server’s IP and host name! But for real logging you want to know real IP. Fortunately a lot of proxy servers send you the header X-Forwarded-For. The format of this header is not documented and not all proxies support it. You shouldn’t count on it to always reveal the real IP. Sometimes it may contain several cascade proxies IPs or something else. So it's better just to save it as it is without converting.
Tracking Users
The second part of the global.asa file is the most interesting. In this part, we work with the Session object. Using this object we can track users.
When a user requests any page on the server, the browser asks for the image that we inserted and our checker.asp is executed. But if this page is the first page he or she requests, the server automatically executes the Session_OnStart sub. (If this is the first request after starting the server, Application_OnStart runs first.) If the server has received no requests for a specified period (set in Application Properties in MMC or programmatically in Session.Timeout property), it calls the Session_OnEnd sub. So, we can use these events to track users.
Listing 3.2 global.asa (Session Object, OnStart Event)
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
SUB Session_OnStart
' Set suitable timeout here (in minutes) or comment
' following line to use settings from MMC
Session.Timeout = 5
' Currently connected visitors
Application.lock
Application("visitors") = Application("visitors") + 1
Application.unlock
' Browser type control
Set objBrowser = Server.CreateObject("MSWC.BrowserType")
' DNS resolving control
Set objDns = Server.CreateObject("INet.DNS.1")
' Adding user in to [Users] table
Set objConn = Application("ConnectionObject")
Set objRst = Server.CreateObject("ADODB.RecordSet")
'adOpenKeyset
objRst.CursorType = 1
'adLockOptimistic
objRst.LockType = 3
'adCmdTable
objRst.Open "Users", objConn, , , &H0002
objRst.AddNew
objRst("RemoteAddr")= Request.ServerVariables("REMOTE_ADDR")
' DNS resolving
objDns.IP = Request.ServerVariables("REMOTE_ADDR")
str = objDns.Host
if objDns.Status = 0 then
objRst("RemoteHost")=str
else
objRst("RemoteHost")= "N/A"
end if
objRst("Start")= CDate(Now)
' User's browser
if objBrowser.browser = "Netscape" then
objRst("Browser")="NN"
elseif objBrowser.browser <> "IE" then
objRst("Browser")="Unk"
else
objRst("Browser")="IE"
end if
objRst("Browser")= objRst("Browser") & objBrowser.majorver
' Real IP
objRst("X-forward")=Request.ServerVariables("HTTP_X-Forwarded-For")
objRst.Update
' Store userid for link with page hits
Session("ID") = objRst("UserID")
' Last visited page to avoid odd information
' (subsequent requests of the same page)
Session("lastUrl") = ""
Session("lastTime") = Now
END SUB
</SCRIPT>
Let’s discuss this code.
In the first lines, we set the Session.Timeout property (discussed above) and increment the visitors variable. After that we create a BrowserType object (to know what browser is being used), load the database Connection object (previously stored in Application object) and create a DNS lookup object (to find out the user's host name).
Note: The IIS Documentation says that the user's host name can be accessed via the REMOTE_HOST server variable. But on my server, IIS always sets this variable to the user’s IP of address. I wrote very simple component, INet.DNS, to resolve the IP to host and vice versa. This component Dns.dll with source in VC++/ATL is included. It's free and ready to use. If you want to use this component, don’t forget to register it on your computer with the following command: regsvr32.exe Dns.dll. Use the IP and Host properties to set and retrieve the IP and host name. Use the Status property to check for success of operation. Of course, you can use your favorite DNS component or write you own.
We also create a Recordset object and use it to open the Users table with a keyset cursor and optimistic lock. Now we can create a new record for this table. We add the following fields: the remote IP, the remote host name, the date and time the user starts, the browser type and browser version (we can distinguish only Internet Explorer and Netscape Navigator with the rest as unknown), and the real IP.
After setting all of the fields, we save this record. Now, after saving, we can access the autonumbered field called UserID (primary key) and store it. We’ll use it later to link the table Requests to Users.
Note: Logging all page hits will suit almost any site but there is one exception - interactive pages. In this case "interactive" means that they are either manually or automatically reloaded. This technique is used in online video, chats, etc. where subsequent requests for the same page arrive several times per minute. In this case our database will grow very fast with odd information. So, we’ll check whether the subsequent requests come from the same page within a minute and in such a case ignore last one.
That’s why we initialize two session scope variables, lastURL and lastTime. lastURL holds the URL of the last page hit and lastTime holds the time when this hit occurs.
You may say that we’ve left one field empty while inserting the new record. That’s correct. The field End contains the date and time that user the left the site. We can track this event in the Session_OnEnd event. Let's see how.
Listing 3.3 global.asa (Session Object, OnEnd Event)
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
SUB Session_OnEnd
Application.lock
Application("visitors") = Application("visitors") - 1
Application.unlock
' Log time when user left
Set objConn = Application("ConnectionObject")
SQLQuery = "UPDATE Users SET Users.End = #"&Now&"# WHERE "&_
"(((Users.UserID)= " &Session("ID")& "));"
objConn.Execute(SQLQuery)
END SUB
</SCRIPT>
In the first lines of the code, we decrement the number of currently active users. After this we load our database Connection object and perform a simple update query (we can use the previously stored UserID) in the Users table.
Important! The Access ADO driver assumes that you are using the US time/date convention for SQL requests even if your system uses a different format. You may experience problems with the code above, because the function Now will return the time/date in your format but the driver will accept only the US format. This problem can be solved by manually converting all dates in the SQL statements to US format.
Click Here to Download this Article's Code
At this point we have a simple but powerful application for logging users on your server. Now it's time to test it. Browse your site from different computers. Or, use the same computer but refer to the server by different names like 127.0.0.1, localhost, your computer name etc. The server will consider you as different users. Try to leave the site for a period longer than the timeout you set.
After this, open you database in Access and open the Users table. You should see all of the users, when they came, and when they left.
The Requests table is still empty, so our next step is logging requests.
Tracking Page Requests
With the Session OnStart event we can log the first request but it's not suitable for logging subsequent calls. However, we do have checker.asp - it's called for every page requested. The main task of this script is to log information about page hits. We can access the page URL from the HTTP_REFERER server variable. Recall that our IMG tag is located on the page that we log, so the request for the picture comes from this page and the browser sets this variable for us.
Listing 3.4 checker.asp
<%
' Load cached recordset
Set objRst = Application("RequestsRst")
' Check for odd information
' (subsequent requests of the same page within 1.5 minutes)
if StrComp(Session("lastURL"),
Request.ServerVariables("HTTP_REFERER"))=0 then
if DateDiff("s", Session("lastTime"), Now)<90 then
Response.Redirect("nop.gif")
end if
end if
' Adding new "page hit" to database using link
' with "user" via "id" variable
' We store only information about hit because
' information about user (like ip, browser)
' was already stored by global.asa
objRst.AddNew
objRst("UserID")= Session("ID")
objRst("Time")= CDate(Now)
objRst("URL")= Request.ServerVariables("HTTP_REFERER")
objRst("Method")= Request.ServerVariables("REQUEST_METHOD")
objRst.Update
Session("lastURL") = Request.ServerVariables("HTTP_REFERER")
Session("lastTime") = Now
' Redirecting to picture
Response.Redirect("nop.gif")
%>
The first lines in the script are used to ignore subsequent calls from the same page within less then a minute of each other. After that, we load the cached recordset that implements our Requests table. We create a new record with following fields:
UserID - a link with the corresponding user
Time - when page was accessed
URL - URL of the requested page
Method - the request method
Then we save this record. We also set our two variables lastURL and lastTime for subsequent calls.
Note our script checker.asp should always return an image (either directly or by redirecting to the picture file). It should return only that and nothing else. In this version of the application we simply redirect the response to an empty GIF called nop.gif.
Congratulations! We’ve completed the first part of our application. At this moment we have stored in the database all of the information about the users and the pages that they request.
The Main Form
At this point, our application can collect and store information about users and page hits in the database. Now we want to publish this information on our Web server for public or private use.
In this section, we we’ll use a simple form to display the user or request statistics for a specified period. This form will be implemented in the entry point of our application - default.asp. This is also where we’ll display the number of users who are currently logged on to the server.
Listing 4.1 default.asp
<%
' Simple script for implementing first form.
' User selects what type of statistics to view - "users" or "page hits"
' and also selects a period (7 days by default)
if Request.Form("type")="Users" then
Session("startdate")=Request.Form("startdate")
Session("finishdate")=Request.Form("finishdate")
Response.Redirect("usrdate.asp")
elseif Request.Form("type")="Pages" then
Session("startdate")=Request.Form("startdate")
Session("finishdate")=Request.Form("finishdate")
Response.Redirect("reqdate.asp")
end if
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<HTML>
<HEAD>
<TITLE>Statistics</TITLE>
</HEAD>
<BODY>
Currently <%= Application("visitors") %> visitors<br>
<form action="default.asp" method="POST">
<table border="0" cellspacing="5" cellpadding="5">
<tr>
<td rowspan="2"><b>Period</b></td>
<td>Start Date</td>
<td><input type="Text" name="startdate"
value="<%= DateAdd("d", -6, Date)%>" size="15"
maxlength="15"></td>
<td><input type="Submit" name="type" value="Users"></td>
</tr>
<tr>
<td>Finish Date</td>
<td><input type="Text" name="finishdate"
value="<%= Date %>" size="15" maxlength="15"></td>
<td><input type="Submit" name="type" value="Pages"></td>
</tr>
</table>
</form>
</BODY>
</HTML>
In this script, we just display a simple form, collect information, set the variables startdate and finishdate, and call either usrdate.asp or reqdate.asp. The default value for the period is seven days. We also display the visitors variable, which is controlled in the session OnStart and OndEnd events.
Click Here to get the code for week statistics
Displaying User Information
In the usrdate.asp script we’ll display information about users such as their start and finish times, the number of hits they made, their browser, IP address, host, and real IP address. We'll also include a hyperlink for detailed statistics for this particular user. Let's look at the first version of this script:
Listing 4.2 usrdate.asp (first version)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<HTML>
<HEAD>
<TITLE>User Statistics</TITLE>
</HEAD>
<BODY>
<%
' Determine requested period, setting to default if something missed
startdate = Request.Form("startdate")
finishdate = Request.Form("finishdate")
if startdate="" then startdate = Session("startdate")
if finishdate="" then finishdate = Session("finishdate")
if Not IsDate(startdate) then startdate = DateAdd("d", -6, Date)
if Not IsDate(finishdate) then finishdate = Date
startdate = CDate(startdate)
finishdate = CDate(finishdate)
Set objConn = Application("ConnectionObject")
' Main query grouped by "users" for period
SQLQuery = "SELECT Users.UserID, Users.RemoteAddr, Users.RemoteHost, " &_
"Users.Start, Users.End, Users.Browser, Users.[X-Forward], " &_
"Count(Requests.Time) AS CountOfTime " &_
"FROM Requests, Users WHERE (((Requests.UserID)=[Users].[UserID]) " &_
"AND (Users.Start>=#" & startdate & "#) AND " &_
"(Users.Start<=#" & DateAdd("d", 1, finishdate)& "#)) " &_
"GROUP BY Users.UserID, Users.RemoteAddr, Users.RemoteHost, " &_
"Users.Start, Users.End, Users.Browser, Users.[X-Forward];"
Set rsUsersList = objConn.Execute(SQLQuery)
' Total users counter
UsrCount=0
%>
<h2>Users Statistics for period from <%= startdate %> to <
%= finishdate %></h2>
<%
' Check if no records found for this period
if (rsUsersList.BOF) and (rsUsersList.EOF) then
Response.Write("No records found </body></html>")
Response.End
end if %>
<!-- Main table -->
<font face="Arial">
<table nowrap width=620><tr valign="MIDDLE">
<table nowrap cellpadding=3 cellspacing=2 border="0"><tr valign="MIDDLE">
<td bgcolor="f7efde" align=center><b>H</b></td>
<td bgcolor="f7efde" align=center><b>B</b></td>
<td bgcolor="f7efde" align=center><b><font size="-1">Come/Left
</font></b></td>
<td bgcolor="f7efde" align=center><b><font size="-1">Host
</font></b></td>
<td bgcolor="f7efde" align=center><b><font size="-1">IP
</font></b></td>
<td bgcolor="f7efde" align=center><b><font size="-1">
Real IP</font></b></td>
<% Do Until rsUsersList.EOF%><tr valign="MIDDLE">
<td bgcolor="f7efde" align=center><font size="-1"><i>
<%= rsUsersList("CountOfTime")%>
<a href="requests.asp?userid=<%= rsuserslist("userid")%>
"><img src="arrow1.gif" width=15 height=15 alt=""
border=0 align="middle"></a></i></font>
</td>
<td bgcolor="f7efde" align=center>
<font size="-2"><%= rsUsersList("Browser")%>
</font>
</td>
<td nowrap bgcolor="f7efde" align=center><font size="-2">
<%= rsUsersList("Start")%><br><%= rsUsersList("End")%>
</font>
</td>
<td bgcolor="f7efde" align=center><font size="-1">
<%= rsUsersList("RemoteHost")%>
</font>
</td>
<td bgcolor="f7efde" align=center><font size="-1"><b>
<%= rsUsersList("RemoteAddr")%></b>
</font>
</td>
<td bgcolor="f7efde"><font size="-2">
<%= rsUsersList("X-Forward")%>
</font>
</td>
</tr>
<%
UsrCount=UsrCount+1
rsUsersList.MoveNext
Loop
%>
</table></font>
<%
' Calculate total number of hits- iteration
' with cursors in this situation is not
' very good solution - but we will use this loop later
SQLQuery = "SELECT Requests.Time FROM Requests WHERE " &_
"(Requests.Time>=#" & startdate & "#) " &_
"AND (Requests.Time<=#" &DateAdd("d", 1, finishdate)& "#)"
HitCount=0
Set rsHitsList = objConn.Execute(SQLQuery)
Do Until rsHitsList.EOF
HitCount=HitCount+1
rsHitsList.MoveNext
Loop
%>
<br>
<font face="Arial">Total <b><%=UsrCount%></b>
users and <b><%=HitCount%></b>
hits</font><br><br>
</body>
</html>
Figure 1 shows the results of the preceding code.
Click Here to View Figure 1
As you can see, by using a simple grouping query we display the table called Users with the number of page hits per user (using the aggregate function Count in SQL). We also count the total number of users and requests. We query for a specified period, which we retrieve from the Session variables, startdate and finishdate. If the variables are not set we set them to the default value which is one week.
Note: Our administration files are also contained in the /log directory with checker.asp. This means that the subs in our global.asa are called even if you set it not to log this directory. The trick is that we use the query based on both the Users and Requests tables and when the Requests table doesn't have an entry for a particular user, this user will not appear in query! This feature can be used to log administrators - just find the users in the table without requests.
Detailed User Statistics
We have the displayed information about users but this doesn't provide any information about the pages that he or she requested. We need detailed info. In the previous code we inserted a hyperlink to our requests.asp script (a small arrow). This script will display detailed information about a particular user. We send the user’s ID via the request string variable, userid).
Listing 4.3 requests.asp
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<HTML>
<HEAD>
<TITLE>Requests Statistics</TITLE>
</HEAD>
<BODY>
<%
Set objConn = Application("ConnectionObject")
who = Request.QueryString("userid")
' Check if correct user id was passed
if Not IsNumeric(Request.QueryString("userid")) then
Response.Write("Bad parameter")
Respopse.Write("<br></body></html>")
Response.End
elseif CInt(Request.QueryString("userid")) <= 0 then
Response.Write("Bad parameter")
Respopse.Write("<br></body></html>")
Response.End
end if
' Access information about user and his requests from one query
SQLQuery = "SELECT Users.UserID, Users.[X-Forward], " &_
"Users.RemoteAddr, " &_
"Users.RemoteHost, Users.Start, Users.End, " &_
"Users.Browser, "&_
"Requests.UserID, Requests.Time, Requests.URL, " &_
"Requests.Method " &_
"FROM Users, Requests WHERE (((Requests.UserID)=" &_
"(Users.UserID) " &_
"And (Requests.UserID)=" &_
Request.QueryString("userid") & ")) " &_
"ORDER BY Requests.Time;"
Set rsReqList = objConn.Execute(SQLQuery)
if (rsReqList.BOF) and (rsReqList.EOF) then
Response.Write("No records for this user")
Response.Write("<br></body></html>")
Response.End
end if
rsReqList.MoveFirst
%>
<h2>User Statistics</h2>
<font face="Arial">
<table>
<tr><td bgcolor="f7efde" align=center>
User # </td>
<td bgcolor="f7efde" align=center>
<%= rsReqList("UserID") %>
</td></tr>
<tr><td bgcolor="f7efde" align=center>
Come at </td>
<td bgcolor="f7efde" align=center>
<%= rsReqList("Start") %>
</td></tr>
<tr><td bgcolor="f7efde" align=center>
Left at </td>
<td bgcolor="f7efde" align=center>
<%= rsReqList("End") %>
</td></tr>
<tr><td bgcolor="f7efde" align=center>
IP / host </td>
<td bgcolor="f7efde" align=center>
<%= rsReqList("RemoteAddr") %> /
<%= rsReqList("RemoteHost") %>
</td></tr>
<tr><td bgcolor="f7efde" align=center>
Browser </td>
<td bgcolor="f7efde" align=center>
<%= rsReqList("Browser") %>
</td></tr>
<tr><td bgcolor="f7efde" align=center>
X-Forward </td>
<td bgcolor="f7efde" align=center>
<%= rsReqList("X-Forward") %>
</td></tr>
</table>
</font>
<br>
<h2>Requests Statistics</h2>
<table><tr>
<td bgcolor="f7efde" align=center><b>
<font size="-1">Time
</font></b>
</td>
<td bgcolor="f7efde" align=center><b>
<font size="-1">URL
</font></b>
</td>
</tr>
<% i=0
Do Until rsReqList.EOF%>
<tr>
<td bgcolor="f7efde" align=center><font size="-1">
<%if who="" then
Response.Write(rsReqList("Time"))
else
Response.Write((rsReqList("Time")))
end if %> </font> </td>
<td bgcolor="f7efde" ><a href="<%= rsReqList("URL")%>">
<font size="-1"><%= rsReqList("URL")%> </font>
</a>
</td>
<%
rsReqList.MoveNext
i=i+1
Loop
ReqCount = i
%>
</table></font>
<br>
Total Hits <b><%= i %></b>
</BODY>
</HTML>
Figure 2 displays the result of above code.
Click Here to View Figure 2
In this script we first display the full user details and after that, all of the pages that he or she requested. All of this information is based on a simple query from both tables using the UserID variable that we passed to the script.
Request Statistics
We’ll also implement complete page hits statistics for specified a period. This will allow us to implement pages rating at a later time. We’ll the display time, page URL, user IP and host, and browser. Each item will also have a hyperlink to corresponding user.
Listing 4.4 reqdate.asp (first version)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<HTML>
<HEAD>
<TITLE>Pages Statistics</TITLE>
</HEAD>
<BODY>
<%
Set objConn = Application("ConnectionObject")
' Determine requested period, setting to default if something missed
startdate = Request.Form("startdate")
finishdate = Request.Form("finishdate")
if startdate="" then startdate = Session("startdate")
if finishdate="" then finishdate = Session("finishdate")
if Not IsDate(startdate) then startdate = DateAdd("d", -6, Date)
if Not IsDate(finishdate) then finishdate = Date
' Main query by "requests" (page hits) for period
SQLQuery = "SELECT Users.UserID, Users.RemoteAddr, " &_
"Users.RemoteHost, " &_
"Users.Start, Users.End, Users.Browser, Requests.UserID, " &_
"Requests.Time, Requests.URL, Requests.Method "
"FROM Users, Requests WHERE (Requests.UserID=Users.UserID) " &_
"And (Requests.Time>=#" & startdate & "#) AND " &_
"(Requests.Time<=#" &DateAdd("d", 1, finishdate)& "#) " &_
"ORDER BY Requests.Time;"
startdate = CDate(startdate)
finishdate = CDate(finishdate)
Set rsReqList = objConn.Execute(SQLQuery)
' Check if no records found for this period
if (rsReqList.BOF) and (rsReqList.EOF) then
Response.Write("No records found")
Response.Write("<br></body></html>")
Response.End
end if
%>
<h2>Requests Statistics for period from
<%= startdate %> to
<%= finishdate %></h2>
<!-- Main table -->
<table><tr>
<td bgcolor="f7efde" align=center><b>
<font size="-1">Time
</font></b></td>
<td bgcolor="f7efde" align=center><b>
<font size="-1">URL
</font></b></td>
<td bgcolor="f7efde" align=center><b>
<font size="-1">IP
</font></b></td>
<td bgcolor="f7efde" align=center><b>
<font size="-1">Host
</font></b></td>
<td bgcolor="f7efde" align=center><b>
<font size="-1">Browser
</font></b></td>
<td bgcolor="f7efde" align=center><b>
<font size="-1">User
</font></b></td>
</tr>
<% i=0
Do Until rsReqList.EOF%>
<tr>
<td bgcolor="f7efde" align=center><font size="-1">
<%= rsReqList("Time") %></font> </td>
<td bgcolor="f7efde" ><a href="<%= rsReqList("URL")%>">
<font size="-1"><%= rsReqList("URL")%> </font>
</a> </td>
<td bgcolor="f7efde" align=center><font size="-1">
<%= rsReqList("RemoteAddr")%> </font> </td>
<td bgcolor="f7efde" align=center><font size="-1">
<%= rsReqList("RemoteHost")%> </font> </td>
<td bgcolor="f7efde" align=center><font size="-1">
<%= rsReqList("Browser")%> </font> </td>
<td bgcolor="f7efde" align=center>
<a href="requests.asp?userid=<%= rsReqList("UserID")%>">
<img src="arrow1.gif" width=15 height=15 alt="" border=0
align="middle">
</a> </td>
</tr>
<%
rsReqList.MoveNext
i=i+1
Loop
%>
</table></font>
<br>
Total Hits <b><%= i %></b>
</BODY>
</HTML>
Figure 3 displays the results of preceding code.
Click Here to See Figure 3
So, when you see an interesting request you can click the arrow to see the complete information about the user who made the request.
Implementing Charts
All of the information that we displayed in the previous section is very interesting but most of us prefer to see visual data - like charts. Drawing charts usually means constructing images and requires special components. However, in this application we can achieve the same result using plain HTML. The idea is to use a small image and the tags WIDTH or HEIGHT so we can stretch the image to the required size.
Complete Users Statistics
We’ll now add charts to our usrdate.asp script. We’ll display user and hit activity for a specified period by hours.
Due to the size restriction only differences are documented in listing 4.5. Click Here to download the complete code from this article.
Listing 4.5 usrdate.asp (second version)
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
' Helper function
Function Max(arg1, arg2)
if arg1<arg2 then
Max = arg2
else
Max = arg1
end if
End Function
</SCRIPT>
Dim UsrHours(24) ' number of users per hour
Dim HitHours(24) ' number of hits per hour
for i=0 to 23
UsrHours(i)=0
HitHours(i)=0
Next
' Variables for proportional chart drawing
UsrCount=0
MaxU = 0
<%
' Filling array and finding maximum
UsrHours(DatePart("h",CDate(rsUsersList("Start"))))=
UsrHours(DatePart("h",CDate(rsUsersList("Start"))))+1
MaxU = Max(UsrHours(DatePart("h",CDate(rsUsersList("Start")))), MaxU)
' Now we should fill array with page hits -
' iteration with cursors in this situation is not
' very good solution, but very simple
SQLQuery = "SELECT Requests.Time FROM Requests WHERE " &_
"(Requests.Time>=#" & startdate & "#) AND " &_
(Requests.Time<=#" &DateAdd("d", 1, finishdate)& "#)"
HitCount=0
MaxH=0
Set rsHitsList = objConn.Execute(SQLQuery)
Do Until rsHitsList.EOF
HitHours(DatePart("h",CDate(rsHitsList("Time"))))=
HitHours(DatePart("h",CDate(rsHitsList("Time"))))+1
MaxH = Max(HitHours(DatePart("h",CDate(rsHitsList("Time")))), MaxH)
Statistic by hours <br>
<i>Legend:</i><br>
<img src="bar1.gif" width=10 height=8> - users<br>
<img src="bar2.gif" width=10 height=8> - hits<br>
<!-- Draw chart by stretching small pictures -->
<table border="0" cellspacing="0" cellpadding="2" align="CENTER">
<tr align="CENTER" valign="BOTTOM">
<% for i=0 to 23%>
<td><font face="Arial" size="1">
<%
if UsrHours(i)<>0 then
Response.Write(UsrHours(i))
end if
%>
</font><br>
<img src="bar1.gif" width=10 height=
<%= Round(UsrHours(i)/MaxU*100) %>
alt="" border="0">
</td>
<% Next %></tr>
<tr align="CENTER" valign="BOTTOM">
<% for i=0 to 23 %>
<td><font face="Arial" size="2"><b><%= i %>
</b>
</font></td>
<% Next%>
</tr>
</table>
<br>
<table border="0" cellspacing="0"
cellpadding="2" align="CENTER" nowrap>
<tr align="CENTER" valign="BOTTOM">
<% for i=0 to 23%>
<td><font face="Arial" size="1">
<%
if HitHours(i)< >0 then
Response.Write(HitHours(i))
end if
%>
</font><br>
<img src="bar2.gif" width=10 height=<
%= Round(HitHours(i)/MaxH*100) %> alt="" border="0">
</td>
<% Next %></tr>
<tr align="CENTER" valign="BOTTOM">
<% for i=0 to 23 %>
<td><font face="Arial" size="2"><b><%= i %></b>
</font></td>
<% Next%>
</tr>
Figure 4 - Shows the Results of Previous Code
Click Here To View Figure 4
We use just two small pictures to draw these charts. We control the height of each bar by specifying HEIGHT property of IMG tag. The value of the height is based on the ratio to maximum value and multiplying by 100 (i.e., the maximum height is 100 pixels). We also write the number of users and hits above the bars. We skip them for zero values. The function Max is not implemented in VBScript so we implement it.
Complete Requests Statistics
Usually we want logging applications to display not only all of the requests but the pages rating. And of course we prefer to see this information in chart form. Let’s add this feature to our reqdate.asp script. To avoid extra information we will display the virtual path to the page without the prefix http://yoursite. The requests will be grouped and sorted by rating. The same technology employed for displaying the user statistics is used to draw this chart.
Listing 4.6 reqdate.asp (second version)
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
' Helper function
Function Max(arg1, arg2)
if arg1<arg2 then
Max = arg2
else
Max = arg1
end if
End Function
</SCRIPT>
<%
' We want to show how many times every page was read
' "Page name" means substring after "http://yoursite"
' WARNING: hyperlinks with this convention will work only
' if application runs on the same virtual server as logged pages
SQLQuery = "SELECT Mid([URL], InStr(8,[URL],'/')) AS ShortURL, " &_
"Count(Requests.Time) AS CountOfTime " &_
"FROM Requests WHERE (Requests.Time>=#" & startdate & "#) " &_
"AND (Requests.Time<=#" &DateAdd("d", 1, finishdate)& "#) " &_
"GROUP BY Mid([URL], InStr(8,[URL],'/')) ORDER " &_
"BY Count(Requests.Time) DESC;"
Set rsReqList = objConn.Execute(SQLQuery)
if (rsReqList.BOF) and (rsReqList.EOF) then
Response.End
end if
' Calculations for proportional chart drawing
MaxR=0
Do Until rsReqList.EOF
MaxR = Max(MaxR, rsReqList("CountOfTime"))
rsReqList.MoveNext
Loop
rsReqList.MoveFirst
%>
<br><br>
<h2>Pages Rating</h2>
<table border="0" cellspacing="3" cellpadding="3" nowrap>
<%i=0
Do Until rsReqList.EOF%>
<tr valign="BASELINE">
<td ><a href="<%= rsReqList("ShortURL")%>"><font size="-1">
<%= rsReqList("ShortURL")%> </font></a> </td>
<td align="LEFT"><img src="bar_blue.gif" align="middle" height=15 width=
<%= Round(rsReqList("CountOfTime")/MaxR*400)%> alt="" border="0">
<font face="Arial" size="1"><%=rsReqList("CountOfTime")%>
</font> </td>
</tr>
<%
rsReqList.MoveNext
i=i+1
Loop
%>
</table>
</BODY>
</HTML>
Figure 5 - Displays The Result of Above Code
Click Here To View Figure 5
From Here…
That’s it! We have implemented the major features we require in a Web server statistics application. Next, you can implement complex queries and other specific tasks. In downloadable source code you’ll find features like weekly statistics and domain/location statistics. The real application should also have a better page design. Try modifying it first and you will understand the code better. Also, try to optimize queries, replace cursors with SQL statements.
Code for Download
The code available for download contains full source from the article and this bonus code: week statistics (Figure 6) and domain rating (Figure 7). Please note that the domain rating sample uses an advanced log database (a new table Hosts was added).
Figure 6 - Week Statistics
Click Here to View Figure 6
Figure 7 - Domain Rating
Click Here to View Figure 7
Click Here to Download Code from this Article
Click Here for Bonus Code
About the Author
Rod Denisyuk is a 19 years old student of Odessa State University, Ukraine. He has been programming with C++ for over 4 years and last summer he read "Working with Active Server Pages" and start using ASP. Now he takes part in the project "Russian Active Server Pages" http://www.activeserverpages.ru (russian language only). You can contact him via e-mail: rod_denisyuk@bigfoot.com or chat via ICQ 3029927.
|
