Skip Ribbon Commands
Skip to main content

Quick Launch

Todd Klindt's home page > Todd Klindt's Office 365 Admin Blog > Posts > Tackling SharePoint's 5000 Item Limit with PowerShell and Search
July 10
Tackling SharePoint's 5000 Item Limit with PowerShell and Search

In the world of SharePoint, the 5000 item per view limit is a well-known challenge, the stuff of legend. To recap, while a SharePoint list or library can have up to 30 million items in it, SharePoint refuses to show you more than 5000 of them at a time. That’s an API level control to protect the backend, so it won’t do this in a view in a web page, an API call through PowerShell or CLI, nothing. While there are several ways to navigate around this limit, such as using CAML queries, these methods often fall short when dealing with really large datasets. This blog post will explore a unique solution to this problem using PowerShell, specifically focusing on the use of Search to retrieve data.

The Challenge

Recently, I was working with a client, a law firm, who had a whopping 7.8 million items in a SharePoint document library. Of course not a great Information Architecture. We were helping them fix that. Among other horrors, over the years, attorneys had copied the contents of CDs and DVDs to various places in SharePoint, creating a massive and complex data structure. The challenge was to find all these so we could migrate them out or delete them. However, due to the sheer volume of data, there was no way to slice the data using the normal tools to get back fewer than 5000 results. This is where PowerShell swoops in and saves the day.

The PowerShell Solution

The solution came in two parts. We were looking for the DVDs by looking for the autorun.inf file in the root. I discovered I couldn’t using something like Get-PnPListItem to find all of the autorun.inf files because I couldn’t find a way with CAML, or anything else to pare the result set down below 5000. There were just too many files. However, I did discover that I could find them in the Search Center, which gave me the first idea. Get them from Search in PowerShell. I used the Submit-PnPSearchQuery cmdlet to send a search query to SharePoint and it retrieves all the results. It gets them in batches of 500 (the maximum for a single search request), with the option of making multiple requests if necessary to retrieve all results. This worked pretty well, but was tedious because I could only get 500 at a time and there were thousands. I had to modify the search, run it again, and append those results to the results from the previous searches. That was too much work, and led to the second part. I wrote a PowerShell function called Submit-PnPSearchQueryAll. This function uses the Submit-PnPSearchQuery cmdlet to send a search query to SharePoint and retrieves all results, paging through them and running multiple queries as needed. If the -ShowProgress switch is provided, the function will display the total number of results and a progress bar.

Before we look at the function itself, let’s see how it works. My usage looked like this:
$AllResults = Submit-PnPSearchQueryAll -query "autorun.inf"

That stored every file named autorun.inf in the variable $AllResults. There are 5046 of them:
$AllResults.Count

image 

Since that’s a collection of objects I can treat them like any old object:
$AllResults[1000]

image 

and

$AllResults | Where-Object {$_.ParentLink -like "*AttorneyFiles/Johnson, Michael*" }

image 

or

$AllResults | Where-Object {$_.ParentLink -like "*AttorneyFiles/Johnson, Michael*" } | select Path,ParentLink

image 

and

$AllResults | Where-Object {$_.ParentLink -like "*AttorneyFiles/Johnson, Michael*" } | Export-Csv .\mj.csv

See all the fun you can have? Since the object we’re getting back is a PnPResultTable object, it doesn’t have all the same properties as a PnPListItem. When I wrote the function I had to decide which ones I needed. If you use this, you might need something different.

How It Works

The function begins by initializing variables for the starting row and page size, set to 500. It then enters a loop where it performs the search query with Submit-PnPSearchQuery and retrieves the results. If the –ShowProgress switch is provided, it will display the total number of results on the first run and a progress bar for each subsequent run.

For each result, the function outputs a custom object with the desired properties. It then increments the $startRow by the $pageSize and continues the loop while the $startRow is less than the Total Rows.

Before I got this working I tried a couple of other approaches, but this one worked the best.

Grab the PSM1 file with Submit-PnPSearchQueryAll here.

Conclusion

This PowerShell function proved to be an effective solution to the SharePoint 5000 item limit, allowing us to retrieve all items from a massive SharePoint document library. It demonstrates the power and flexibility of PowerShell and SharePoint's Search functionality when dealing with large datasets. Whether you're dealing with millions of items or just want a more efficient way to retrieve data from SharePoint, consider giving this function a try.

tk

