Office 365/Sharepoint Online - PowerShell Script To Call Search API And Get The Result

Prasham Sabadra

6 Comments

Join the conversation! Your thoughts help the community grow.

mgm tech9y ago
I got an error below
mgm tech9y ago
At C:\scripts\Queryfile\SearchFile.ps1:46 char:238+ ... ["Created"]+","+$result["ModifiedBy"]+","+$result["LastModifiedTime"] + ~~ The string is missing the terminator: ". + CategoryInfo : ParserError: (:) [], ParseException + FullyQualifiedErrorId : TerminatorExpectedAtEndOfStringI got an error with the script.
Praveen Shetty9y ago
I want to archive this in SharePoint online site O365 .
Praveen Shetty9y ago
HI Prasham, First of all thanks for detail script which helped me to archive my requirement . Using this script max I could get 500 search results into Excel file . But basically my search results are giving 25K(25000) result and I need all these results item to be exported into Excel file . Could please help me how can I archive this .
- Prasham Sabadra9y ago
  Hi Praveen, In step 4 on line 06 RowLimit 500 is specified. Please comment that line. So that you will get all result and you could export to all result to .csv.
- anil kuchi8y ago
  Hi Prasham ,But in CSOM there is a limitation of 500 rows .Can you please let me know if you were able to iterate and get all the results

Our client has an Office 365 tenant and is very heavily using SharePoint online. We have one big SharePoint online site collection. Site collection has lots of content and there is also unwanted content available. The customer wants to clean up the site collection. Customer requested to get all the items/documents which are not updated since last year. Customer also wants the view count / open count for respective list items and documents.

One thought came to mind, that is - Audit Reports which we were supposed to use in the On Premises version. So we went to Site Settings >> Site Collection Administration >> Audit log reports (https://myTenant/_layouts/15/Reporting.aspx?Category=Auditing)

But in SharePoint online Audit log reports don't works, it says, even though Auditing is enabled.

Figure 1 : SharePoint Online : Audit Log Report

These audit reports never worked for us in SharePoint online. So, the option remains of using Search and managing meta data property “ViewsLifeTime” and “ViewsLifeTimeUniqueUsers”. We configured the Content Search webpart and could see the result for some data. For some data these properties didn’t return any result.

Our customer wants the result in excel sheet and its really difficult to get content search Web Part result in excel sheet though we can write from display template.

Finally, we decided to write the PowerShell script and call the search APIs like KeywordQuery from the PowerShell script.

Here, I’ll explain step by step how to call search APIs from PowerShell script.

Step 1

Load the required libraries

# add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Search.dll"

Step 2

Set the required variables like root SiteCollection URL, tenant admin UserName, and Password of your site to connect.

#Specify tenant admin and URL
$User = ""
#Configure Site URL and User
$SiteURL = ""
#Password
$Password =" "
$securePassword = ConvertTo-SecureString -String $Password -AsPlainText –Force
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$securePassword)

Step 3

Get the Microsoft.SharePoint.Client.ClientContext instance and set the credentials.

#client context object and setting the credentials
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Context.Credentials = $Creds

Step 4

Creating instance of KeywordQuery class and setting, selecting the properties.

#Calling Search API - Create the instance of KeywordQuery and set the properties
$keywordQuery = New-Object Microsoft.SharePoint.Client.Search.Query.KeywordQuery($Context)
#Sample Query - To get the last year result
$queryText="Path:https://MySitePath/ AND LastModifiedTime<2015-11-05 AND Created<2015-11-05"
$keywordQuery.QueryText = $queryText
$keywordQuery.RowLimit=500
$keywordQuery.TrimDuplicates=$false
$keywordQuery.SelectProperties.Add("LastModifiedTime")
$keywordQuery.SelectProperties.Add("ViewsLifeTime")
$keywordQuery.SelectProperties.Add("ModifiedBy")
$keywordQuery.SelectProperties.Add("ViewsLifeTimeUniqueUsers")
$keywordQuery.SelectProperties.Add("Created")
$keywordQuery.SelectProperties.Add("CreatedBy")
$keywordQuery.SortList.Add("ViewsLifeTime","Asc")

Step 5

Creating instance of SearchExecutor class and getting the result.

#Search API - Create the instance of SearchExecutor and get the result
$searchExecutor = New-Object Microsoft.SharePoint.Client.Search.Query.SearchExecutor($Context)
$results = $searchExecutor.ExecuteQuery($keywordQuery)
$Context.ExecuteQuery()
#Result Count
Write-Host $results.Value[0].ResultRows.Count

Step 6

Writing result to CSV file.

#CSV file location, to store the result
$exportlocation = "G:\MCH\MCHS Marketing Team details\Admin_ViewsCount.csv"
foreach($result in $results.Value[0].ResultRows)
{
$outputline='"'+$result["Title"]+'"'+","+'"'+$result["Path"]+'"'+","+$result["ViewsLifeTime"]+","+$result["ViewsLifeTimeUniqueUsers"]+","+$result["CreatedBy"]+","+$result["Created"]+","+$result["ModifiedBy"]+","+$result["LastModifiedTime"]
Add-Content $exportlocation $outputline
}