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.
