PowerShell Script to Workaround No Data in SharePoint 2013 Usage Reports

Brian T. Jackett PowerShell, Search, SharePoint

   Over the past few months I’ve had 2 customers that have run into an scenario where the SharePoint 2013 web analytics usage reports have no data (all zeroes) in the reports.  While working with some brilliant Microsoft escalation engineers (thanks Anthony and Jason) we were able to run some PowerShell scripts that added receivers to start data showing again on the following day.  Since I haven’t seen any posts on this as of yet I thought I would post a version of the PowerShell scripts we used.

 

Scenario

    In SharePoint 2013 the search service application incorporates web analytics (which is a separate service application in SharePoint 2010).  Web analytics processes usage logs on the SharePoint machines and generates reports on a daily schedule.  These reports can be viewed for an individual site in the site settings under Site Collection Administration > Popularity and Search Reports.

UsageReportDataEmpty1

 

   In the Popularity and Search Reports you can click on the Usage report which will launch an Excel workbook.

UsageReportDataEmpty2

 

   What I found with 2 customers and one of my lab farms was that the Usage report contained all zeroes for data even though the customer (and me in my lab farm) had been using the site regularly with multiple accounts over the past few days.

UsageReportDataEmpty3

 

   We analyzed the logging database and found that it had usage data, but the search analytics database did not.  (Note: do not directly query the search analytics database as that is unsupported as of the time this post was written.  See http://technet.microsoft.com/en-us/library/cc678868.aspx for more information.)  So it appeared the data in the logging database wasn’t being processed by the search service web analytics timer jobs.  After verifying that the timer jobs were indeed running the long road of PowerShell queries into the system began.  We finally used the below commands to arrive at what we believe to be the culprit for these customers.  Our findings follow the commands.

 

$aud = Get-SPUsageDefinition | where {$_.Name -like “Analytics*”} 
$aud | fl 

$prud = Get-SPUsageDefinition | where {$_.Name -like “Page Requests”}  
$prud | fl
  • AnalyticsUsage usage definition had no Receivers defined
  • PageRequest usage definition had no Receivers defined

UsageReportDataEmpty4

 

    Not having any Receivers defined also led to the EnableReceivers property to be set to false for both.

 

Workaround

   The workaround in these scenarios was to manually create the Receivers.  The PowerShell commands to do so is below (slightly modified to check for empty receivers first).  Again this sample script is provided as-is with no warranty.  Do not run this in your environment without first testing.  This is not an official Microsoft approved script.  You can download a copy off my SkyDrive folder as well.

 

if((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell) -eq $null) 
{ 
    Add-PSSnapin Microsoft.SharePoint.PowerShell 
} 

$aud = Get-SPUsageDefinition | where {$_.Name -like “Analytics*”} 
# if analytics usage definition receivers is empty then manually add back receiver 
if($aud.Receivers.Count -eq 0) 
{ 
    $aud.Receivers.Add(“Microsoft.Office.Server.Search.Applications, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c”, “Microsoft.Office.Server.Search.Analytics.Internal.AnalyticsCustomRequestUsageReceiver”) 
} 
# if analytics usage definition receiver is not enabled then enable it 
if($aud.EnableReceivers -eq $false) 
{ 
    $aud.EnableReceivers = $true 
    $aud.Update() 
} 
$aud | fl 
$prud = Get-SPUsageDefinition | where {$_.Name -like “Page Requests”}  
# if page requests usage definition receivers is empty then manually add back receiver 
if($prud.Receivers.Count -eq 0) 
{ 
    $prud.Receivers.Add(“Microsoft.Office.Server.Search.Applications, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c”, “Microsoft.Office.Server.Search.Analytics.Internal.ViewRequestUsageReceiver”)  
} 
# if page requests usage definition receiver is not enabled then enable it 
if($prud.EnableReceivers -eq $false) 
{ 
    $prud.EnableReceivers = $true 
    $prud.Update() 
} 
$prud | fl

   After the script has been run the output from the prior commands can confirm that Receivers have been created and the EnableReceivers property is set to true.

UsageReportDataEmpty5

 

<Update 2013-08-09>

   The next step is to recycle the OWSTimer service (SharePoint Timer Service) on each server.  This ensures that the new receivers are properly picked up by the timer jobs.

</Update 2013-08-09> 

  Waiting one day the usage reports were now showing data.  (Note the below report was mocked up manually to show data as I did not have direct access to the customers’ reports, but this is consistent with what we had seen after the scripts were applied.)

UsageReportDataEmpty6

 

Conclusion

   This is a strange scenario of no data in the usage reports when there is data in the logging databases.  I’ve run into it myself and with 2 customers, but when I tried to reproduce the scenario I couldn’t.  If anyone is facing this issue hopefully this process of manually creating the usage definition receivers and waiting 24 hrs is a workaround.  Let me know if you have seen this and if the workaround works for you.  Curious to learn more on it.

 

      -Frog Out

41 thoughts on “PowerShell Script to Workaround No Data in SharePoint 2013 Usage Reports”

  7. Originally posted on: https://briantjackett.com/archive/2013/08/26/powershell-script-to-workaround-no-data-in-sharepoint-2013-usage.aspx#633362My both event handlers are working fine and I restarted the timer job, still I got 0 count ( for Unique user and hits). but this was working fine two months backMy current report ( usage)================July – i have recordsAugust – I have recordsSep – 0Oct -0But for “Number of Queries” i have values….Kindly give me a solution for this….

    Like

    Reply

  19. Originally posted on: https://briantjackett.com/archive/2013/08/26/powershell-script-to-workaround-no-data-in-sharepoint-2013-usage.aspx#642691I am facing this issue at the page level. As you know, you can click the Popularity Trends menu on each page to view usage data for that page. In my SharePoint deployment, I can see usage data for every page except one which is the Default.aspx page. When I queried the AnalyticsItemData table, it showed LastProcessingTime of Dec 15, 2014.

    Like

    Reply

  20. Originally posted on: https://briantjackett.com/archive/2013/08/26/powershell-script-to-workaround-no-data-in-sharepoint-2013-usage.aspx#642822Hi Brian, Thanks for your wonderful scripts. Unfortunately, which I ran the customer’s machine. On SharePoint 2013 app and front end servers. and ran timer job for all servers but even after 2 days no data was shown up. I tried other options such as: i. Microsoft SharePoint Foundation Usage Data and ii. Microsoft SharePoint Foundation Usage Data Processing. Both were enabled. I re-run the job schedules again. iii. C:Program FilesMicrosoft Office Servers15.0DataOffice ServerAEStore today’s and past folders were there. however, I did not verify the search service application and WSS_Logging database. Any other pointers that I could run the scripts so that usage report are available for the customer?Thank you.

    Like

    Reply

  22. Originally posted on: https://briantjackett.com/archive/2013/08/26/powershell-script-to-workaround-no-data-in-sharepoint-2013-usage.aspx#643692Hi Brian,We have two site collections in our environment. One is root site collection and another one is searchcenter site collection. Usage reports are showing data for root site collection but not for searchcenter site collections. However other reports like “number of queries” and “top queries by day” reports are showing data. I have checked logging database, it has data in it. All jobs are running fine and also we don’t see any errors in search crawl jobs. Also these receivers are enabled. Couldn’t see any errors in log files too.Can you please help with this issue.

    Like

    Reply

  28. Originally posted on: https://briantjackett.com/archive/2013/08/26/powershell-script-to-workaround-no-data-in-sharepoint-2013-usage.aspx#646809I have the same issue on my SP2013 farms, activity was working some time ago but after reverting the farms it displays 0, I have reviewed the receivers and both are configured in the same way as yours, DB is showing activity but report is displaying 0, do you have any idea of my problem?.Thanks in advance!! PS: Nice post BTW!

    Like

    Reply

  29. Originally posted on: https://briantjackett.com/archive/2013/08/26/powershell-script-to-workaround-no-data-in-sharepoint-2013-usage.aspx#647264Most popular items is specific to site collection?last month i have fixed the issue for one site( Sub site) and Most popular items were showing as expected. After 2 weeks another user reported the issue for different issue and fixed the issue and now results were showing as expected. What is the permanent fix. Popularity trends is specific to site.

    Like

    Reply

  34. We only see usage data from the point when we applied this fix. I would have thought this should include all detail from when Usage&Health was enabled in the farm (ie; day 1)

    Like

    Reply

  35. This should have worked out of the box. Thank Brian. I gather since there was no reporting point specified in the configuration you would not be able to restore the old logs. They data probably exists but reposting will require some more research.

    Like

    Reply

    • I haven’t found a way to restore old logs but also haven’t researched much. Part of this will depend on how old the missing data is and whether or not the source data exists in the logging database (default name WSS_Logging). There might be a way to force re-processing of data into the search analytics DBs but I haven’t checked before.

      Like

      Reply

  36. Hi Brian,

    I having this issue for a specific site collection. I have several SharePoint sites from where I can get usage report successfully except from one specific SharePoint site.

    This is in SharePoint 2016 farm.

    Any idea how to fix this issue.

    Like

    Reply

  37. Brian,
    Thanks for your response. Yes the following timer jobs are configured correctly.
    Microsoft SharePoint Foundation ‘Usage Data Import’ , Usage Data Maintenance , Usage Data Processing.
    Also, search is working fine. **The site collection in our discussion** was not added in the content source.
    I created a new content source and ran a ‘Full Crawl’ , Still the issue persist
    Also, the below script to show the analytics information is not returning any information, where while running the
    $searchApp = Get-SPEnterpriseSearchServiceApplication
    $site = Get-SPSite “https://Sharepoint site”
    $result = $searchApp.GetRollupAnalyticsItemData(1,[System.Guid]::Empty,$site.ID,[System.Guid]::Empty)
    $result
    same script for other sharepoint site returns the following information.
    EventType : 1
    SiteId :
    ScopeId :
    TenantId :
    ItemId :
    LastProcessingTime :
    CurrentDate :
    TotalHits :
    TotalUniqueUsers :
    LastProcessingHits :
    LastProcessingUniqueUsers :
    any idea…

    Like

    Reply

  39. Hello Brian,

    Thank you for your Great Article.
    I was searching this article from many days and now i got it.
    In our SP2013 environment(2 servers), we also receiving the 0 count on Popularity Trend report.
    I have executed your complete script in the Application server and recycle the OWSTimer service (SharePoint Timer Service) on both the servers. Then waited for 1 day for Search Crawler completion time and then checked the Popularity Trend report which is showing some values rather than 0.

    Thank you very much for your wonderful article who is facing the same issues on their SP servers.

    Liked by 1 person

    Reply

Leave a comment