Wednesday 1 April 2020

Office 365/SharePoint Online - PowerShell Script to get Unique Visitors & Total Views count for Site Page

Problem Statement -

We have a client with a large SharePoint Tenant, want to know the Total Views count along with Unique Visitors count for each Post/Page, they are adding every day for their employees.

Analysis -

Currently, Microsoft is working on Analytics API that is supposed to give you the details regarding total view count and unique view count for specific item. Even though it is released but not working for a single item.

The alternative way is to use the SharePoint Search API. This will use Microsoft Classic Search results and provide you count based on that.

Resolution -

We have decided to go with the SharePoint Search API approach. We have found an article with the same idea. The only concern was, using Search API directly can return max 500 rows as a result at a time. So, we need to generalize that script in such a way that it can be executed for all items in the list/library.

So, here is generalized the Power-Shell script:

Step 1 - Load required dependencies/assemblies:
# Paths to SDK. Please verify location on your computer.  
 #Add-PSSnapin Microsoft.SharePoint.PowerShell  
 [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll")  
 [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll")  
 [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Search.dll") 

Step 2 - Create a Function - to get all list items and using search query to retrieve ViewsLifeTime & ViewsLifeTimeUniqueUsers for each item:
function Get-SPOListView  
 {  
   param(  
   [Parameter(Mandatory=$true,Position=1)]  
   [string]$Username,  
   [Parameter(Mandatory=$true,Position=2)]  
   $AdminPassword,  
   [Parameter(Mandatory=$true,Position=3)]  
   [string]$Url,  
   [Parameter(Mandatory=$true,Position=4)]  
   [string]$ListTitle  
   [Parameter(Mandatory=$true,Position=5)]  
   [string]$TenantUrl  
   )  
  #Get the SharePoint List/Library.
   $ctx=New-Object Microsoft.SharePoint.Client.ClientContext($Url)  
   $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username, $AdminPassword)  
   $ll=$ctx.Web.Lists.GetByTitle($ListTitle)  
   $ctx.load($ll)  
   $ctx.ExecuteQuery()  
  #Get all items from the List/Library.
   $qry = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()  
   $items = $ll.GetItems($qry)   
   $ctx.Load($items)  
   $ctx.ExecuteQuery()  
   foreach($listItem in $items)  
   {  
     Write-Host "ID - " $listItem["ID"] "Title - " $listItem["Title"] "EncodedAbsUrl - " $listItem["FileRef"]     
     $fileurl = $TenantUrl+ $listItem["FileRef"]  
     #Using Search API - Create the instance of KeywordQuery and set the properties.
     $keywordQuery = New-Object Microsoft.SharePoint.Client.Search.Query.KeywordQuery($ctx)   
     #Sample Query - To get the result of last year.
     $queryText="Path:" + $fileurl  
     $keywordQuery.QueryText = $queryText  
     $keywordQuery.TrimDuplicates=$false  
     $keywordQuery.SelectProperties.Add("ViewsLifeTime")  
     $keywordQuery.SelectProperties.Add("ViewsLifeTimeUniqueUsers")  
     $keywordQuery.SortList.Add("ViewsLifeTime","Asc")   
     #Search API - Create the instance of SearchExecutor and get the result.
     $searchExecutor = New-Object Microsoft.SharePoint.Client.Search.Query.SearchExecutor($ctx)  
     $results = $searchExecutor.ExecuteQuery($keywordQuery)  
     $ctx.ExecuteQuery()  
     #Result Count  
     Write-Host $results.Value[0].ResultRows.Count  
     #CSV file location, to store the result  
     $exportlocation = "C:\Pages_ViewsCount - Copy.csv"  
     foreach($result in $results.Value[0].ResultRows)  
     {  
       $outputline='"'+$result["Title"]+'"'+","+'"'+$result["Path"]+'"'+","+$result["ViewsLifeTime"]+","+$result["ViewsLifeTimeUniqueUsers"]   
       Add-Content $exportlocation $outputline   
     }   
     #}  
   }  
 }

Step 3 - Call the above function:
# Insert the credentials along with Admin & Tenant URLs and Call above Function.  
 #Enter Username here  
 $Username="username@sharepoint.com"  
 #Enter Password Here  
 $Password=Read-Host -Prompt "Password" -AsSecureString  
 #URL of the site collection  
 $rootUrl= "Root Site collection URL"  
 $ListTitle= "Site Pages"  
 $TenantUrl= "Your tenant URL E.g. https://YourCompany.sharepoint.com"  
 Get-SPOListView -Username $Username -AdminPassword $Password -Url $rootUrl -ListTitle $ListTitle -TenantUrl $TenantUrl 

It will export all items of the list with Total views count - ViewsLifeTime & Unique views count - ViewsLifeTimeUniqueUsers in excel sheet as shown in below image:


In case you need the dll files, you can download from below link
https://drive.google.com/file/d/1P9YzJUvCeAfnZ8xIr-7SKCN2fisbRP6a/view?usp=sharing

References