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.


Step1


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,


 CONSTRAINT [PK_HitLog] PRIMARY KEY CLUSTERED


(


      [ID] ASC


)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


) ON [PRIMARY]


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]


AS


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]


AS


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))


Step2


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>";


            newSqlConnection.Open();


            newSqlCommand.ExecuteNonQuery();


            newSqlConnection.Close();


        }


        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=1.0.0.0, 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


WHERE


          date_hit BETWEEN @StartDate AND @EndDate


GROUP BY [Hour]


ORDER BY 1, 2


 


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. mylordan@gmail.com 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:

    Fabiano,

    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