SharePoint storage report in Microsoft 365

Hi,

We have reached the allowed SharePoint storage for our tenant. Currently we have two options, pay for additional storage (this is NOT the preferred option) or to remove some unused data.

In the SharePoint admin center I see the site collection sizes, but I do not see any detailed information. Is there any way to get a better picture, what data we have and where can we spare some space by removing old or unwanted data?

We have hundreds of sites, so a manual approach (using the built in storage metrics) is not really a working one.

Thanks,

pngit

NicolasKheirallah Thanks for your script, it works; however, there are some modifications that need to be done to make it working under PowerShell 7.4 and using two factor authentication:

#Set Variables
$SiteURL = "https://your_tenant.sharepoint.com/sites/your_team_site"
$LibraryName = "Documents"
$ReportOutput = "C:\Temp\getAllFiles.csv"

#Please note that on my case, the modules: 
#Microsoft.Online.SharePoint.Powershell and
#Microsoft.Online.SharePoint.Powershell didn't get loaded
#automatically, so have to manually import them

#This doesn't seems to work with Powershell 7.4 because it seems
#that this module is not yet compatible. You will get some warnings
#Import-Module Microsoft.Online.SharePoint.Powershell

#This will work with Powershell 7.4, I guess it is using version
#5.1 for the import
Import-Module Microsoft.Online.SharePoint.Powershell -UseWindowsPowerShell

Import-Module PnP.PowerShell

#Connect to SharePoint Online site. Please note that since I'm
#using two factor authentication, Get-Credential won't work, so,
#I used the "Connect-PnPOnline" with the "-Interactive" option,
#then a Window will popup
Connect-PnPOnline -Url $SiteURL -Interactive

$FileData = @()
#Iterate through all files
$ItemCounter = 0 

$ListItems = Get-PnPListItem -List $LibraryName -PageSize 500 -Fields Author, Editor, Created, File_x0020_Type, File_x0020_Size, Versions

Foreach ($Item in $ListItems)
{
  $FileData += New-Object PSObject -Property ([ordered]@{
    Name              = $Item["FileLeafRef"]
    Type              = $Item.FileSystemObjectType
    FileType          = $Item["File_x0020_Type"]
    FileSize =  [Math]::Round(($Item.FieldValues.File_x0020_Size/1KB),2)
    RelativeURL       = $Item["FileRef"]
    CreatedByEmail    = $Item["Author"].Email
    CreatedOn         = $Item["Created"]
    Modified          = $Item["Modified"]
    ModifiedByEmail   = $Item["Editor"].Email
  })
}

$FileData | Export-Csv -Path $ReportOutput -NoTypeInformation -Delimiter ";"

Thanks for the script.

Best regards

Josef

pngit
Copper Contributor
Aug 17, 2023
If you guys are interested, today I've found a possible solution for my problem described above.

https://www.youtube.com/watch?v=2Md9hQ7tRzc

Based on this video, it seems, it will generate the exactly the same information I was looking for. Has someone any experience with Storage Optimizer for Microsoft 365?

NicolasKheirallah

MVP

Apr 28, 2023

pngit

Yes and No,
You can see what sites are take the most storage but not anything else from admin portal.
What you can do is to get iterate through the each site and check the document libraries for files, the main issue here is version management. So 1gb file with 100 versions will be 100gb's.

You can modify this script, what you need to add it get get all sites, all document libraries in the site and add versioning:

#Set Variables
$SiteURL = ""
$LibraryName = "Documents"
$ReportOutput = "C:\Temp\getAllFiles.csv"
#Connect to SharePoint Online site
Connect-PnPOnline -Url $SiteURL -Interactive
 
$FileData = @()
#Iterate through all files
$ItemCounter = 0 


$ListItems = Get-PnPListItem -List $LibraryName -PageSize 500 -Fields Author, Editor, Created, File_x0020_Type, File_x0020_Size, Versions

Foreach ($Item in $ListItems)
{

            $FileData += New-Object PSObject -Property ([ordered]@{
            Name              = $Item["FileLeafRef"]
            Type              = $Item.FileSystemObjectType
            FileType          = $Item["File_x0020_Type"]
            FileSize =  [Math]::Round(($Item.FieldValues.File_x0020_Size/1KB),2)
            RelativeURL       = $Item["FileRef"]
            CreatedByEmail    = $Item["Author"].Email
            CreatedOn         = $Item["Created"]
            Modified          = $Item["Modified"]
            ModifiedByEmail   = $Item["Editor"].Email
        })
}

