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