SharePoint Online: Find All Large Lists and Generate Report using PowerShell
SharePoint Online uses the hard limit of 5000 items as its list view threshold. So, we need to keep an eye on large lists in our SharePoint Online environment. This may be a nearly impossible task if you do it manually when you have a lot of sites in your tenant. In this blog post, we will show you how to find all the large lists in your environment with PowerShell quickly and easily.
PowerShell Script to find all large lists in SharePoint Online:
Here is the PowerShell script to scan and get all lists exceeding the 5000 items limit on the given SharePoint Online site collection.
#Load SharePoint CSOM Assemblies
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"
#Config Parameters
$SiteURL="https://crescent.sharepoint.com"
$ReportOutput="C:\temp\LargeLists.csv"
$Threshold=5000
#Exclude system lists
$ExcludedLists = @("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"
,"Master Docs","Master Page Gallery","MicroFeed","NintexFormXml","Quick Deploy Items","Relationships List","Reusable Content","Search Config List", "Solution Gallery",
"Suggested Content Browser Locations","TaxonomyHiddenList","User Information List","Web Part Gallery","wfpub","wfsvc","Workflow History","Workflow Tasks")
#Delete the Output Report, if exists
if (Test-Path $ReportOutput) { Remove-Item $ReportOutput }
#Setup Credentials
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
Function Get-SPOLargeLists($SiteURL)
{
Write-host "Processing Web:"$SiteURL
Try {
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Credentials
#Get the web from given URL and its subsites
$Web = $Ctx.web
$Ctx.Load($Web)
$Ctx.Load($web.Webs)
$Ctx.executeQuery()
#Get all lists from the web
$Lists = $Ctx.Web.Lists
$Ctx.Load($Lists)
$Ctx.ExecuteQuery()
$LargeListsResult = @()
#Loop through each list and get items count
Foreach($List in $Lists)
{
if($ExcludedLists -NotContains $List.Title) #Exclude certain Lists
{
if($List.ItemCount -gt $Threshold)
{
#Get the Result to a psobject
$Result = New-Object PSObject
$Result | Add-Member NoteProperty Title($List.Title)
$Result | Add-Member NoteProperty URL($web.URL)
$Result | Add-Member NoteProperty Count($List.ItemCount)
#Add the result to an Array
$LargeListsResult += $Result
Write-host "Found List '$($List.Title)' with $($List.ItemCount) Items" -f Yellow
}
}
}
#Export the result Array to CSV file
$LargeListsResult | Export-CSV $ReportOutput -NoTypeInformation -Append
#Iterate through each subsite of the current web and call the function recursively
foreach ($Subweb in $web.Webs)
{
#Call the function recursively to process all subsites underneaththe current web
Get-SPOLargeLists($Subweb.url)
}
}
Catch {
write-host -f Red "Error Finding Large Lists!" $_.Exception.Message
}
}
#Call the function with Root site URL
Get-SPOLargeLists -SiteURL $SiteURL
How do you find large lists in all site collections in the tenant?
PnP PowerShell to Find Larger Lists in All Site Collections
Let’s audit all site collections in the tenant for large lists and generate a report using PnP PowerShell.
#Parameters
$TenantAdminURL = "https://crescent-admin.sharepoint.com"
$CSVFile = "C:\Temp\LargeLists.csv"
$Threshold = 5000
$LargeListsInventory = @()
#Function to Audit Larger lists
Function Find-PnPLargeLists($Web)
{
Write-host "Auditing Site:"$Web.Url
#Get All Lists from the Web
$Lists= Get-PnPProperty -ClientObject $Web -Property Lists
$Lists | ForEach-Object {
If($_.ItemCount -gt $Threshold)
{
Write-host -f Yellow "`tFound a Larger list '$($_.Title)' with Items:"$_.ItemCount
$LargeListsInventory += New-Object -TypeName PSObject -Property @{
SiteURL = $Web.Url
ListName = $_.Title
ItemCount = $_.ItemCount
LastModified = $_.LastItemUserModifiedDate
}
}
}
#Export Data to CSV File
$LargeListsInventory | Export-Csv $CSVFile -NoTypeInformation -Append
}
$Cred = Get-credential
#Connect to Tenant Admin Site
Connect-PnPOnline -url $TenantAdminURL -credential $Cred
#Delete the CSVFile if exists
If (Test-Path $CSVFile) { Remove-Item $CSVFile }
#Get All Site collections and Iterate through
$SiteCollections = Get-PnPTenantSite
ForEach($Site in $SiteCollections)
{
#Connect to Site Collection
Connect-PnPOnline -url $Site.URL -credential $Cred
#Get webs of the Site Collection
$Webs = Get-PnPSubWeb -Recurse -IncludeRootWeb
#Get All Webs of the site collection and call the function to find large lists
$Webs | ForEach-Object {Find-PnPLargeLists -Web $_}
}
The output of the script:
Here is another post for on-premises: Find Large Lists in SharePoint using PowerShell
Export to the CSV File is not working. Can you assist on getting the export please.
How do I get all large lists for all site collections?
Post has been updated to get large lists from all sites in the tenant.