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