Freigeben über

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

  • Artikel

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

  • Anonymous
    May 13, 2009
    PingBack from http://asp-net-hosting.simplynetdev.com/moss-2007-%e2%80%93-create-your-own-customized-usage-report-solution-step-by-step/

  • Anonymous
    May 14, 2009
    SharePoint Create your own customized usage report solution step by step SharePoint WebPart Property

  • Anonymous
    May 28, 2009
    Hello, I would like to know if is possible to do that in WSS.

  • Anonymous
    May 29, 2009
    Fabiano, Yes this is possible in WSS but RS Add-ins are only available in MOSS. So you cannot publish your report in WSS.

  • Anonymous
    June 01, 2009
    Como cada mes, desde hace ya un par de a&ntilde;os, aqu&iacute; os dejo el cl&aacute;sico recopilatorio

  • Anonymous
    July 07, 2009
    Can you elaborate on step 2.  Not sure how to create the HTTPmodule.  Visual Studio needed?

  • Anonymous
    July 10, 2009
    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.

  • Anonymous
    August 18, 2009
    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

  • Anonymous
    April 06, 2010
    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.

  • Anonymous
    July 09, 2010
    I want program for adding hit count for a document

  • Anonymous
    November 01, 2010
    context.Request.Url.AbsoluteUri get lots of files, such as: css, js, gif ...