Run SharePoint 2013 and 2016 Search Reports from PowerShell

Updated to include IDs for SharePoint 2016!   Original article here.

Update! Need these reports for every site collection in the farm? See Part 2: http://techtrainingnotes.blogspot.com/2015/04/run-sharepoint-2013-search-reports-from_21.html

In my Search Administration class I stress that admins should dump the search reports on a regular basis as the data is only kept in detail for 14 days and in summary form for 35 months. But who wants to both run these reports at least once every 14 days, even they can remember to do so. So, PowerShell to the rescue… Schedule this script to run each weekend and your work is done.

The following script works for on premise SharePoint 2013. To work with Office 365 you will have to figure out how to include your credentials. The example included here works on premises by using "UseDefaultCredentials = $true".

After lots of hacking, detective work (see below) and just plain trial and error, here's the script:

# This is the URL from YOUR Central Admin Search Service Usage Reports page:
#
# The script will not work unless this is correct!
#
$url = "http://yourCentralAdminURL/_layouts/15/reporting.aspx?Category=AnalyticsSearch&appid=ed39c68b%2D7276%2D46f7%2Db94a%2D4ae7125cf567"  

# This is the path to write the reports to (must exist, but can be anywhere):
$path = "c:\SearchReports\"




function Get-SPSearchReports ($farmurl, $searchreport, $path, $version)
{
  # TechTrainingNotes.blogspot.com

  if ($version -eq "2013")
  {
    # Report names and IDs
    $Number_of_Queries          = "21be5dff-c853-4259-ab01-ee8b2f6590c7"
    $Top_Queries_by_Day         = "56928342-6e3b-4382-a14d-3f5f4f8b6979"
    $Top_Queries_by_Month       = "a0a26a8c-bf99-48f4-a679-c283de58a0c4"
    $Abandoned_Queries_by_Day   = "e628cb24-27f3-4331-a683-669b5d9b37f0"
    $Abandoned_Queries_by_Month = "fbc9e2c1-49c9-44e7-8b6d-80d21c23f612"
    $No_Result_Queries_by_Day   = "5e97860f-0595-4a07-b6c2-222e784dc3a8"
    $No_Result_Queries_by_Month = "318556b1-cabc-4fad-bbd5-c1bf8ed97ab1"
    $Query_Rule_Usage_by_Day    = "22a16ae2-ded9-499d-934a-d2ddc00d406a"
    $Query_Rule_Usage_by_Month  = "f1d70093-6fa0-4701-909d-c0ed502e3df8"
  }
  else # 2016
  {
    $Number_of_Queries          = "df46e7fb-8ab0-4ce8-8851-6868a7d986ab"
    $Top_Queries_by_Day         = "06dbb459-b6ef-46d1-9bfc-deae4b2bda2d"
    $Top_Queries_by_Month       = "8cf96ee8-c905-4301-bdc4-8fdcb557a3d3"
    $Abandoned_Queries_by_Day   = "5dd1c2fb-6048-440c-a60f-53b292e26cac"
    $Abandoned_Queries_by_Month = "73bd0b5a-08d9-4cd8-ad5b-eb49754a8949"
    $No_Result_Queries_by_Day   = "6bfd13f3-048f-474f-a155-d799848be4f1"
    $No_Result_Queries_by_Month = "6ae835fa-3c64-40a7-9e90-4f24453f2dfe"
    $Query_Rule_Usage_by_Day    = "8b28f21c-4bdb-44b3-adbe-01fdbe96e901"
    $Query_Rule_Usage_by_Month  = "95ac3aea-0564-4a7e-a0fc-f8fdfab333f6"
  }

  $filename = $path + (Get-Variable $searchreport).Name + " " + (Get-Date -Format "yyyy-mm-dd")  + ".xlsx"
  $reportid = (Get-Variable $searchreport).Value

  $TTNcontent = "&__EVENTTARGET=__Page&__EVENTARGUMENT=ReportId%3D" + $reportid

  # setup the WebRequest
  $webRequest = [System.Net.WebRequest]::Create($farmurl)
  $webRequest.UseDefaultCredentials = $true
  $webRequest.Accept = "image/jpeg, application/x-ms-application, image/gif, application/xaml+xml, image/pjpeg, application/x-ms-xbap, */*"
  $webRequest.ContentType = "application/x-www-form-urlencoded"
  $webRequest.Method = "POST"

  $encodedContent = [System.Text.Encoding]::UTF8.GetBytes($TTNcontent)
    $webRequest.ContentLength = $encodedContent.length
    $requestStream = $webRequest.GetRequestStream()
    $requestStream.Write($encodedContent, 0, $encodedContent.length)
    $requestStream.Close()

  # get the data
  [System.Net.WebResponse] $resp = $webRequest.GetResponse();
    $rs = $resp.GetResponseStream();
    #[System.IO.StreamReader] $sr = New-Object System.IO.StreamReader -argumentList $rs;
    #[byte[]]$results = $sr.ReadToEnd();
    [System.IO.BinaryReader] $sr = New-Object System.IO.BinaryReader -argumentList $rs;
    [byte[]]$results = $sr.ReadBytes(10000000);

  # write the file
  Set-Content $filename $results -enc byte
}


# Note: Change the version to 2013 or 2016

Get-SPSearchReports $url "Number_of_Queries" $path "2013"
Get-SPSearchReports $url "Top_Queries_by_Day" $path "2013"
Get-SPSearchReports $url "Top_Queries_by_Month" $path "2013"
Get-SPSearchReports $url "Abandoned_Queries_by_Day" $path "2013"
Get-SPSearchReports $url "Abandoned_Queries_by_Month" $path "2013"
Get-SPSearchReports $url "No_Result_Queries_by_Day" $path "2013"
Get-SPSearchReports $url "No_Result_Queries_by_Month" $path "2013"
Get-SPSearchReports $url "Query_Rule_Usage_by_Day" $path "2013"
Get-SPSearchReports $url "Query_Rule_Usage_by_Month" $path "2013"
 

The Detective Work…

I could not find anything documented on how the reports are called or details on things like the report GUIDs. So here's how I got there:

• Go the search reports page in Central Admin and press F12 to open the Internet Explorer F12 Developer Tools then:
  • Click the Network tab and click the play button to start recording.
  • Click one of the report links.
  • Double-click the link generated for the report in the F12 pane to open up the details.
  • Make note of the URL (It's the same as the report page!)
  • Note the Accept, and Content-Type Request Headers.
  • Click the Request Body tab.
  • Stare at 3000 characters in that string until your head really hurts, or until you recognize most of what is there is the normal page postback stuff like VIEWSTATE. So we need to find what's unique in the string. (It's the Report IDs.)
  • Click on each of the nine reports and copy out the report IDs.
  • With a lot of trial and error figure out what the minimum string needed is to generate the reports. (It's ""&__EVENTTARGET=__Page&__EVENTARGUMENT=ReportId" plus the report id.)
  • Find out how to do an HTTP POST using PowerShell. (Steal most of it from here: http://www.codeproject.com/Articles/846061/PowerShell-Http-Get-Post.)
  • Find some other needed .Net code and convert the C# to PowerShell.
  • Fill in some gaps with PowerShell putty …….

.