Forum Discussion

Copper Contributor

Apr 28, 2023

Solved

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 ...

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

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

jmcarthur

Copper Contributor

Oct 09, 2024

Josef76 I made an additional change that will likely help others. Rather than using a basic list/array, I implemented a Generic List. Every time you += to an array, it copies the entire thing. So, in the case where a SP site has tens of thousands of files, this will consume a massive amount of memory. See this example for adding elements to the list. All other operations are the same as a regular array.

$FileData = New-Object System.Collections.Generic.List[PSCustomObject]
Foreach ($Item in $ListItems)
{
    $newObj = 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.Add($newObj) # <------ This is the key operation
}

Brijesh1978
Copper Contributor
Jul 04, 2024
Josef76 NicolasKheirallah script is working perfectly.
Thank much for sharing.
Is there a way to add last accessed column in the report?
Thanks
Brijesh
- NicolasKheirallah
  MVP
  Jul 10, 2024
  last accessed column is unfortunatly a SharePoint On-prem only field. So that's nothing we can query
sbammer
Copper Contributor
May 02, 2024
Josef76 my question is how do I modify this script to loop through ALL our SharePoint online sites and get the versioning information. We have 18,000+ SharePoint sites and over 20 million files. Thanks in advance.
- Josef76
  Copper Contributor
  Nov 22, 2024
  I don't know if you still need this, but by chance, I was doing a script to delete versions, there I'm also counting the size occupied by them. Here the website I based my script:
  https://www.sharepointdiary.com/2018/05/sharepoint-online-delete-version-history-using-pnp-powershell.html#ixzz8sKcVNlfC
  
  The scripts there show how to delete the versions, but you can also use it for just getting the information you are looking for. From the file object, you can get data like:
  $file.ServerRelativeUrl -> File location
  $item["Author"].Email -> This one is tricky because $file also has a user object, but it is empy
  $file.Length
  $file.TimeCreated
  $file.TimeLastModified
  $file.Versions -> This is what you are interested
  The version object have this attributes that you may need.
  $versions[$i].Created
  $versions[$i].Size
  
  On the page, you will see several examples for iterating over the versions; however, there is something not mentioned there: if you make a lot of requests in a minute, then you will be throttled. See:
  https://learn.microsoft.com/en-us/sharepoint/dev/general-development/how-to-avoid-getting-throttled-or-blocked-in-sharepoint-online
  
  So, you need to add pauses inside your script. For example for a Microsoft Tenant with less than 1000 licenses, you need to at at least a pause of 50 milliseconds. There you have a limit of 1200 request per minute (60000 milliseconds), so: 60000 / 1200 = 50 milliseconds. I set 55 to be sure. Usually the method "ExcecuteQuery" is the one that silently raises exceptions when overpassing the limits. So, you have to catch the exception and in case that it happens, you need to do a bigger pause, let's say 5 minutes, then call the "Load" methods again. I'm doing a script to delete versions and some times the exceptions comes, but the script continues normally after retrying once.
  
  There is another way of doing it and it is using the SharePoint CSOM Assemblies:
  https://www.sharepointdiary.com/2016/02/sharepoint-online-delete-version-history-using-powershell.html
  
  Some people said that those assemblies are even better for larger libraries, but I haven't used them. However, it looks like quite similar.
  
  Best regards
  Josef
NicolasKheirallah
MVP
Feb 20, 2024
Good job! 🙂