Script To Get Lists Exceeding The List View Threshold Of 5000 Items

Vinod Kumar
7y
4.2k
0
1

Blog

Well, we all know SharePoint imposes limits on the list view threshold. If any list exceeds the limit, it can be a cause for performance degradation due to long running queries at the back-end [SQL].

Therefore, it’s fair to get the report, which gives us information about the lists, which are exceeding the limit of 5000 items.

The script given below provides the report.

Clear-Host
Write-Host -ForegroundColor Cyan "============================================================="
Write-Host -ForegroundColor Magenta " SharePoint List Threshold Script "
Write-Host -ForegroundColor Cyan "============================================================="
Function PromptForScopeSelections
{
$promptTitle = "Scope selection"
$promptMessage = "Please select the option to set scope for generating threshold report"
$optionFarm = New-Object System.Management.Automation.Host.ChoiceDescription "&Farm", `
"Generate list view threshold report for entire farm"
$optionFarm.HelpMessage = "This option will generate list threshold report for entire farm"
$optionWebApp = New-Object System.Management.Automation.Host.ChoiceDescription "&Web Application", `
"Generate list view threshold report for single web application"
$optionWebApp.HelpMessage = "This option will generate list threshold report for single web application"
$optionSiteCollection = New-Object System.Management.Automation.Host.ChoiceDescription "&Site Collection", `
"Generate list view threshold report for single site collection"
$optionSiteCollection.HelpMessage = "This option will generate list threshold report for single site collection"
$promptOptions = [System.Management.Automation.Host.ChoiceDescription[]]($optionFarm, $optionWebApp, $optionSiteCollection)
$promptResult = $host.ui.PromptForChoice($promptTitle, $promptMessage, $promptOptions, 0)
Return $promptResult
}
#Load the SharePoint snap-in in PowerShell if it is not loaded already
Write-Host -ForegroundColor Yellow "Please wait while SharePoint snap-in is being loaded..."
Add-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue
if((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -ne $null)
{
Write-Host -ForegroundColor Green "SharePoint snap-in has been loaded successfully..."
#Call the function to prompt for scope selection.
$UserOption = 3
$UserOptionAccepted = $false
While($UserOptionAccepted -ne $true)
{
$UserOption = PromptForScopeSelections
if($UserOption -ge 0 -and $UserOption -le 3)
{
Write-Host -ForegroundColor Green "Input has been accepted."
switch ($UserOption)
{
0 {Write-Host -ForegroundColor DarkCyan "You selected farm."}
1 {Write-Host -ForegroundColor DarkCyan "You selected web application."}
2 {Write-Host -ForegroundColor DarkCyan "You selected site collection."}
}
$UserOptionAccepted = $true
}
elseif($UserOption -lt 0 -or $UserOption -ge 3 -or $UserOption -eq "" -or $UserOption -eq $null)
{
Write-Host -ForegroundColor Red "Invalid input..."
}
}
if($UserOptionAccepted -eq $true)
{
$ScriptPath = Split-Path $MyInvocation.MyCommand.Path
$ScriptDate = Get-Date -Format "dd-MMM-yyyy"
$ScriptTime = Get-Date -Format "hh-mm-ss"
if($UserOption -eq 0)
{
$ThresholdReportPath = $ScriptPath + "\" + "SharePointFarm_LVT_" + $ScriptDate + "_" + $ScriptTime + ".csv"
}
elseif($UserOption -eq 1)
{
$WebAppAccepted = $false
while($WebAppAccepted -ne $true)
{
$WebAppURL = Read-Host -Prompt "Please Enter Web Application URL"
if(($WebApplication = Get-SPWebApplication $WebAppURL -ErrorAction SilentlyContinue) -ne $null)
{
Write-Host -ForegroundColor Green "Web Application URL has been accepted..."
$WebAppTitle = $WebApplication.Name.ToString().Replace(" ","")
$WebAppAccepted = $true
}
else
{
Write-Host -ForegroundColor Red "Invalid input, please provide valid Web Application URL"
}
}
$ThresholdReportPath = $ScriptPath + "\" + "WebApplication_" + $WebAppTitle + "_LVT_" + $ScriptDate + "_" + $ScriptTime + ".csv"
}
elseif($UserOption -eq 2)
{
$SiteAccepted = $false
while($SiteAccepted -ne $true)
{
$SiteCollURL = Read-Host -Prompt "Please Enter Site Collection URL"
if(($SiteCollection = Get-SPSite $SiteCollURL -ErrorAction SilentlyContinue) -ne $null)
{
Write-Host -ForegroundColor Green "Site Collection URL has been accepted..."
$SiteTitle = $SiteCollection.RootWeb.Name.ToString().Replace(" ","")
$SiteAccepted = $true
}
else
{
Write-Host -ForegroundColor Red "Invalid input, please provide valid Site Collection URL"
}
}
$ThresholdReportPath = $ScriptPath + "\" + "SiteCollection_" + $SiteTitle + "_LVT_" + $ScriptDate + "_" + $ScriptTime + ".csv"
}
#Create Output File Headers
"Site URL`tList Title`tItem Count" | Out-File $ThresholdReportPath
$ListCounter = 0
if($UserOption -eq 0)
{
$WebApps = Get-SPWebApplication
ForEach($WebApp in $WebApps)
{
$Sites = Get-SPSite -Limit All -WebApplication $WebApp.URL
ForEach($Site in $Sites)
{
ForEach($Web in $Site.AllWebs)
{
ForEach($List in $Web.Lists)
{
$Items = 0
$Items = $List.ItemCount
if($Items -ge 5000)
{
$ListCounter++
Write-Host -BackgroundColor DarkCyan "No. of lists having more than 5K items: $ListCounter `r" -NoNewline
$Web.URL + "`t" + $List.Title + "`t" + $Items | Out-File $ThresholdReportPath -Append
}
}
}
}
}
}
elseif($UserOption -eq 1)
{
$Sites = Get-SPSite -Limit All -WebApplication $WebApplication.URL
ForEach($Site in $Sites)
{
ForEach($Web in $Site.AllWebs)
{
ForEach($List in $Web.Lists)
{
$Items = $List.ItemCount
if($Items -ge 5000)
{
$ListCounter++
Write-Host -BackgroundColor DarkCyan "No. of lists having more than 5K items: $ListCounter `r" -NoNewline
$Web.URL + "`t" + $List.Title + "`t" + $Items.TOString() | Out-File $ThresholdReportPath -Append
}
}
}
}
}
elseif($UserOption -eq 2)
{
ForEach($Web in $SiteCollection.AllWebs)
{
ForEach($List in $Web.Lists)
{
$Items = $List.ItemCount
if($Items -ge 5000)
{
$ListCounter++
Write-Host -BackgroundColor DarkCyan "No. of lists having more than 5K items: $ListCounter `r" -NoNewline
$Web.URL + "`t" + $List.Title + "`t" + $Items | Out-File $ThresholdReportPath -Append
}
}
}
}
Write-Host " "
Write-Host -ForegroundColor Cyan "Script Execution Completed..."
Write-Host -ForegroundColor DarkMagenta "Output File Path is: " -NoNewline
Write-Host -ForegroundColor Gray $ThresholdReportPath
}
else
{
Write-Host -ForegroundColor Red "Scope selection was not valid, terminating the exectuion..."
Return
}
}
else
{
Write-Host -ForegroundColor Red "SharePoint snap-in could not be loaded, terminating the execution..."
}

Inputs

Scope Selection: It provides you three options, as mentioned below. If you want a report for the entire Farm, press “F”. Similarly “W” or “S” for Web Application and Site Collection respectively.
- Farm
- Web Application
- Site Collection
If you select Web Application, then the next input you should provide is Web Application URL.
If you select Site Collection, then the next input you should provide is Site Collection URL.

Once the required inputs are provided, it will go through each list in the sites and check, if it exceeds the limit of 5000 items. If yes, it will add that list to the report.

After the execution is completed, it will provide you the location, where the report is generated.

You can have a look at the report and identify the large lists. Later, remediate the lists, as per Microsoft’s recommended methods to ensure the list. The views don’t retrieve more than 5000 items at a time.

Hence, you can contribute to smooth performance for SharePoint Farm.

I hope this helps. Thanks for reading and using the script.