Export OneDrive usage report in Microsoft 365

Get OneDrive storage used size in Microsoft 365

To get the OneDrive storage used size for a user in Microsoft 365, follow the below steps:

1. Sign in to Microsoft 365 admin center
2. Click on Users > Active users
3. Click on the user from the list

4. Click on OneDrive
5. Check the Storage used section

The advantage is that the OneDrive storage used data is populated within a few minutes.

Export OneDrive usage report in Microsoft 365 admin center

To export OneDrive usage report for all users in Microsoft 365, follow the below steps:

1. Sign in to Microsoft 365 admin center
2. Click on Settings > Org settings > Services

3. Click Reports
4. Uncheck Display concealed user, group, and site names in all reports
5. Click Save

6. Click Reports > Usage
7. Click OneDrive and scroll down to the bottom
8. Click on Export to download the OneDrive usage report CSV file

The disadvantage of this report is that the data is not up to date right away. The reports become available within 48 hours.

Export OneDrive usage report with PowerShell script

The Get-OneDriveSizeReport.ps1 PowerShell script will get the OneDrive size for all users and output the below information:

1. Owner
2. UPN
3. SiteId
4. IsDeleted
5. LastActivity
6. FileCount
7. ActiveFileCount
8. QuotaGB
9. UsedGB
10. PercentUsed
11. City
12. Country
13. Department
14. JobTitle

To export OneDrive usage report for all users with PowerShell, follow the below steps:

Step 1. Install Microsoft Graph PowerShell

Run Windows PowerShell as administrator and Install Microsoft Graph PowerShell.

Install-Module Microsoft.Graph -Force

Install-Module Microsoft.Graph.Beta -AllowClobber -Force

Step 2. Download Get-OneDriveSizeReport PowerShell script

Create two folders on the (C:) drive:

Download and place Get-OneDriveSizeReport.ps1 PowerShell script in the C:\Scripts folder. The script will export all the users OneDrive sizes to a report in the C:\Temp folder.

Ensure the file is unblocked to prevent errors when running the script. Read more in the article Not digitally signed error when running PowerShell script.

Another option is to copy and paste the code below into Notepad. Give it the name Get-OneDriveSizeReport.ps1 and place it in the C:\Scripts folder.

<#
    .SYNOPSIS
    Get-OneDriveSizeReport.ps1

    .DESCRIPTION
    Export OneDrive storage usage in Microsoft 365 to CSV file with PowerShell.

    .LINK
    www.alitajran.com/export-onedrive-usage-report/

    .NOTES
    Written by: ALI TAJRAN
    Website:    www.alitajran.com
    LinkedIn:   linkedin.com/in/alitajran

    .CHANGELOG
    V1.00, 08/10/2024 - Initial version
#>

# Connect to Microsoft Graph with necessary permissions
Connect-MgGraph -NoWelcome -Scopes "User.Read.All", "Reports.Read.All", "ReportSettings.ReadWrite.All"

# Define file paths
$CSVOutputFile = "C:\temp\OneDriveSizeReport.csv"
$TempExportFile = "C:\temp\TempExportFile.csv"

# Remove the temporary export file if it exists
if (Test-Path $TempExportFile) {
    Remove-Item $TempExportFile
}

# Check if tenant reports have concealed user data, and adjust settings if necessary
if ((Get-MgBetaAdminReportSetting).DisplayConcealedNames -eq $true) {
    $Parameters = @{ displayConcealedNames = $false }
    Write-Host "Unhiding concealed report data to retrieve full user information..." -ForegroundColor Cyan
    Update-MgBetaAdminReportSetting -BodyParameter $Parameters
    $ConcealedFlag = $true
}
else {
    $ConcealedFlag = $false
    Write-Host "User data is already fully visible in the reports." -ForegroundColor Cyan
}

# Retrieve detailed user account information
Write-Host "Fetching user account details from Microsoft Graph..." -ForegroundColor Cyan

# Define user properties to be retrieved
$Properties = 'Id', 'displayName', 'userPrincipalName', 'city', 'country', 'department', 'jobTitle', 'officeLocation'

# Define parameters for retrieving users with assigned licenses
$userParams = @{
    All              = $true
    Filter           = "assignedLicenses/`$count ne 0 and userType eq 'Member'"
    ConsistencyLevel = 'Eventual'
    CountVariable    = 'UserCount'
    Sort             = 'displayName'
}

# Get user account information and select the desired properties
$Users = Get-MgUser @UserParams -Property $Properties | Select-Object -Property $Properties

# Create a hashtable to map UPNs (User Principal Names) to user details
$UserHash = @{}
foreach ($User in $Users) {
    $UserHash[$User.userPrincipalName] = $User
}

