SharePoint 2010: Get Web Analytics Summary for all Site Collections

Although web analytics report gives the ability to view reports at a site collection level, there seems to be no option to get the summary for all site collections in the farm to do a comparative analysis. We can use the following query against the Web Analytics Reporting database to get the summary for all site collections.

It's not recommended to query against SharePoint databases. This is something that can be run in a non-production environment whose content databases and service application databases have been provisioned from snapshot of production.

The right approach would be use web analytics APIs as outlined in http://blogs.technet.com/b/sharepointdevelopersupport/archive/2012/10/04/how-to-retrieve-web-analytics-report-data-using-api.aspx

USE [WebAnalyticsServiceApplication_ReportingDB]

 

DECLARE @SiteId UniqueIdentifier

DECLARE @AggregationId UniqueIdentifier

DECLARE @SitePath NVarchar(255)

DECLARE @StartDate Int

DECLARE @EndDate Int

 

DECLARE @AllSitesWASummary TABLE

(SiteCollectionId UniqueIdentifier,

 SiteCollectionAggId UniqueIdentifier,

 SitePath NVarchar(255),

 WAStartDate Int,

 WAEndDate Int,

 PropertyName NVarChar(255),

 CurrentValue Int,

 PreviousValue Int,

 PercentageChange Int

 )

 

 --Set the Date Range through Start and End Dates in YYYYMMDD number format

SET @StartDate = 20110915

SET @EndDate = 20110715

 

DECLARE WACursor CURSOR

FOR

--Get the SiteID and AggregationID, Site Collection Relative Url can also be referred from Config Database

SELECT DISTINCT DimensionName,AggregationId,SM.[Path]

FROM WASiteInventorySnapshot WASIS WITH (NOLOCK)

INNER JOIN [SharePoint_Config_2010].[dbo].[SiteMap] SM WITH (NOLOCK)

ON WASIS.DimensionName = SM.Id

WHERE WASIS.DimensionType=0

 

 

OPEN WACursor

 

FETCH NEXT FROM WACursor

INTO @SiteId,@AggregationId,@SitePath

 

WHILE @@FETCH_STATUS =0

BEGIN

 

INSERT INTO @AllSitesWASummary

(SiteCollectionId,SiteCollectionAggId,SitePath,WAStartDate,WAEndDate,

 PropertyName,CurrentValue,PreviousValue,PercentageChange)

 

--Get WebAnalytics Summary

SELECT @SiteId AS 'SiteId',@AggregationId AS 'AggregationId',@SitePath AS 'SitePath',@StartDate 'StartDate',@EndDate 'EndDate',

* FROM

[WebAnalyticsServiceApplication_ReportingDB].[dbo].[fn_WA_GetSummary]

(20110915,20110715,90,@AggregationId,1)

 

FETCH NEXT FROM WACursor

INTO @SiteId,@AggregationId,@SitePath

 

END

SELECT * FROM @AllSitesWASummary

CLOSE WACursor

DEALLOCATE WACursor