Run SharePoint 2013 Search Reports from PowerShell–Part 2!

 

First… see the original article here: http://techtrainingnotes.blogspot.com/2015/04/run-sharepoint-2013-search-reports-from.html

What if you want reports for each site collection? All we need is another function, a "Get-SPSIte –Limit ALL" and some code to generate unique file names and we can now create hundreds to thousands of Excel files!!! (Please make sure you never fill your server's drives with this!)

 

# This is the path to write the reports to:
$path = "c:\SearchReports\"


function Get-SPSearchReports ($farmurl, $searchreport, $path)
{
  # 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"

  # create a unique filename for each site and report
  $sitename = $farmurl.Replace("http://","").Replace("https://","")
  $sitename = $sitename.substring(0,$sitename.IndexOf("/_layouts"))
  $sitename = $sitename.Replace("/","_").Replace(":","_").Replace(".","_")

  $filename = $path + $sitename + " " + 
              (Get-Variable $searchreport).Name + " " + 
              (Get-Date -Format "yyyy-mm-dd") + ".xlsx"
  #Write-Host "Creating $filename"

  $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
}


function Get-SPSearchReportsForSite ($url, $path)
{

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

}


Get-SPSite -Limit All | 
  foreach   {
    $url = $_.Url + "/_layouts/15/Reporting.aspx?Category=AnalyticsSiteCollection";
    #$path = (you might do a custom path for each set of reports)

    Write-Host "Getting files for $url"
    Get-SPSearchReportsForSite $url $path
  }