4/21/2015

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
  }

2 comments:

Anonymous said...

This does not work in SharePoint 2016, the ID's are different.

Mike Smith said...

The updated IDs can be found here:

http://techtrainingnotes.blogspot.com/2018/02/run-sharepoint-2013-and-2016-search.html

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.