Written by 
I try to loop into all SharePoint sites, libraries, folders and documents and check which one are shared with more as 50 members. This script is very slow at this moment. Does someone have some PowerShell performance optimalisation tips?
The bigest problem is I think the part where I need to loop into all folders and documents. Some sites have thousands of documents.
function countInheritedPermissionFiles {
param (
[Microsoft.SharePoint.Client.ClientObject]$folder,
[string]$listName
)
$folderItems = Get-PnPFolderItem -Identity $folder
foreach($folderItem in $folderItems) {
if($folderItem.TypedObject.ToString() -eq "Microsoft.SharePoint.Client.Folder") {
$folderByPnP = Get-PnPFolder -Url $folderItem.ServerRelativeUrl -Includes ListItemAllFields.HasUniqueRoleAssignments, Folders
if(!$folderByPnP.ListItemAllFields.HasUniqueRoleAssignments) {
countInheritedPermissionFiles $folderByPnP $listName
}
} else {
$item = Get-PnPListItem -List $listName -UniqueId $folderItem.UniqueId
$HasUniquePermissions = Get-PnPProperty -ClientObject $item[0] -Property "HasUniqueRoleAssignments"
if(!$HasUniquePermissions) {
$global:inheritedPermissionsFileCount ++
}
}
}
}
function getPermissions($_object) {
# make arrays empty
$global:permissionUsers = @()
$global:permissionType = @()
$global:permissionLevels = @()
$roleAssignments = Get-PnPProperty -ClientObject $_object -Property RoleAssignments
#Loop through each permission assigned and extract details
Foreach($roleAssignment in $_object.RoleAssignments)
{
#Get the Permission Levels assigned and Member
Get-PnPProperty -ClientObject $roleAssignment -Property RoleDefinitionBindings, Member
if($roleAssignment.Member.LoginName -notlike "*Limited Access*" -and $roleAssignment.RoleDefinitionBindings[0].Name -notlike "*Beperkte toegang*" -and $roleAssignment.RoleDefinitionBindings[0].Name -notlike "*Limited Access*") {
#Get the Permission Levels assigned, Remove Limited Access
foreach($roleDefinitionBinding in $roleAssignment.RoleDefinitionBindings) {
if(!$global:permissionLevels.Contains($roleDefinitionBinding.Name)) {
$global:permissionLevels += $roleDefinitionBinding.Name
}
}
#Check if the Principal is SharePoint group
If($roleAssignment.Member.PrincipalType -eq "SharePointGroup")
{
if(!$global:permissionType.Contains("SharePointGroup")) {
$global:permissionType += "SharePointGroup"
}
#Get Group Members
$GroupMembers = Get-PnPGroupMember -Identity $roleAssignment.Member.LoginName
foreach($groupMember in $GroupMembers) {
if(!$global:permissionUsers.Contains($groupMember.Email) -and $groupMember.LoginName -notlike "*c:0o.c|federateddirectoryclaimprovider|*" -and $groupMember.Email -ne "") {
$global:permissionUsers += $groupMember.Email
}
}
}
Else #User
{
if(!$global:permissionType.Contains("DirectUser")) {
$global:permissionType += "DirectUser"
}
if(!$global:permissionUsers.Contains($roleAssignment.Member.Email)) {
$global:permissionUsers += $roleAssignment.Member.Email
}
}
}
}
}
#Parameters
$dateTime = (Get-Date).toString("dd-MM-yyyy-hh-mm-ss")
$invocation = (Get-Variable MyInvocation).Value
$directorypath = Split-Path $invocation.MyCommand.Path
$fileName = "M365OversharingReport.csv"
$reportOutput = $directorypath + "\Report\"+ $fileName
$traceLoggingFolderPath = $directorypath + "\Logs\"
$traceLoggingFilename = "transcript-${datetime}.log"
$transcriptLogFileFullPath = Join-Path -Path $traceLoggingFolderPath -ChildPath $traceLoggingFilename
$global:permissionUsers = @()
$global:permissionType = @()
$global:permissionLevels = @()
$global:inheritedPermissionsFileCount = 0
$recordsCount = 0
$minUsersCount = 50
$adminSiteURL = "https://myCompany-admin.sharepoint.com"
$tenant = "myCompany.onmicrosoft.com"
$spSiteDomain = "https://myCompany.sharepoint.com"
$certThumbprint = "something"
$appId = "someGuid"
LogEntry("START")
Connect-PnPOnline -Url $adminSiteURL -ClientId $appId -Thumbprint $certThumbprint -Tenant $tenant
$excludedSites = @("https://myCompany.sharepoint.com/sites/AppCatalog", "https://myCompany-my.sharepoint.com/", "https://myCompany.sharepoint.com/", "https://myCompany.sharepoint.com/search", "https://myCompany.sharepoint.com/sites/allcompany")
$excludedLists = @("Access Requests", "App Packages", "appdata", "appfiles", "Apps in Testing", "Cache Profiles", "Composed Looks", "Content and Structure Reports", "Content type publishing error log", "Converted Forms",
"Device Channels", "Form Templates", "fpdatasources", "Get started with Apps for Office and SharePoint", "List Template Gallery", "Long Running Operation Status", "Maintenance Log Library", "Images", "site collection images"
, "Master Docs", "Master Page Gallery", "MicroFeed", "NintexFormXml", "Quick Deploy Items", "Relationships List", "Reusable Content", "Reporting Metadata", "Reporting Templates", "Search Config List", "Site Assets", "Preservation Hold Library",
"Site Pages", "Solution Gallery", "Style Library", "Suggested Content Browser Locations", "Theme Gallery", "TaxonomyHiddenList", "User Information List", "Web Part Gallery", "wfpub", "wfsvc", "Workflow History", "Workflow Tasks", "Pages")
# Haal alle sitecollecties op
LogEntry("Get all SharePoint sites")
$sitesCollection = Get-PnPTenantSite
LogEntry("Found '$($sitesCollection.Count)' SharePoint sites")
# Read CSV and to skip the processed sites
$processedSites = @()
if (Test-Path $reportOutput) {
$processedSites = Import-Csv $reportOutput | Where-Object { $_.Status -eq "ProcessedOversharing" -or $_.Status -eq "ProcessedExcludedSite" -or $_.Status -eq "ProcessedNoOversharing" } | Select-Object -ExpandProperty SiteUrl
LogEntry("Found '$($processedSites.Count)' SharePoint sites already analysed in CSV file")
}
$siteCounter = 0
# Loop into all sitecollection
ForEach($site in $sitesCollection) {
$siteCounter++
LogEntry("Site $siteCounter / $($sitesCollection.Count)")
# Check if this site is already processed
if ($processedSites -contains $site.Url) {
LogEntry("Site $($site.Url) is already processed, skip it and do nothing...")
continue
}
if(!$excludedSites.Contains($site.Url) -and !$site.Url.Contains(($spSiteDomain + "/portals"))) {
Connect-PnPOnline -Url $site.Url -ClientId $appId -Thumbprint $certThumbprint -Tenant $tenant
$skipped = $true
$owners = ""
If($Site.Template -like 'GROUP*') {
# Get M365 Group owner
try {
$owners = (Get-PnPMicrosoft365GroupOwners -Identity ($Site.GroupId) | Select-Object -ExpandProperty Email) -join "; "
}
Catch [Exception] {}
} Else {
# Get site owner
try {
$ownergroup = Get-PnPGroup -AssociatedOwnerGroup
$owners = ($ownergroup.Users | Where-Object { $_.Email -ne $null } | Select-Object -ExpandProperty Email) -join "; "
}
Catch [Exception] {}
}
try {
$web = Get-PnPWeb
$libraries = Get-PnPList -Includes BaseType, Hidden, Title, HasUniqueRoleAssignments, RootFolder | Where-Object {$_.Hidden -eq $False -and $_.Title -notin $excludedLists -and $_.BaseType -eq "DocumentLibrary" }
$siteListItemsCount = 0
LogEntry("Process '$($libraries.Count)' libraries")
foreach($library in $libraries) {
$siteListItemsCount += $library.ItemCount
if($library.HasUniqueRoleAssignments) {
getPermissions $library
if($global:permissionUsers.Count -gt $minUsersCount) {
$recordsCount++
LogEntry("Site $siteCounter / $($sitesCollection.Count), save record $recordsCount (library) '$($site.Url)$($library.RootFolder.ServerRelativeUrl)'")
$libraryAccessItem = New-Object PSObject
$libraryAccessItem | Add-Member -NotePropertyName "SharePointObjectType" -NotePropertyValue "Library"
$libraryAccessItem | Add-Member -NotePropertyName "UsersCount" -NotePropertyValue $global:permissionUsers.Count
$libraryAccessItem | Add-Member -NotePropertyName "ItemsCount" -NotePropertyValue $library.ItemCount
$libraryAccessItem | Add-Member -NotePropertyName "PermissionInheritedItemsCount" -NotePropertyValue $library.ItemCount
$libraryAccessItem | Add-Member -NotePropertyName "SiteUrl" -NotePropertyValue $site.Url
$libraryAccessItem | Add-Member -NotePropertyName "ListUrl" -NotePropertyValue $library.RootFolder.ServerRelativeUrl
$libraryAccessItem | Add-Member -NotePropertyName "ServerRelativeUrl" -NotePropertyValue $library.RootFolder.ServerRelativeUrl
$libraryAccessItem | Add-Member -NotePropertyName "LinkType" -NotePropertyValue ($global:permissionLevels -join '|')
$libraryAccessItem | Add-Member -NotePropertyName "SharedLinkOrDirectAccess" -NotePropertyValue "DirectAccess"
$libraryAccessItem | Add-Member -NotePropertyName "DirectPermissionType" -NotePropertyValue ($global:permissionType -join '|')
$libraryAccessItem | Add-Member -NotePropertyName "Users" -NotePropertyValue ($global:permissionUsers -join '|')
$libraryAccessItem | Add-Member -NotePropertyName "Owners" -NotePropertyValue $Owners
$libraryAccessItem | Add-Member -NotePropertyName "SiteTemplate" -NotePropertyValue $site.Template
$libraryAccessItem | Add-Member -NotePropertyName "Status" -NotePropertyValue "ProcessedOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "ErrorMessage" -NotePropertyValue ""
# Save row to CSV
$libraryAccessItem | Export-CSV $reportOutput -Append -NoTypeInformation
$skipped = $false
}
}
# Get documents from library
$listItems = Get-PnPListItem -List $library -PageSize 2000 # TODO, Check if I get more as 2000 results
LogEntry("Process '$($listItems.Count)' documents from library '$($library.Title)'")
ForEach($item in $listItems) {
# Check if this document has unique permissions
$hasUniquePermissions = Get-PnPProperty -ClientObject $Item -Property "HasUniqueRoleAssignments"
If($hasUniquePermissions) {
getPermissions $item
if($global:permissionUsers.Count -gt $minUsersCount) {
$recordsCount++
LogEntry("Site $siteCounter / $($sitesCollection.Count), save record $recordsCount (document) '$($site.Url)$($item.FieldValues["FileRef"])'")
$directAccessItem = New-Object PSObject
if ($item.FileSystemObjectType -eq "Folder") {
# Get documents and folders
$folderRelativePath = $item.FieldValues["FileRef"].Replace($web.ServerRelativeUrl,"")
$items = Get-PnPFolderItem -FolderSiteRelativeUrl $folderRelativePath -Recursive -ItemType File
$directAccessItem | Add-Member -NotePropertyName "SharePointObjectType" -NotePropertyValue "Folder"
$directAccessItem | Add-Member -NotePropertyName "UsersCount" -NotePropertyValue $global:permissionUsers.Count
$directAccessItem | Add-Member -NotePropertyName "ItemsCount" -NotePropertyValue $items.Count
# Initialiseer bestandstelling
$global:inheritedPermissionsFileCount = 0
$folder = Get-PnPFolder -Url $item.FieldValues["FileRef"] -Includes Files, Folders
# Count documents from this subfolder
countInheritedPermissionFiles $folder $library.Title
$directAccessItem | Add-Member -NotePropertyName "PermissionInheritedItemsCount" -NotePropertyValue $global:inheritedPermissionsFileCount
} else {
$directAccessItem | Add-Member -NotePropertyName "SharePointObjectType" -NotePropertyValue "File"
$directAccessItem | Add-Member -NotePropertyName "UsersCount" -NotePropertyValue $global:permissionUsers.Count
$directAccessItem | Add-Member -NotePropertyName "ItemsCount" -NotePropertyValue "N/A"
$directAccessItem | Add-Member -NotePropertyName "PermissionInheritedItemsCount" -NotePropertyValue "N/A"
}
$directAccessItem | Add-Member -NotePropertyName "SiteUrl" -NotePropertyValue $site.Url
$directAccessItem | Add-Member -NotePropertyName "ListUrl" -NotePropertyValue $library.RootFolder.ServerRelativeUrl
$directAccessItem | Add-Member -NotePropertyName "ServerRelativeUrl" -NotePropertyValue $item.FieldValues["FileRef"]
$directAccessItem | Add-Member -NotePropertyName "RoleList" -NotePropertyValue "TODO"
$directAccessItem | Add-Member -NotePropertyName "LinkScope" -NotePropertyValue "TODO"
$directAccessItem | Add-Member -NotePropertyName "LinkType" -NotePropertyValue ($global:permissionLevels -join '|')
$SharingLinks = if ($item.FileSystemObjectType -eq "File") {
Get-PnPFileSharingLink -Identity $item.FieldValues["FileRef"]
} elseif ($item.FileSystemObjectType -eq "Folder") {
Get-PnPFolderSharingLink -Folder $item.FieldValues["FileRef"]
}
if($SharingLinks) {
$directAccessItem | Add-Member -NotePropertyName "SharedLinkOrDirectAccess" -NotePropertyValue "DirectAccess/SharedLinks"
} else {
$directAccessItem | Add-Member -NotePropertyName "SharedLinkOrDirectAccess" -NotePropertyValue "DirectAccess"
}
$directAccessItem | Add-Member -NotePropertyName "DirectPermissionType" -NotePropertyValue ($global:permissionType -join '|')
$directAccessItem | Add-Member -NotePropertyName "Users" -NotePropertyValue ($global:permissionUsers -join '|')
$directAccessItem | Add-Member -NotePropertyName "Owners" -NotePropertyValue $Owners
$directAccessItem | Add-Member -NotePropertyName "SiteTemplate" -NotePropertyValue $site.Template
$directAccessItem | Add-Member -NotePropertyName "Status" -NotePropertyValue "ProcessedOversharing"
$directAccessItem | Add-Member -NotePropertyName "ErrorMessage" -NotePropertyValue ""
# Save row to CSV
$directAccessItem | Export-CSV $reportOutput -Append -NoTypeInformation
$skipped = $false
}
}
}
}
getPermissions $web
if($global:permissionUsers.Count -gt $minUsersCount) {
$recordsCount++
LogEntry("Site $siteCounter / $($sitesCollection.Count), save record $recordsCount (site) '$($site.Url)'")
$siteAccessItem = New-Object PSObject
$siteAccessItem | Add-Member -NotePropertyName "SharePointObjectType" -NotePropertyValue "Site"
$siteAccessItem | Add-Member -NotePropertyName "UsersCount" -NotePropertyValue $global:permissionUsers.Count
$siteAccessItem | Add-Member -NotePropertyName "ItemsCount" -NotePropertyValue $siteListItemsCount
$siteAccessItem | Add-Member -NotePropertyName "PermissionInheritedItemsCount" -NotePropertyValue $siteListItemsCount
$siteAccessItem | Add-Member -NotePropertyName "SiteUrl" -NotePropertyValue $site.Url
$siteAccessItem | Add-Member -NotePropertyName "ListUrl" -NotePropertyValue "N/A"
$siteAccessItem | Add-Member -NotePropertyName "ServerRelativeUrl" -NotePropertyValue $web.ServerRelativeUrl
$siteAccessItem | Add-Member -NotePropertyName "LinkType" -NotePropertyValue ($global:permissionLevels -join '|')
$siteAccessItem | Add-Member -NotePropertyName "SharedLinkOrDirectAccess" -NotePropertyValue "DirectAccess"
$siteAccessItem | Add-Member -NotePropertyName "DirectPermissionType" -NotePropertyValue ($global:permissionType -join '|')
$siteAccessItem | Add-Member -NotePropertyName "Users" -NotePropertyValue ($global:permissionUsers -join '|')
$siteAccessItem | Add-Member -NotePropertyName "Owners" -NotePropertyValue $Owners
$siteAccessItem | Add-Member -NotePropertyName "SiteTemplate" -NotePropertyValue $site.Template
$siteAccessItem | Add-Member -NotePropertyName "Status" -NotePropertyValue "ProcessedOversharing"
$siteAccessItem | Add-Member -NotePropertyName "ErrorMessage" -NotePropertyValue ""
# Save row to CSV
$siteAccessItem | Export-CSV $reportOutput -Append -NoTypeInformation
$skipped = $false
}
}
catch [Exception] {
LogEntry("Catch error: " + $_.Exception.Message)
}
if($skipped) {
$libraryAccessItem = New-Object PSObject
$libraryAccessItem | Add-Member -NotePropertyName "SharePointObjectType" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "UsersCount" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "ItemsCount" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "PermissionInheritedItemsCount" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "SiteUrl" -NotePropertyValue $site.Url
$libraryAccessItem | Add-Member -NotePropertyName "ListUrl" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "ServerRelativeUrl" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "LinkType" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "SharedLinkOrDirectAccess" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "DirectPermissionType" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "Users" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "Owners" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "SiteTemplate" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "Status" -NotePropertyValue "ProcessedNoOversharing"
$libraryAccessItem | Add-Member -NotePropertyName "ErrorMessage" -NotePropertyValue ""
# Save row to CSV
$libraryAccessItem | Export-CSV $reportOutput -Append -NoTypeInformation
}
} else {
$libraryAccessItem = New-Object PSObject
$libraryAccessItem | Add-Member -NotePropertyName "SharePointObjectType" -NotePropertyValue "Site"
$libraryAccessItem | Add-Member -NotePropertyName "UsersCount" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "ItemsCount" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "PermissionInheritedItemsCount" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "SiteUrl" -NotePropertyValue $site.Url
$libraryAccessItem | Add-Member -NotePropertyName "ListUrl" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "ServerRelativeUrl" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "LinkType" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "SharedLinkOrDirectAccess" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "DirectPermissionType" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "Users" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "Owners" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "SiteTemplate" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "Status" -NotePropertyValue "ProcessedExcludedSite"
$libraryAccessItem | Add-Member -NotePropertyName "ErrorMessage" -NotePropertyValue ""
# save row in CSV
$libraryAccessItem | Export-CSV $reportOutput -Append -NoTypeInformation
}
} else {
break
}
LogEntry("Sharing Links Report Generated Successfully $reportOutput")
LogEntry("FINISHED")