SharePoint Large List Notifier

Posted on Posted in SharePoint

As any SharePoint Administrator might attest, good governance is always a challenge.

One issue that always seems to creep up on you is the size of your lists. Now we know in 2010 the list threshold was lifted to about 5000 items before you start seeing the fabled error;

[text]
This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.

To view items, try selecting another view or creating a new view. If you do not have sufficient permissions to create views for this list, ask your administrator to modify the view so that it conforms to the list view threshold.
[/text]

You can obviously increase the list threshold limit if you wish, which isn’t necessarily a great idea. You should really investigate why this is happening, in most cases applying and index to the list column would do the trick.

To help in governing large lists, here’s a simple script can iterate all your lists and then send an email for any lists that have gone over the threshold. This way you never skip a beat and you’re always on top of it.

The Script
The script will go through your lists and store any offending lists with their Name, Url and Items in a Data Table. Certain lists will always be massive so there is an exclusion array you can use so you dont get nagged by these serial offenders. Finally the script will email you if it finds any items of your desired threshold.


#
# SharePoint Large List Notifier
#

# Add PowerShell Snapin
Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

# Host site
$site = Get-SPSite http://portal.domain.local

# List Limit
$limit = 5000
# Exlude
$exclude = @("TaxonomyHiddenList",
"User Information List",
"Accepted List 01",
"Accepted List 02"
)

# Create Data Table
$table = New-Object System.Data.DataTable "Large Lists"
# Create Columns
$col1 = New-Object system.Data.DataColumn Title,([string])
$col2 = New-Object system.Data.DataColumn Url,([string])
$col3 = New-Object system.Data.DataColumn Items,([int])
# Add Columns
#Add the Columns
$table.Columns.Add($col1)
$table.Columns.Add($col2)
$table.Columns.Add($col3)

foreach ($web in $site.AllWebs ) {
foreach ($list in $web.Lists ) {
if ($list.ItemCount -gt $limit) {
# Uncomment if you want to see the results on the console
# Write-Host "Title:" -ForegroundColor Black -BackgroundColor Cyan -NoNewline
# Write-Host " " -NoNewline
# Write-Host $list.Title -ForegroundColor White
# Write-Host " URL: " -ForegroundColor DarkCyan -NoNewline
# Write-Host $list.DefaultViewUrl -ForegroundColor Gray
# Write-Host "Items: " -ForegroundColor DarkCyan -NoNewline
# Write-Host $list.ItemCount -ForegroundColor Gray

# Exclude sites that can have more items
if ($exclude -contains $list.Title) {
# Ignore
}
else {
# Add into table
$row = $table.NewRow()
$row.Title = $list.Title
$row.Url = $list.DefaultViewUrl
$row.Items = $list.ItemCount
$table.Rows.Add($row)
}

}

else {
# Ignore
}

}
}

if ($table.Rows.Count -eq 0) {
# Do nothing
}
else {
$tableSites = $table | Format-Table -AutoSize | Out-String
# Send email containing large lists
# SMTP Server
$smtpServer = "smtp.domain.local"
# Net Mail Object
$msg = New-Object Net.Mail.MailMessage
# SMTP Server Object
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
# Email
$msg.From = "Large.Lists@domain.local"
$msg.ReplyTo = "Large.Lists@domain.local"
$msg.To.Add("Recipient.One@domain.local")
$msg.To.Add("Recipient.Two@domain.local")
$msg.CC.Add("Recipient.Three@domain.local")
$msg.Subject = "[WARNING] The following list/s are above $limit items."
$msg.Body =
"Greetings,

The following lists have more items then the recommended limit;

$tableSites
Regards,`n

--
Large List Notifier
"
# Send Message
$smtp.Send($msg)
}

# Clear and Dispose Table
#$table
$table.Clear()
$table.Dispose()

Hopefully you’ll be in a better place now to manage those pesky large lists we always strive to maintain.

Enjoy,

Kristof

Leave a Reply

Your email address will not be published. Required fields are marked *