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

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
}