MOSS 2007 – create your own customized usage report solution step by step

Usage report in MOSS 2007 gives you somewhat fare information about site usage. But if you need customized report to satisfy your specific requirement, then there is hardly any option to extend or customize those reports. For example I want usage reports should show following information for a specific web application:

1.    Total and average hits per hour

2.    Total and average hits from various location (IP)

3.    Total and average hits per url

In general usage reports use these tables from <SharedServicesName>_DB (in my case it is SharedServices1_DB):

dbo.ANLHit, dbo.ANLHistoricalWebUsage, dbo.ANLHistoricalSiteUsage etc.

But it’s a kind of taboo to use the underlying db tables of SharePoint. But we can create our own application to store the usage history in our own db table(s) and expose those data using SSRS reports through MOSS dashboard.


Create your own database in SQL Server and add following table and views:

CREATE TABLE [dbo].[HitLog](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [url] [varchar](260) NOT NULL,

      [user_name] [varchar](50) NOT NULL,

      [hit_time] [datetime] NOT NULL,

      [user_ip] [varchar](50) NULL,



      [ID] ASC



CREATE View [dbo].[Temp_Hits_per_hour] as

SELECT  DATEADD(dd, 0, DATEDIFF(dd, 0, hit_time)) AS date_hit, DatePart(HH, hit_time) as [Hour], Count(*) as Total_Hits FROM HitLog

GROUP BY DatePart(HH, hit_time), DATEADD(dd, 0, DATEDIFF(dd, 0, hit_time))

CREATE VIEW [dbo].[View_HitByIP]


SELECT     COUNT(*) AS number_of_hits, DATEADD(dd, 0, DATEDIFF(dd, 0, hit_time)) AS date_hit, user_ip

FROM         dbo.HitLog

GROUP BY user_ip, DATEADD(dd, 0, DATEDIFF(dd, 0, hit_time))

CREATE VIEW [dbo].[View_HitByURL]


SELECT     COUNT(*) AS number_of_hits, DATEADD(dd, 0, DATEDIFF(dd, 0, hit_time)) AS date_hit, url

FROM         dbo.HitLog

GROUP BY url, DATEADD(dd, 0, DATEDIFF(dd, 0, hit_time))


Create a class library project to create a HTTPModule:

using System;

using System.Collections.Generic;

using System.Web;

using System.Web.UI;

using System.Data;

using System.Data.SqlClient;


namespace HitModule


    public class HitModule : IHttpModule


        public void Init(HttpApplication app)


            app.PreRequestHandlerExecute += new EventHandler(app_PreRequestHandlerExecute);


        void app_PreRequestHandlerExecute(object sender, EventArgs e)


            HttpContext context = HttpContext.Current;

            string url = context.Request.Url.AbsoluteUri;

            string userIP = context.Request.UserHostAddress;

            string userName = context.User.Identity.Name;


            string insertSql = "insert into HitLog(url, user_name, user_ip, hit_time) values('" + url + "','" + userName + "','" + userIP + "',getdate())";

            SqlConnection newSqlConnection = new SqlConnection();

            SqlCommand newSqlCommand = new SqlCommand(insertSql, newSqlConnection);

            newSqlCommand.CommandType = System.Data.CommandType.Text;

            newSqlConnection.ConnectionString = "user id=<userid>;password=<password>;Initial Catalog=<dbname>;Data Source=<sql server name>";





        public void Dispose()





Step 3

 Add the HTTPModule’s reference into the web.config file of your desired web application.

      <add name="HitModule" type="HitModule. HitModule, HitModule, Version=, Culture=neutral, PublicKeyToken=9f4da00116c38ec5" />


Step 4


Create 3 parameterized reports using SQL Server Business Intelligence Development Studio. Here are the select commands for the datasets of those reports:


select top 20 URL, Sum(Number_of_hits) as total_number_of_hits,Convert(decimal(10,2), Sum(Number_of_hits))/DateDiff(dd, @StartDate, @EndDate) as Avg_Hits

from View_HitByURL

where date_hit between @StartDate and @EndDate

group by url

order by  total_number_of_hits Desc


SELECT [Hour], SUM(Total_Hits) as Total_Hits,Convert(decimal(10,2), SUM(Total_Hits))/(DateDiff(dd, @StartDate ,@EndDate) + 1) as Avg_Hits FROM Temp_Hits_Per_Hour


          date_hit BETWEEN @StartDate AND @EndDate




select sum(number_of_hits) total_number_of_hits,Convert(decimal(10,2), Sum(number_of_hits))/DateDiff(dd, @StartDate, @EndDate) as Avg_Hits, user_ip

from View_HitByIP

where date_hit between @StartDate and @EndDate

group by user_ip


Step 5


Publish those reports in a dashboard page of your MOSS report center.

Comments (11)

  1. says:

    context.Request.Url.AbsoluteUri get lots of files, such as: css, js, gif …

  2. suresh says:

    I want program for adding hit count for a document

  3. Robert says:

    Your reporting tool works great, but when I set a refernce to it in the web.config file, I am unable to add columns to SharePoint Lists.  When I comment out the reference in the web.config file, all is well.  Do you have any idea why your dll would prohibit a user from adding columns to SharePoint lists?  Thanks.

  4. likk says:

    Thanks a lot . It’s very useful.  

    I have a question.

    The Url in non-English is not readable 。

    I try to use

    string url = HttpUtility.UrlDecode(context.Request.Url.AbsoluteUri);

    but It’s not working for me.

    any ideas ? thx a lot

  5. Curious says:

    I was able to get this working…  However, I had problems with certain pages in report center erroring out.  The solution was to increase the url field in the new DB from 260 character to 2000.

    Thanks for the solution.

  6. Curious says:

    Can you elaborate on step 2.  Not sure how to create the HTTPmodule.  Visual Studio needed?

  7. Como cada mes, desde hace ya un par de a&ntilde;os, aqu&iacute; os dejo el cl&aacute;sico recopilatorio

  8. Pranab-MSFT says:


    Yes this is possible in WSS but RS Add-ins are only available in MOSS. So you cannot publish your report in WSS.

  9. Fabiano Brito says:

    Hello, I would like to know if is possible to do that in WSS.

  10. SharePoint Create your own customized usage report solution step by step SharePoint WebPart Property

Skip to main content