I had an interesting question being asked by one of my clients the other day. They were running into storage issues and needed to identify all the files with large file size in their tenant. I blogged about a similar topic a while ago; however, back then, the scenario and solution were a bit different. In that other use case, I explained how to identify SharePoint sites with the most storage taken and how to drill down and find the worst offenders on that particular site. You can read more about this solution here. In today’s use case, the requirement is entirely different. The requirement is to come up with a list of files exceeding a certain file size from all sites. So in this article, I will explain how to find and identify such files.
Method to find files of certain file size in SharePoint Online
If you google the above topic, you will find many options that utilize 3rd party tools and PowerShell scripts. However, you know me. I am a simple guy, and my IQ is not high enough to get familiar with the options above 😊 Besides, I am strictly an Out of the Box kind of guy, so I must use the solution that is simple and OOTB.
So the solution I came up with for this client was to use the built-in Search.
That said, although Search in SharePoint is pretty powerful, it does not allow filtering or sorting by file size.
How to search for files of certain file size in SharePoint Online
There are two main conditions for you to follow to utilize the Size managed property:
- Since the Size managed property is an Integer, when you execute the search using the Size managed property, you need to use math operators like “<” or “>” to find files of specific file size
- The number you put next to math operators is in bytes. So if you are looking for files larger than 1 MB, the number you would need to set is 1000000 (1,000,000 bytes = 1 MB)
size > 10000000 finds all the files larger than 10 MB
size > 1000000 AND filetype:pdf finds all the PDFs that are large than 1 MB
- Since the above method relies on generic SharePoint Search, whoever is executing the search commands must have access to the sites to surface content and, in our case, large files in the search results
- It might be useful to combine the above-mentioned trick with other managed properties via Boolean Operators. For example, you might want to limit search results to large PDFs. In such a case, your search query will be: size > 1000000 AND filetype:pdf, Just like you can see in the image above.
- You can use the above command anywhere where you have a search box – this includes the SharePoint Document Library search box, SharePoint Site search box, OneDrive for Business Search Box, and Microsoft Teams search box.
- Thousands separator in the number that designates the file size is not allowed. In other words, if you are searching for files with sizes greater than 10MB, you should type in size > 10000000, not size > 10,000,000
- Do not forget (as I did until my loyal blog follower pointed out!) that 1 KB (kilobyte) is equal to 1,024 bytes, not 1,000 bytes. Likewise, 1 MB = 1024KB = 1,048,576 Bytes. So sometimes, the search queries might turn up results that you would think are wrong and do not make sense, like in the example below. I have a file that is 49.9 MB, and when I execute a search for files > 52000000 bytes, it still shows up. That is because 49.9 MB translates into 52,323,942 bytes, which is obviously slightly higher than 52,000,000 bytes!
The video file is 49.9 MB…
…yet, it is picked up by size search when I query for file sizes larger than 52 MB