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

1. • 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)

1. • 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

1. • 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

1. • 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

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

1. • 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)

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.

1. • Query [WebAnalyticsSvcApp_ReportingDB].[dbo]. [WASetting] (This table only has four rows)

(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.

Comments

• Anonymous
  April 25, 2012
  Per your steps #6, what if the two datetime stamps do not match?  Where do i start trobuleshooting then? Web analytics used to work and stopped.  My environment passes all but step#6 where the two values are few months apart.  Morever, this about the last time web analytics worked. LastAggregationTime 2012-01-14T10:00:22.073 LastDataCopyTime       2012-04-25T20:59:19.630 Could you share your thoughts on what can i do next to resolve this issue. thanks.

• Anonymous
  May 03, 2012
  Shahzadh... I would start by increasing the ULS logging levels for the "Web Analytics Services" category. The reports typically get refreshed around 2am, so that's where I would start (check out a tool called ULSViewer for reviewing ULS logs). Do you see any messages with 'exception' or 'fail'? ...Are any related timer jobs failing? ...What account are you using for the Web Analytics Data Processing Service? Is it a Managed Account (Central Admin -> Security -> Configure Service Accounts)? ...Which App Pool is the Web Analytics Service Application using? From IIS, what user account is this App Pool using? ...From SQL Server, what accounts have permissions for the ReportingDB? Beyond that, I'd have to respectfully suggest opening a support ticket to help you dig in further. Hope that helps, --Brian

• Anonymous
  May 16, 2012
  It's a nice superb blog. I heartily congratulates to you to implement such new ideas. It's extremely terribly informative article and it taught me several ideas. Thanks for our co-operation towards its (blog) posting and developing means.

• Anonymous
  May 16, 2012
  It's a great amazing blog. I heartily congratulates to you to implement such new concepts. It's really very informative article and it taught me many concepts. Thanks for our co-operation towards its (blog) posting and developing way.

• Anonymous
  August 09, 2012
  Hi I checked all the things, and all seems to be ok, but I have one Question. How can I force Web Analytics to collect data & update Reports in Sharepoint? The Job Microsoft SharePoint Foundation Usage Data Processing does not do it - if I run it manually, but  each night get I the data collected. Can you help?

• Anonymous
  August 09, 2012
  The comment has been removed

• Anonymous
  August 30, 2012
  Brian, This was very helpful. In step 6 I am seeing NULL for LastAggregationTime. Any idea what would cause this?

• Anonymous
  September 11, 2012
  The property titled "ReportConsolidationStartTime" is available in the service application properties, but I am unable to determine the format of the property value. I guess we will have to peek into the assembly. As outlined in the architectural overview section of capacity planning, the webanalytics.exe process handles the task of consolidating report from staging to reporting database. Get-SPServiceApplication|?{$_.TypeName -eq "Web Analytics Service Applic ation"}|select Name,ReportConsolidationStartTime

• Anonymous
  October 31, 2012
  The format is int32... 2 would correspond to "Daily at 02:00:00 AM"

• Anonymous
  November 07, 2012
  A very useful blog, it's helped me out with a problem or two on the Service Application.  Thanks for posting.

• Anonymous
  January 17, 2013
  I can't find good values only in the ReportingDB. Everything else seems to be correct. I do have a local Development VM that is not turned on all day. Which Timer Job is responsible for copying data vom StagingDB to ReportingDB and how can I force this?

• Anonymous
  February 12, 2013
  Thank you so much! I kept pouring through the blogs to find out why my Web Analytics wasn't working. Turns out it was the Hotfix that you pointed out. MSFT should do a better job of pointing those out. Maybe to improve your document, you could have "Solution: " section under each number to document how to fix the issue?

• Anonymous
  March 28, 2013
  Was digging in the nooks & crannies of this blog's settings and just found several comments that weren't published (which is strange b/c some of the others came through..). In either case, apologies for being silent on responses and will do my best to go back and answer where I can :-)

• Anonymous
  April 02, 2013
  I'm affected by #6 above, where the reporting db is not aggregating the info from staging. Any suggestions on a fix to resolve this piece? Thanks. Patrick