Creating a Log Visit Report


oops.. today I’d decided to post a new article but surprisingly noticed that the blog was suspended! Thanks to Matt, it is alive and kicking now!

Anyway, lets see how we can generate a log visit report for our ASP.NET web site. By the term of Log Visit, I mean the statistics of a website which shows at which time, which page (or file) has been viewed by who!

The good news is that IIS can record the above information for us. It can log all activities done by your web site in the following ways and formats:

W3C Extended log file format

World Wide Web Consortium (W3C) Extended format is customizable and is saved in an ASCII text file. You can select your desired fields among a lot of choices.

IIS log file format

IIS log file format has  fixed columns and is stored as an ASCII text file. This file includes more information than W3C format.

NCSA Common log file format

National Center for Supercomputing Applications (NCSA) Common log file format is a fixed ASCII format that is available for Web sites, but not for FTP sites

ODBC logging

Open Database Connectivity (ODBC) logging format is a record of a fixed set of data properties in a database.

Logging into text files is quicker than logging into a database. It does not have any performance pitfall but when it comes to reporting, you probably should import the text file into a database.

Logging by ODBC will slow down your website to some extent  because it records very much of (sometimes unnecessary) information. For example, in W3C format, only information about pages who have been loaded successfully is stored. While in ODBC method, all referred objects, like .js or image files, are recorded, no metter if the resource have been loaded successfully or not.

By the way, since ODBC will make this post shorter, I will choose ODBC method for my sample program.

To enable logging with ODBC, first you must create a table for storing log information and then create an ODBC data source thorough which IIS can store logs.

To create the log table, which is usually called INETLOG, go to System32\InetSrv folder and find LogTemp.Sql file. This file includes a script that creates the required table into your database. This script has been written for Sql Server, but you can modify it to be executed on other databases like MS Access or even My Sql.

In the next step, create a SYSTEM DNS that points to the above table. You must create a System DNS because IIS will use the DNS with a user different from the one you have logged into your computer (usually NetWork Service account).

To create a new System DSN, go to Control Panel-> Administrative Tools and then double-click Data Sources (ODBC) . Then, go to System DNS tab and click Add. Select SQL Server, enter the name of your server and database and specify a user/password. You would better enter a SQL SERVER login (do not choose Windows Authentication) and specify a password that will not be changed.

After doing so, change the default database of this DSN to the database that logging table resides on. Click OK and close the dialog box.

Now go to Control Panel -> Administrative Tools  and double-click on Internet Information Services Manager.

Open Web Sites, and then find your Virtual Directory or Web Site. Right-click on it, choose properties and under the Home Directory tab, find Enable Logging check box. Enable the check box and In the Active log format list box, select ODBC. Afterwards, click on Configure button to view the ODBC settings. In this dialog box, enter the name of ODBC DSN you created in the previous step, name of the table (inetlog), user name and password of logging data base.

For example:

ODBC: WordPressLogDSN

Table: InetLog

Login: sa

Password: 123

In the above sample I used “SA” SQL SERVER’s default login name. This is a risky job and I do not recommend you do it! You would better use a user name with no administrative permission which only has access to logging table or database.

From now on, any visit to a page will be recorded!

NOTICE: The logging table will be filled with tons of records, therefore, I strongly recommend you to store InetLog table on a seperate FileGroup. Also, You would better to schedule a script that removes unnecessary records from inetlog table.

Writing The Code

In the rest of the post I am going to write a sample page that displays how many times each .aspx or .html page has been visited. I will show the stats in numeric and bar chart format.

As I mentioned, data about any referred resources will be stored in InetLog table. Thus, we should be able to determine if a record belongs to a Page or not. Only pages whose ServiceStatus is equal to 200 have been loaded or access with no error. So, I need a Sql function that determines if a resource is a page (i.e. .aspx) and if it has been accessed successfully.

I will call the function IsPage. The code is very simple so I just bring the script body here:

create function IsPage(   @PageName varchar(200) )
returns int
begin
Declare @len int
set @len = len(@PageName)

if Substring(@PageName, @Len, 1) = ‘/’
set @PageName = Substring(@PageName,1,@Len-1)

Declare @Extension  Varchar(4)
Set @Extension = Upper(Substring(@PageName,@Len – 3 , 4))

if @Extension = ‘ASPX’ or @Extension = ‘HTML’
return 1
return  0
end

I will use this function to create my stats and also to remove records of non-page resources like .js files (if I don’t need them).

At the next step I will write a stored procedure that determines how many times each page has been visited. To let the script run on older versions of Sql Server, I have written it as simple as possible:

create procedure rep_LogVisit
@BeginDate smalldatetime,
@EndDate smalldatetime
as
select Count(1) as FileCount, target as PageName
from inetlog
where ServiceStatus = 200 and dbo.isPage(target) =1
and LogTime>=@BeginDate and LogTime<=@EndDate
group by dbo.GetFileName(target)
go

As the above code indicates, rep_LogVisit counts the records who’s ServiceStatus equals to 200 (successful) , the resource is a page and the log has been recorded between a given date.

In order to display the result, I use a GridView control, having two columns. One for the page name, and one for the visit count:

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”

CellPadding=”4″ ForeColor=”#333333″

GridLines=”None”>

<Columns>

<asp:BoundField DataField=”target” HeaderText=”Page Name” />

<asp:TemplateField HeaderText=”Visit Count”>

<ItemTemplate>

<asp:Panel runat=”server” ID=”divBar” style=”background-color:Red” Width='<%#GetLength((int)Eval(“TotalCount”)) %>’>

<asp:Label runat=”server” ID=”lblCount” text='<%#Eval(“TotalCount”) %>’ ForeColor=”White”></asp:Label>

</asp:Panel>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

Well, I will call  rep_LogVisit stored procedure, bind the grid to it’s result. I also have to write a function called GetLength in order to calculate the lenth of each bar:

int MaxCount = 0;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

SqlConnection conn = new SqlConnection(“Your Connection String Here”);

SqlCommand cmd = conn.CreateCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = “rep_LogVIsit”;

// Add date parameters here. skipped to simplify the code.

DataTable table = new DataTable();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

adapter.Fill(table);

// Get the bigger visit count

MaxCount = (int)(from C in table.AsEnumerable()

select C).Max(c=>c[“TotalCount”]);

GridView1.DataSource = table;

GridView1.DataBind();

}

}

// Calculate the length of each bar

internal int GetLength(int TotalCount)

{

if (MaxCount == 0)

return 0;

else

return Convert.ToInt32(Math.Round(((double)TotalCount / MaxCount)*300));

}

At run-time, you will see something like the image bellow:

untitled1

Advertisements

2 thoughts on “Creating a Log Visit Report

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s