# Retrieve OneDrive for Business site usage details for the last 30 days and export to a temporary CSV file
Write-Host "Retrieving OneDrive for Business site usage details..." -ForegroundColor Cyan
Get-MgReportOneDriveUsageAccountDetail -Period D30 -Outfile $TempExportFile

# Import the data from the temporary CSV file
$ODFBSites = Import-CSV $TempExportFile | Sort-Object 'User display name'

if (-not $ODFBSites) {
    Write-Host "No OneDrive sites found." -ForegroundColor Yellow
    return
}

# Calculate total storage used by all OneDrive for Business accounts
$TotalODFBGBUsed = [Math]::Round(($ODFBSites.'Storage Used (Byte)' | Measure-Object -Sum).Sum / 1GB, 2)

# Initialize a list to store report data
$Report = [System.Collections.Generic.List[PSCustomObject]]::new()

# Populate the report with detailed information for each OneDrive site
foreach ($Site in $ODFBSites) {
    $UserData = $UserHash[$Site.'Owner Principal name']
    $ReportLine = [PSCustomObject]@{
        Owner             = $Site.'Owner display name'
        UserPrincipalName = $Site.'Owner Principal name'
        SiteId            = $Site.'Site Id'
        IsDeleted         = $Site.'Is Deleted'
        LastActivityDate  = $Site.'Last Activity Date'
        FileCount         = $Site.'File Count'
        ActiveFileCount   = $Site.'Active File Count'
        QuotaGB           = [Math]::Round($Site.'Storage Allocated (Byte)' / 1GB, 2)
        UsedGB            = [Math]::Round($Site.'Storage Used (Byte)' / 1GB, 2)
        PercentUsed       = [Math]::Round($Site.'Storage Used (Byte)' / $Site.'Storage Allocated (Byte)' * 100, 2)
        City              = $UserData.city
        Country           = $UserData.country
        Department        = $UserData.department
        JobTitle          = $UserData.jobTitle
    }
    $Report.Add($ReportLine)
}

# Export the report to a CSV file and display the data in a grid view
$Report | Sort-Object UsedGB -Descending | Export-CSV -NoTypeInformation -Encoding utf8 $CSVOutputFile
$Report | Sort-Object UsedGB -Descending | Out-GridView -Title OneDriveUsageReport

Write-Host ("Current OneDrive for Business storage consumption is {0} GB. Report saved to {1}" -f $TotalODFBGBUsed, $CSVOutputFile) -ForegroundColor Cyan

# Reset tenant report data concealment setting if it was modified earlier
if ($ConcealedFlag -eq $true) {
    Write-Host "Re-enabling data concealment in tenant reports..." -ForegroundColor Cyan
    $Parameters = @{ displayConcealedNames = $true }
    Update-MgBetaAdminReportSetting -BodyParameter $Parameters
}

# Clean up the temporary export file
if (Test-Path $TempExportFile) {
    Remove-Item $TempExportFile
    Write-Host "Temporary export file removed." -ForegroundColor Cyan
}

• Line 24/25: Edit the CSV file path

Step 3. Run Get-OneDriveSizeReport PowerShell script

Run the below command to run the Get-OneDriveSizeReport.ps1 PowerShell script.

c:\scripts\.\Get-OneDriveSizeReport.ps1

The below output appears.

Unhiding concealed report data to retrieve full user information...
Fetching user account details from Microsoft Graph...
Retrieving OneDrive for Business site usage details...
Current OneDrive for Business storage consumption is 124,19 GB. Report saved to C:\temp\OneDriveSizeReport.csv
Re-enabling data concealment in tenant reports...
Temporary export file removed.

The report output is sent to an interactive table in a separate window (Out-GridView).

Step 4. Check OneDrive usage report CSV file

The Get-OneDriveSizeReport.ps1 PowerShell script exports all Microsoft 365 users OneDrive sizes to a CSV file.

Find the file OneDriveSizeReport.csv in the path C:\temp.

Open the CSV file with your favorite application. In our example, it’s Microsoft Excel.

That’s it!

Read more: Configure OneDrive administrative template files (ADMX/ADML) »

Conclusion

You learned how to export OneDrive usage report in Microsoft 365. If you want to instantly check the OneDrive size data for a user, look up the user in Microsoft 365 admin center. To get a report with more details, use the report section in Microsoft 365 admin center or use PowerShell. The disadvantage is that you need to wait 48 hours for the data to become available.

Did you enjoy this article? You may also like Microsoft 365 security recommendations with PowerShell script. Don’t forget to follow us and share this article.