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.

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

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