SharePoint - Keeping site usage data longer than 30 days

This post is aimed at Moss 2007, but should be applicable to WSS 2007 and SharePoint 2010.

As I am sure many of you are aware that Microsoft only keeps site usage data for a rolling 30 days. After that the data is purged. Also the out of the box reports (Site Settings - Site Usage Reports) are pretty limited. So we are going to extract that data and put it into our own database so we can keep the content as long as we would like, and we can also run SQL reports off of it.

Warning: Microsoft does not allow direct database access, so proceed at your own risk.

4 of the 5 tables we need are located in your SSP database.

• ANLDay
• ANLHit
• ANLResource
• ANLUser

The other table we need is located in each of your Content databases. For this example we are just going to assume you only have 1 content db.

• Webs

You will want to write a SSIS (DTS) package to run on a nightly schedule to copy the data from the 5 above tables into a new database that you create. That way you will ensure that you copy all records since they are kept only for a rolling 30 days.

Here are the queries I have written to copy the content. I am inserting the data into a new db called Site_Usage.

ANLDay Import

insert into Site_Usage.dbo.ANLDayBackup (DayId, FullDate)

select DayId, FullDate

from SharePoint_Portal_SSP.dbo.ANLDay

where not exists (select DayId, FullDate

from Site_Usage.dbo.ANLDayBackup

where ANLDayBackup.DayId = ANLDay.DayId)

ANLHit Import

insert into Site_Usage.dbo.ANLHitBackup (Id, Dayid, ResourceId, UserId)

select Id, Dayid, ResourceId, UserId

from SharePoint_Portal_SSP.dbo.ANLHit

where not exists (select Id, Dayid, ResourceId, UserId

from Site_Usage.dbo.ANLHitBackup

where ANLHitBackup.Id = ANLHit.Id)

ANLResource Import

insert into Site_Usage.dbo.ANLResourceBackup (ResourceId, DocName, FullUrl)

select ResourceId, DocName, FullUrl 

from SharePoint_Portal_SSP.dbo.ANLResource

where not exists (select ResourceId, DocName, FullUrl

from Site_Usage.dbo.ANLResourceBackup

where ANLResourceBackup.ResourceId = ANLResource.ResourceId)

ANLUser Import

insert into Site_Usage.dbo.ANLUserBackup (UserId, UserName)

select UserId as 'Id', UserName

from SharePoint_Portal_SSP.dbo.ANLUser

where not exists (select UserId, UserName

from Site_Usage.dbo.ANLUserBackup

where ANLUserBackup.UserId = ANLUser.UserId)

Webs Import

insert into Site_Usage.dbo.Webs (Fullurl, Title)

select fullurl, title

from moss_communities_content.dbo.Webs --name of your content db

where not exists (select fullurl, title

from Site_Usage.dbo.webs

where moss_communities_content.dbo.Webs.fullurl = Site_Usage.dbo.webs.fullurl

collate database_default)

Now we have all of the tables copied into our new Site_Usage db, and we can keep it updated on a nightly basis. That means we can now run SQL reports against it that contain data from the day before. 

Examples of some of the possibilities for those SQL reports will be in a future post.