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

managed property gives files size for all versions?

Dear Todd, I´m aware that using submit-pnpsearchquery we can retrieve info from managed properties (size, title, storage...) but I´m missing a property that retrieves file size of ALL versions in SharePoint Online. does it exists? I cannot find it. My email: pepetriay@gmail.com

Thanks in advance and best regards.
 on 7/30/2024 4:31 AM

Great post

Made from premium fabrics the <a href="https://www.thecaliforniaoutfits.com/product/palace-saints-varsity-jacket/">palace saints varsity jacket</a> is stylish and long-lasting This jacket's high-quality wool and leather materials guarantee that it will survive as long as it looks great.
 on 9/6/2024 8:42 AM

sep

This site called topessaywriting.org review at medium is the reason for my relief. Literally with right on time I am able to get everything on my hands. topessaywriting.org review at medium https://medium.com/@julie.swann.m/topessaywriting-org-review-with-summary-%EF%B8%8Ffor-fast-decision-699e168bb564
 on 9/11/2024 10:42 AM

sep

What i don’t realize is in fact how you’re no longer actually much more smartly-liked than you may be right now. You are very intelligent. You understand thus significantly on the subject of this matter, produced me for my part imagine it from so many various angles. Its like men and women are not involved unless it is something to do with Lady gaga! Your personal stuffs nice. Always maintain it up! Instrument shops for musicians https://thebandbiz.com/
 on 9/23/2024 5:26 AM

sep

Youre so cool! I dont suppose Ive learn something like this before. So good to find somebody with some unique ideas on this subject. realy thank you for beginning this up. this website is something that is needed on the net, somebody with somewhat originality. helpful job for bringing something new to the internet!  front door refinishing near me https://www.doorrefinishingarizona.com/
 on 10/3/2024 11:11 AM

sep

I enjoy brain-stimulating content like this. I have no issue with any of the information here. I agree with a good deal of the points mentioned in this excellent article. 온라인 포커 플레이 https://holdempalace.com
 on 10/16/2024 12:27 PM

sep

I am constantly browsing online for ideas that can benefit me. Thanks! 3D Models UAE https://www.g3d-models.com/
 on 10/18/2024 5:19 AM

sep

You can find dissertation online sites from the web make your home pickup unsurprisingly referred to in your ınternet sites. roof repair Lewiston https://www.arcticroof.co/
 on 10/19/2024 5: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