Troubleshooting SharePoint 2010 Web Analytics

Web Analytics reports are pre-built reports in Microsoft SharePoint Server 2010 that use usage data to analyze various aspects of sites and site collections. But when things break down, it’s not always obvious how to troubleshoot the various moving pieces. The following steps should help you find the barrier as you follow the flow from the .Usage files on the web front ends (WFEs) into the LoggingDB and then on to the StagingDB and ReportingDB for the Web Analytics Service Application.

Troubleshooting Steps

1. Verify that the Usage and Health Data Collection Service Application and its Proxy both show Started from Central Admin -> Application Management -> Manage Service Applications

    • If the Proxy shows Stopped, use PowerShell cmdlets such as the following to restart it:
      $svcProxy = Get-SPServiceApplicationProxy | where-object {$_.TypeName –eq “Usage and Health Data Collection Proxy”}
      $svcProxy.Provision()

2. Verify that the .USAGE files are being generated on the WFE(s)

    • The location of these files are specified in Central Admin -> Monitoring -> Reporting -> Configure usage and health data collection
    • Within the Usage Data Collection Settings section, find the path specified for Log file location
    • If this directory contains files with the .USAGE extension and recent/current Last Modified dates, continue to the next step

3. Verify that the SharePoint Logging database (LoggingDB) contains current information

    • The location of these files are specified in Central Admin -> Monitoring -> Reporting -> Configure usage and health data collection
    • Within the Logging Database Server section, find the path specified for Database Server and Database Name
    • From the specified LoggingDB, query from the RequestUsage VIEW to verify that the data that is being collected from the .USAGE files and the timestamps are current
      • If the data is in the LoggingDB then the problem is not with any of the steps up to this point. Continue to the next step.
      • If the data is NOT in the LoggingDB:
        1. Review Configure usage and health data collection (see above) to verify the Usage Data Collection and the Event Selection section specifies which events to log
        2. Review the Microsoft SharePoint Foundation Usage Data Import timer job status
        3. Note: The following TechNet article will help with foundational checks in this area as well (https://technet.microsoft.com/en-us/library/ff453926.aspx)

4. Verify the usage information is not exceeding the daily partition size for the LoggingDB

    • In the SharePoint ULS logs, check for messages such as the following:
      03/15/2012 09:30:04.78 OWSTIMER.EXE (0x1E24) 0x2C98 SharePoint Foundation Health i0m6 High    
      Table RequestUsage_Partition19 has 444858368 bytes that has exceeded the max bytes 444858368    
      Note: In a future post, I will further discuss this particular issue

Hint: For steps 5 and 6 below, run the following PowerShell to find the name of the StagingDB and ReportingDB:

Get-SPServiceApplication | Where {$_.TypeName -like "Web Analytics*"} | fl

5. Verify the data is successfully extracted from the LoggingDB into the StagingDB

    • Query [WebAnalyticsSvcApp_StagingDB].[dbo]. [WASetting] (This table only has three rows)
    • From the output, review the LastLoggingExtractionTime to verify it is current and then continue to the next step (the time stamps are UTC, so be sure to convert to your timezone)
SettingName SettingValue
LastLoggingExtractionTime    2012-03-14T23:20:15.320

 

 

6. Verify the time that data was last copied from the StagingDB to the ReportingDB and the last time that data was successfully aggregated in the ReportingDB.

    • Query [WebAnalyticsSvcApp_ReportingDB].[dbo]. [WASetting] (This table only has four rows)
SettingName SettingValue
LastAggregationTime  2011-12-14T08:00:05.483
LastDataCopyTime 2011-12-14T07:59:04.760

 

(Updated 16-April-2012)

7. If all else fails, turn up the ULS levels for the Web Analytics Services category (Central Admin -> Monitoring -> Configure Diagnostic Logging) and look for errors. A good place to start is ~2am as this is when the Reports get refreshed (and I'm unfortunately not aware of any way to configure the time of this refresh) 

 

Additional Resources:  
Introducing Web Analytics in SharePoint 2010
https://blogs.msdn.com/b/ecm/archive/2010/03/21/introducing-web-analytics-in-sharepoint-2010.aspx
 
Configure usage and health data collection (SharePoint Server 2010)
https://technet.microsoft.com/en-us/library/ee663480.aspx
 
Configure Web Analytics service application (SharePoint Server 2010)
https://technet.microsoft.com/en-us/library/gg266382.aspx
 
Capacity requirements for the Web Analytics Shared Service in SharePoint Server 2010 (see "Architectural Overview" section)
https://technet.microsoft.com/en-us/library/gg440601.aspx
 
Monitoring overview (SharePoint Server 2010)
https://technet.microsoft.com/en-us/library/ee748636.aspx
 
Description of the SharePoint Server 2010 hotfix package (June 2010 CU) 
https://support.microsoft.com/?id=2204024
 
Disclaimer:
ALL information in this blog is provided "AS IS" with no warranties and confers no rights. This blog does not represent the thoughts, intentions, plans or strategies of my employer. All content is solely my opinion and provided with a best effort to be based in reality. All examples, code samples, demonstrations, or anything resembling a “how-to” are provided "AS IS" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. Inappropriate comments will be deleted at the authors discretion.