How to Query SharePoint Web Analytics Reports from SQL Server

This time, I want to show you on how you can query the SharePoint Web Analytics Reports from your SharePoint 2010. It seems that some people still confused because on my previous post, I didn’t give any examples or anything to proof this. I need to apologize, and in this post, I’ll write about how to Query it first, so you can get what I ‘m trying to explain.

I think image will tell you much than words…

That is the result of my query in SQL Server, I got the Top Pages on one of my SharePoint Web Application. So, to reach that result, below is the steps I took before.

Determine what type of Analytics Report I would need, is it Site Collection (SPSite) or Site (SPWeb)?

I grab my home-made application, GetAggregationId in Command Prompt, to get the Aggregation ID for my Site / Site Collection. This must be done in SharePoint machine box. A little forgot right there, what is the parameters, but finally…

Yay… got it for SPWeb and SPSite level.

Then I looked at the MS-MAVA Documentation, what query I could use and modify it based on my requirements. I want to get the Top Pages on my SharePoint site.

Open the SQL Server Management Studio, and point to Web Analytics Report Database. If you unsure where and what is the Web Analytics Report Database, you can read my previous post about Web Analytics.
After a serious modifications of the Top Pages query, now this is my final query.

Query for Web Analytics

1. USE WebAnalytics_RPT_Service_DB
2. exec sp_executesql
3. N'SELECT TOP (@MaxRow) [PageId], [Frequency], [Percentage]
4. FROM [dbo].[fn_WA_GetTopPages](@Start, @End, @AssetGuid, default)
5. ORDER BY [Frequency] DESC',
6. N'@Start int,@End int,@AssetGuid uniqueidentifier,@MaxRow int',
7. @Start=20110101,@End=20120505,@AssetGuid='793474d4-6cc3-9228-6e94-6c761a0c5080',@MaxRow=10

You may want to notice, what is @Start, @End, @AssetGuid, and @MaxRow are. @AssetGuid is the Aggregation ID for your query. While @MaxRow specifies max rows returned for this query. @Start and @End is the start date and end date for the query in yyyyMMdd format. So, if you want to input start date for Jan 5th 2012, so the @Start must be 20120105. Notice that there’s no URL parameter in this query, all is set up by using Aggregation ID.

Aaaaaand…

Congratulations… you’ve already done it!