Home > Reporting Service, Sharepoint > SharePoint 2007 Usage Reports

SharePoint 2007 Usage Reports

For the past few weeks I was trying to develop some custom reports to track SharePoint site usage. The built-in reports show only the current statistics. But if someone wants to view old statistics there is no way that person can generate a report.

I started by going through the MOSS object model to find out some method where I can generate these reports easily. Then I found SPWeb.GetUsageData method. But it was giving enough problems and I decided to look for some other mean.

Finally I end up with a happy solution. It was so easy, you just connect to SharePoint shared services database and it has all the tables and views you need to generate the reports.

 

 

SharePoint Shared Services database

 

 

Available views

 

 

All the tables starting with ‘ANL’ contains site usage data.

 

I simply wrote few queries and developed required reports using SSRS. Or even you can use any reporting tool which is available in the market. Since I used SSRS I simply published it into a SharePoint site.

Following is a sample query I wrote to view the most popular documents in a SharePoint site.

 

SELECT ANLResource.DocName, COUNT_BIG(*) AS HitCount

FROM ANLResourceHits INNER JOIN

ANLWeb ON ANLResourceHits.WebGuid = ANLWeb.WebGuid INNER JOIN

ANLResource ON ANLResourceHits.ResourceId = ANLResource.ResourceId

WHERE (CHARINDEX(‘.aspx’, ANLResource.DocName) = 0) AND (CHARINDEX(‘.swf’,

ANLResource.DocName) = 0)

GROUP BY ANLResource.DocName

ORDER BY HitCount DESC

About these ads
  1. Anshuman Singh
    February 25, 2008 at 8:04 am | #1

    Its really great. Nothing to say more than than.

    good work…..

  2. April 28, 2008 at 9:54 am | #2

    It’s not really supported by MS that you actually gonna query the databases of SharePoint. It’s (still unwritten rule) in the SharePoint dev world never to use the database but try to make use of the OM and webservices as much as possible..

    But nevertheless your queries can be very useful ;)

  3. Wiseman
    July 8, 2008 at 7:15 pm | #3

    This is great info. I have been searching a couple days for this type of information.
    Do you have any other useful queries?
    I am looking at trying to find out how many users did NOT log in the past week or month.
    With the current contract I am on now we use FBA and I also need to connect and get the user first name, last name, email, region, and division. (Region and Division are custom we have added to the user profiles)

  4. sanika
    July 14, 2008 at 6:35 am | #4

    want more columns in result

  5. August 28, 2008 at 3:58 pm | #5

    nice work!

    any idea what table holds the search usage data?

  6. Kale
    September 30, 2008 at 8:17 pm | #6

    Thank you! This is what I was looking for and was wondering where that was stored at. Do you have any other queries you could share before I start down that road?

  7. pradeep
    April 21, 2010 at 9:56 am | #7

    hi kolitha,
    thanks for the post. it is so helpful to me. can you please tell me whether we can generate report on Disctict users for the past 30 days ? thanks in advance

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: