Saturday, February 9, 2008

How to find your document hits on SharePoint 2007

We recently had a need to find out how documents were being accessed on our SharePoint 2007 intranet. This need was again tied back to the original intent of
minimizing user clicks to important imformation. Hence we had to figure out the hit counts of all documents on our intranet and then organize the more accessed documents corporate wide on a page that would organize this information based on our classification and thus provide quick access to employees.

SharePoint OOB does not provide you with these level of reports across all your site collections. My friend Travis wrote up a nifty little SQL script that would do the trick. Enjoy!!


DECLARE @table TABLE(DocName varchar(4000), HitCount bigint)

DECLARE @doc varchar(4000)
DECLARE @hits bigint

DECLARE c_docs CURSOR FOR
SELECT DISTINCT DocName
FROM [MOSS_SSP_DB_Name_Here].[dbo].[ANLSiteResourceHits]
WHERE ([DocName] NOT LIKE '%.aspx') and ([DocName] NOT LIKE '%.html')
and ([DocName] NOT LIKE '%.asp') and ([DocName] NOT LIKE '%.htm')
and ([DocName] NOT LIKE '%.xml') and ([DocName] NOT LIKE '%.xsd')
and ([DocName] NOT LIKE '%.one') and ([DocName] NOT LIKE '%.xsn')

OPEN c_docs
FETCH NEXT FROM c_docs INTO @doc

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @hits = (SELECT SUM([HitCount])
FROM [MOSS_SSP_DB_Name_Here].[dbo].[ANLSiteResourceHits]
WHERE ([DocName] = @doc))

INSERT INTO @table VALUES (@doc, @hits)

Next_Row:
FETCH NEXT FROM c_docs INTO @doc
END

SELECT *
FROM @table
ORDER BY [HitCount] DESC

7 comments:

Anonymous said...

Hello
I cannot find the table you mention in my SSP database, Do I need to activate some options to do that.

Hope you can help me.

Regards,

André

Faraz said...

Hi Andre,
Are you using MOSS SP1 by any chance?

If so, try using the dbo.ANLResource table, that should work for you.

Anonymous said...

Hello,
Thanks for your quick response. I am using MOSS2007+SP1. I have searched the table but it is empty and it does not have the hitcount field (or something simmilar). I think I need to activate some option in the SSP? I will try tomorrow and get back to you.

Thank you for your help

André

gouri said...

Hi, Does the same technique worl for getting the document hit count in SPS 2003?

Srividya said...

HI
Tables in my ssp db are empty, i have anlhit and anlresource table and both are empty.
is there anything to be doneto get values into the tables

SG said...

Hi Faraz,

ANLResource table exists in Moss 2007 but there is no track of downloaded documents or excel file.
So How can we get track of documents and its total counts or total hits at item level.

Anonymous said...

Hello Faraz,

this is really a good infomation regarding one kind of auditng we can say by means of SQL SERVER.
need some help regarding this.. as this is a SQL script, could you please guide me step by step how to execute this and where we have make changes in that script.I tried to run this script but failed.
Hope u can help me!

Thanks Amol...