$FileData | Export-Csv -Path $ReportOutput -NoTypeInformation -Delimiter ";"

Josef76

Copper Contributor

Feb 20, 2024

NicolasKheirallah Thanks for your script, it works; however, there are some modifications that need to be done to make it working under PowerShell 7.4 and using two factor authentication:

#Set Variables
$SiteURL = "https://your_tenant.sharepoint.com/sites/your_team_site"
$LibraryName = "Documents"
$ReportOutput = "C:\Temp\getAllFiles.csv"

#Please note that on my case, the modules: 
#Microsoft.Online.SharePoint.Powershell and
#Microsoft.Online.SharePoint.Powershell didn't get loaded
#automatically, so have to manually import them

#This doesn't seems to work with Powershell 7.4 because it seems
#that this module is not yet compatible. You will get some warnings
#Import-Module Microsoft.Online.SharePoint.Powershell

#This will work with Powershell 7.4, I guess it is using version
#5.1 for the import
Import-Module Microsoft.Online.SharePoint.Powershell -UseWindowsPowerShell

Import-Module PnP.PowerShell

#Connect to SharePoint Online site. Please note that since I'm
#using two factor authentication, Get-Credential won't work, so,
#I used the "Connect-PnPOnline" with the "-Interactive" option,
#then a Window will popup
Connect-PnPOnline -Url $SiteURL -Interactive

$FileData = @()
#Iterate through all files
$ItemCounter = 0 

$ListItems = Get-PnPListItem -List $LibraryName -PageSize 500 -Fields Author, Editor, Created, File_x0020_Type, File_x0020_Size, Versions

Foreach ($Item in $ListItems)
{
  $FileData += New-Object PSObject -Property ([ordered]@{
    Name              = $Item["FileLeafRef"]
    Type              = $Item.FileSystemObjectType
    FileType          = $Item["File_x0020_Type"]
    FileSize =  [Math]::Round(($Item.FieldValues.File_x0020_Size/1KB),2)
    RelativeURL       = $Item["FileRef"]
    CreatedByEmail    = $Item["Author"].Email
    CreatedOn         = $Item["Created"]
    Modified          = $Item["Modified"]
    ModifiedByEmail   = $Item["Editor"].Email
  })
}

$FileData | Export-Csv -Path $ReportOutput -NoTypeInformation -Delimiter ";"

Thanks for the script.

Best regards

Josef

NicolasKheirallah
MVP
Feb 20, 2024
Good job! 🙂

pngit
Copper Contributor
Apr 28, 2023
hi NicolasKheirallah,

Thanks for the code part. I'm looking more like an OoB solution rather than programming something like this. I need to prepare reports for management etc. If I have to implement everything from scratch (I have only a little PS experience), it would take time ...
So, let's wait if someone else knows about an existing app for this purpose.
- jcgonzalezmartin
  MVP
  Apr 29, 2023
  Unfortunately there is not an OOB to get this information Toay

NanddeepNachan
Learn Expert
Apr 30, 2023
Hi pngit,

You can get the partial information from M365 Admin Center > Reports > Usage section as shown below:

This section will help you understand the sites which have more number of inactive files. However, this section will not show those exact file listings.
- pngit
  Copper Contributor
  May 02, 2023
  Thanks for your response. I'm aware of this report, however, it does not give me, what I need. It helps only narrowing down the list of sites, what need to be checked manually (using the built in storage metrics or similar). This is a huge manual work again ...
Faisal1775
Brass Contributor
May 02, 2023
pngit
To view a SharePoint storage report in Microsoft 365, you can follow these steps:
Go to the Microsoft 365 admin center (https://admin.microsoft.com) and sign in with your admin credentials.
In the left navigation menu, click on "Reports."
In the "Reports" section, click on "Usage."
In the "Usage" section, click on "SharePoint."
This will take you to the SharePoint usage report, which shows you a breakdown of storage usage for each SharePoint site in your organization. You can view the report by day, week, month, or year, and you can filter the report by site, user, or file type.
To export the report, click on the "Export" button in the upper-right corner of the report and select your desired format (CSV or XLSX).
Note that you must have the necessary permissions to view the SharePoint usage report. Specifically, you must be a global or SharePoint admin, or you must have the "View usage data" permission for the SharePoint site you want to view the report for.

Forum Discussion

SharePoint storage report in Microsoft 365

Share

Resources