ShortURL: https://www.toddklindt.com/Posh5000ItemLimit

Comments

 Awesome!

Thanks for sharing this amazing analysis and solution! 🙏
 on 7/11/2023 4:18 AM

Creative solution!

I haven’t dealt with this issue for a customer in almost ten years and yet I still enjoyed reading about the scenario and your solution. Thanks for sharing the script!
 on 7/15/2023 9:59 AM

먹튀검증

The world hopes for even more passionate writers like you who are not afraid to say how they believe. <a href="https://meoktwi.com/" target="_blank">먹튀검증</a>
 on 8/22/2023 12:38 AM

토토사이트

I saw a lot of website but I conceive this one has something extra in it.
https://therowdylender.com/
https://totogorae.com/
https://mymelee.com/
 on 8/22/2023 12:39 AM

Angel17

Thanks for sharing this wonderful blog. Keep sharing. www.repairmyappliance.ca/calgary/
 on 8/26/2023 2:34 PM

صيانة شارب

<a href="https://sharpfixe.com"> صيانة شارب </a>
<a href="https://toshibae.com"> صيانة توشيبا </a>
<a href="https://lg-maintenanc.com/"> صيانة ال جي</a>
 on 8/28/2023 4:42 AM

صيانة شارب

<a href="https://sharpfixe.com"> صيانة شارب </a>
<a href="https://toshibae.com"> صيانة توشيبا </a>
<a href="https://lg-maintenanc.com/"> صيانة ال جي</a>
 on 8/28/2023 5:52 AM

صيانة اجهزة

<a href="https://seyanaegy.com">دليل رقم صيانة اجهزة بمصر</a>
<a href="https://washersfix.com">رقم صيانة ثلاجات و غسالات</a>
<a href="https://siantk.com‏">رقم صيانة اجهزة بمصر</a>
<a href="https://twekel.com"> رقم صيانة معتمد للاجهزة </a>
<a href="https://alsiyanuh.com">رقم صيانة معتمد للاجهزة </a>
<a href="https://amaintenanc.com">رقم صيانة </a>
<a href="https://twkel.com"> رقم صيانة اجهزة </a>
<a href="https://sharpfixe.com">رقم صيانة شارب غسالات </a>
<a href="https://toshibae.com">رقم صيانة توشيبا غسالات </a>
<a href="https://lg-maintenanc.com/">رقم صيانة ال جي غسالات </a>



<a href="https://bosch.washersfix.com/ ">رقم صيانة بوش غسالات </a>
<a href="https://hoover.twkel.com/">رقم صيانة هوفر غسالات </a>
<a href="https://westinghouse.twkel.com ">رقم صيانة  وستنجهاوس غسالات </a>
<a href="https://frigidaire.twkel.com/">رقم صيانة فريجيدير غسالات </a>
    <a href="https://whirlpool.washersfix.com/">رقم صيانة ويرلبول غسالات </a>
<a href="https://kelvinator.twkel.com">رقم صيانة كلفينيتور غسالات </a>
<a href="https://indesit.washersfix.com ">رقم صيانة اندست غسالات </a>
 <a href="https://smeg.washersfix.com/">رقم صيانة سميج غسالات </a>
<a href="https://hitachi.twekel.com/">رقم صيانة هيتاشي غسالات </a>
 <a href="https://ariston.washersfix.com/  ">رقم صيانة اريستون غسالات </a>
 <a href="https://tornado.twekel.com/ ">رقم صيانة  تورنيدو غسالات </a>
 <a href="https://fresh.twekel.com/ ">رقم صيانة فريش غسالات </a>
<a href="https://whitepoint.twekel.com/">رقم صيانة وايت بوينت غسالات </a>
<a href="https://toshiba.twkel.com">رقم صيانة توشيبا غسالات </a>
<a href="https://toshiba.washersfix.com">رقم صيانة توشيبا غسالات </a>
<a href="https://daewoo.siantk.com">رقم صيانة دايو غسالات </a>
<a href="https://samsung.twkel.com">رقم صيانة سامسونج غسالات </a>
<a href="https://kiriazi.siantk.com">رقم صيانة كريازي غسالات </a>
<a href="https://kiriazi.twkel.com">رقم صيانة كريازي غسالات </a>
<a href="https://sharp.siantk.com">رقم صيانة شارب غسالات </a>
<a href="https://whitewhale.siantk.com">رقم صيانة وايت ويل غسالات </a>
<a href="https://whitewhale.washersfix.com">رقم صيانة وايت ويل غسالات </a>
<a href="https://beko.alsiyanuh.com">رقم صيانة بيكو غسالات </a>
<a href="https://lg.twkel.com">رقم صيانة ال جي غسالات </a>
<a href="https://universal.alsiyanuh.com">رقم صيانة يونيفرسال غسالات غسالات </a>
<a href="https://zanussi.twkel.com">رقم صيانة زانوسي غسالات </a>
<a href="https://unionaire.twkel.com">رقم صيانة يونيون اير </a>
 on 8/31/2023 8:35 AM

