Tuesday, 8 November 2016

Office 365 / SharePoint online - PowerShell script to call Search API and get the result

Hi All,

In this article, I'll explain using KeywordQuery class from PowerShell script and write results to .CSV file.  I’ll also explain the scenario where we need to use Search from the PowerShell script.

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

One thought came to mind is -  Audit Reports which we supposed to use in 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 dosent works, it says as following image, even though Auditing is enabled.
Figure 1 : SharePoint Online : Audit Log Report
These audit reports never worked for us in SharePoint online. So option remains using Search and manage 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 returned any result.

Our customer wants the result in excel sheet and its really difficult to get content search webpart 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 as
 
#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 as
 
#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 
}

Thanks!
 
Enjoy Reading :)
 
As usual any comment / suggestions / feedback / questions always welcome :).

2 comments:

Sathya G said...

All the details are explained clearly with the great explanation. Thanks for this wonderful blog. Step by step processes execution are given clearly.Know the details about different thing.
Digital Marketing Training in Chennai

aarthi rengarajan said...

This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge. xcellent post!!! Our Digital Marketing Training is tailored for beginners who want to learn how to stand out digitally, whether it is for their own business or a personal brand.

Digital Marketing Training in Chennai
Digital Marketing Course in Chennai
Digital Marketing Courses in Chennai
SEO Training in Chennai
What is Digital Marketing
Digital Marketing
Digital Marketing Company
SEO Services in India