SPO : PowerShell Script to get report of all subsites for a given site collection

  • Article

In SharePoint Online, most of the times, a Site Owner would like to get subsites report of their site collection.  Here is a sample PowerShell script which uses CSOM to get all users with Full control permission in all the subsites (recursively) under the given Site Collection and outputs in an Excel sheet.  The script can be modified as needed to get other properties of a subsite.

 

Add-Type –Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"

Add-Type –Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

 

$siteURL = Read-Host -Prompt "Enter Site Collection URL"

$username = Read-Host -Prompt "Enter User Name"

$password = Read-Host -Prompt "Password for $username" -AsSecureString

[Microsoft.SharePoint.Client.ClientContext]$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)

$clientContext.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)

 

$excel = New-Object -ComObject Excel.Application

$excel.Visible = $true

$workbook = $excel.Workbooks.Add()

$sheet = $workbook.ActiveSheet

$counter = 1

$sheet.cells.Item($counter,1) = 'Site URL'

$sheet.cells.Item($counter,2) = 'Unique Permission'

$sheet.cells.Item($counter,3) = 'Users with Full Control Permission'

$sheet.cells.Item($counter,1).font.bold = $True

$sheet.cells.Item($counter,2).font.bold = $True

$sheet.cells.Item($counter,3).font.bold = $True

$sheet.cells.Item($counter,1).Interior.ColorIndex = 8

$sheet.cells.Item($counter,2).Interior.ColorIndex = 8

$sheet.cells.Item($counter,3).Interior.ColorIndex = 8

 

Function Invoke-LoadMethod() {

param(

[Microsoft.SharePoint.Client.ClientObject]$Object = $(throw "Please provide a Client Object"),

[string]$PropertyName

)

$ctx = $Object.Context

$load = [Microsoft.SharePoint.Client.ClientContext].GetMethod("Load")

$type = $Object.GetType()

$clientLoad = $load.MakeGenericMethod($type)

$Parameter = [System.Linq.Expressions.Expression]::Parameter(($type), $type.Name)

$Expression = [System.Linq.Expressions.Expression]::Lambda(

[System.Linq.Expressions.Expression]::Convert(

[System.Linq.Expressions.Expression]::PropertyOrField($Parameter,$PropertyName),

[System.Object]

),

$($Parameter)

)

$ExpressionArray = [System.Array]::CreateInstance($Expression.GetType(), 1)

$ExpressionArray.SetValue($Expression, 0)

$clientLoad.Invoke($ctx,@($Object,$ExpressionArray))

}

function Get-SPOWebs(){

param(

$Url = $(throw "Please provide a Site Collection Url"),

$Credential = $(throw "Please provide a Credentials")

)

$context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)

$context.Credentials = $Credential

$web = $context.Web

$context.Load($web)

$context.Load($web.Webs)

$context.ExecuteQuery()

foreach($web in $web.Webs)

{

Get-SPOWebs -Url $web.Url -Credential $Credential

Invoke-LoadMethod -Object $web -PropertyName "HasUniqueRoleAssignments"

$context.ExecuteQuery()

$outputString = ''

$siteGroups = $web.SiteGroups

$context.Load($web.AssociatedOwnerGroup)

$context.ExecuteQuery()

$ownersGroup = $siteGroups.GetByName($web.AssociatedOwnerGroup.Title)

$context.Load($ownersGroup)

$context.ExecuteQuery()

$ownersGroupUsers = $ownersGroup.Users

$context.Load($ownersGroupUsers)

$context.ExecuteQuery()

$ownersGroupUsers | ForEach-Object {$outputString += $_.Email + ';' }

$rows = $sheet.UsedRange.Rows.Count + 1

$sheet.cells.Item($rows ,1) = $web.Url

$sheet.cells.Item($rows ,2) = $web.HasUniqueRoleAssignments.ToString()

$sheet.cells.Item($rows ,3) = $outputString

$outputString = ''

}

}

Get-SPOWebs -Url $siteURL -Credential $clientContext.Credentials