Post
Ask Question

Office 365/SharePoint Online - PowerShell Script to Get and Export Site Pages ViewsLifeTimeUniqueUsers & ViewsLifeTime Details

Introduction

 
We have a client with a big SharePoint tenant, and they want to get the unique visitor and total view count for a site collection posts that they have every day.
 
Analysis
 
We received a very good article from Prasham Sabadra related to this. Please see the reference link for this. He has done something similar using the search API.  
 
Solution
 
We have decided to go with the SharePoint Search API approach. We have found an article with the same idea. The only concern was that using Search API directly can return max 500 rows as a result at a time. So, we need to generalize that script so that it can be executed for all items in the list/library.
 
So, here is generalized the Power-Shell script:
 
Step 1
 
Load the required dependencies/assemblies:
  1. # Paths to SDK. Please verify location on your computer.      
  2. #Add-PSSnapin Microsoft.SharePoint.PowerShell      
  3. [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll")      
  4. [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll")      
  5. [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 use the search query to retrieve ViewsLifeTime & ViewsLifeTimeUniqueUsers for each item:
  1. function Get - SPOListView {  
  2.     param(  
  3.         [Parameter(Mandatory = $true, Position = 1)][string] $Username,  
  4.         [Parameter(Mandatory = $true, Position = 2)] $AdminPassword,  
  5.         [Parameter(Mandatory = $true, Position = 3)][string] $Url,  
  6.         [Parameter(Mandatory = $true, Position = 4)][string] $ListTitle[Parameter(Mandatory = $true, Position = 5)][string] $TenantUrl)  
  7.     #Get the SharePoint List / Library.  
  8.     $ctx = New - Object Microsoft.SharePoint.Client.ClientContext($Url)  
  9.     $ctx.Credentials = New - Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username, $AdminPassword)  
  10.     $ll = $ctx.Web.Lists.GetByTitle($ListTitle)  
  11.     $ctx.load($ll)  
  12.     $ctx.ExecuteQuery()  
  13.     #Get all items from the List / Library.  
  14.     $qry = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()  
  15.     $items = $ll.GetItems($qry)  
  16.     $ctx.Load($items)  
  17.     $ctx.ExecuteQuery()  
  18.     foreach($listItem in $items) {  
  19.         Write - Host "ID - "  
  20.         $listItem["ID"]  
  21.         "Title - "  
  22.         $listItem["Title"]  
  23.         "EncodedAbsUrl - "  
  24.         $listItem["FileRef"]  
  25.         $fileurl = $TenantUrl + $listItem["FileRef"]  
  26.         #Using Search API - Create the instance of KeywordQuery and set the properties.  
  27.         $keywordQuery = New - Object Microsoft.SharePoint.Client.Search.Query.KeywordQuery($ctx)  
  28.         #Sample Query - To get the result of last year.  
  29.         $queryText = "Path:" + $fileurl  
  30.         $keywordQuery.QueryText = $queryText  
  31.         $keywordQuery.TrimDuplicates = $false  
  32.         $keywordQuery.SelectProperties.Add("ViewsLifeTime")  
  33.         $keywordQuery.SelectProperties.Add("ViewsLifeTimeUniqueUsers")  
  34.         $keywordQuery.SortList.Add("ViewsLifeTime", "Asc")  
  35.         #Search API - Create the instance of SearchExecutor and get the result.  
  36.         $searchExecutor = New - Object Microsoft.SharePoint.Client.Search.Query.SearchExecutor($ctx)  
  37.         $results = $searchExecutor.ExecuteQuery($keywordQuery)  
  38.         $ctx.ExecuteQuery()  
  39.         #Result Count  
  40.         Write - Host $results.Value[0].ResultRows.Count  
  41.         #CSV file location, to store the result  
  42.         $exportlocation = "C:\Pages_ViewsCount - Copy.csv"  
  43.         foreach($result in $results.Value[0].ResultRows) {  
  44.             $outputline = '"' + $result["Title"] + '"' + "," + '"' + $result["Path"] + '"' + "," + $result["ViewsLifeTime"] + "," + $result["ViewsLifeTimeUniqueUsers"]  
  45.             Add - Content $exportlocation $outputline  
  46.         }  
  47.         #  
  48.     }  
  49. }  
  50. }  
Step 3
 
Call the above function:
  1. # Insert the credentials along with Admin & Tenant URLs and Call above Function.      
  2. #Enter Username here      
  3. $Username="[email protected]"      
  4. #Enter Password Here      
  5. $Password=Read-Host -Prompt "Password" -AsSecureString      
  6. #URL of the site collection      
  7. $rootUrl= "Root Site collection URL"      
  8. $ListTitle= "Site Pages"      
  9. $TenantUrl= "Your tenant URL E.g. https://YourCompany.sharepoint.com"      
  10. 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 them from below link.
 
Reference