صيانة اجهزة

<a href="https://seyanaegy.com">دليل رقم صيانة اجهزة بمصر</a>
<a href="https://washersfix.com">رقم صيانة ثلاجات و غسالات</a>
<a href="https://siantk.com‏">رقم صيانة اجهزة بمصر</a>
<a href="https://twekel.com"> رقم صيانة معتمد للاجهزة </a>
<a href="https://alsiyanuh.com">رقم صيانة معتمد للاجهزة </a>
<a href="https://amaintenanc.com">رقم صيانة </a>
<a href="https://twkel.com"> رقم صيانة اجهزة </a>
<a href="https://sharpfixe.com">رقم صيانة شارب غسالات </a>
<a href="https://toshibae.com">رقم صيانة توشيبا غسالات </a>
<a href="https://lg-maintenanc.com/">رقم صيانة ال جي غسالات </a>



<a href="https://bosch.washersfix.com/ ">رقم صيانة بوش غسالات </a>
<a href="https://hoover.twkel.com/">رقم صيانة هوفر غسالات </a>
<a href="https://westinghouse.twkel.com ">رقم صيانة  وستنجهاوس غسالات </a>
<a href="https://frigidaire.twkel.com/">رقم صيانة فريجيدير غسالات </a>
<a href="https://whirlpool.washersfix.com/">رقم صيانة ويرلبول غسالات </a>
<a href="https://kelvinator.twkel.com">رقم صيانة كلفينيتور غسالات </a>
<a href="https://indesit.washersfix.com ">رقم صيانة اندست غسالات </a>
<a href="https://smeg.washersfix.com/">رقم صيانة سميج غسالات </a>
<a href="https://hitachi.twekel.com/">رقم صيانة هيتاشي غسالات </a>
<a href="https://ariston.washersfix.com/  ">رقم صيانة اريستون غسالات </a>
<a href="https://tornado.twekel.com/ ">رقم صيانة  تورنيدو غسالات </a>
<a href="https://fresh.twekel.com/ ">رقم صيانة فريش غسالات </a>
<a href="https://whitepoint.twekel.com/">رقم صيانة وايت بوينت غسالات </a>
<a href="https://toshiba.twkel.com">رقم صيانة توشيبا غسالات </a>
<a href="https://toshiba.washersfix.com">رقم صيانة توشيبا غسالات </a>
<a href="https://daewoo.siantk.com">رقم صيانة دايو غسالات </a>
<a href="https://samsung.twkel.com">رقم صيانة سامسونج غسالات </a>
<a href="https://kiriazi.siantk.com">رقم صيانة كريازي غسالات </a>
<a href="https://kiriazi.twkel.com">رقم صيانة كريازي غسالات </a>
<a href="https://sharp.siantk.com">رقم صيانة شارب غسالات </a>
<a href="https://whitewhale.siantk.com">رقم صيانة وايت ويل غسالات </a>
<a href="https://whitewhale.washersfix.com">رقم صيانة وايت ويل غسالات </a>
<a href="https://beko.alsiyanuh.com">رقم صيانة بيكو غسالات </a>
<a href="https://lg.twkel.com">رقم صيانة ال جي غسالات </a>
<a href="https://universal.alsiyanuh.com">رقم صيانة يونيفرسال غسالات غسالات </a>
<a href="https://zanussi.twkel.com">رقم صيانة زانوسي غسالات </a>
<a href="https://unionaire.twkel.com">رقم صيانة يونيون اير </a>
 on 9/2/2023 10:18 AM

rennna111

 on 9/3/2023 4:30 AM
1 - 10Next

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.

Title


Body *


Today's date *

Select a date from the calendar.
Please enter today's date so I know you are a real person

Twitter


Want a message when I reply to your comment? Put your Twitter handle here.

Attachments

 

 SysKit