How to Export OneDrive Usage Report in Microsoft 365?

OneDrive is a key component of Microsoft 365, offering cloud storage for businesses and personal use. If you manage a Microsoft 365 account, you might want to track OneDrive usage to understand how your users are storing and sharing data. The good news is that Microsoft 365 makes it easy to export OneDrive usage report. In this simple guide, we will show you how to do it in just a few steps.

Why Export Microsoft 365 OneDrive Report?

Exporting a OneDrive usage report gives you insight on how your organization is using cloud storage. You can see:

• How much storage is being used by each user.
• Who has the most active files.
• Overall trends in file uploads and downloads.

This data is helpful for IT administrators, especially when managing storage limits or optimizing the user experience.

Export OneDrive Usage Report in Microsoft 365

We can export OneDrive usage reports through Admin Center and Microsoft PowerShell through script.

OneDrive Usage Report Export from Office 365 Admin Center

By default, when we export report, it shows GUID in the report instead of user principal name (UPN). To display the UPN in the report we need to make some changes to the report under ORG settings.

• Login to your Microsoft 365 account with admin credentials and go to the admin center.
• Click on the settings, then Org settings in the left side panel of the admin center.

• In the right side, click on the reports and uncheck the option “display concealed user, group and site names in all reports” and save.

• After saving the changes, click on the Usage under Reports in the left side panel.

• Now a new window will open, click on OneDrive and scroll down and click on the Export A new window will open you will be notified of the downloading of the report.

Microsoft 365 OneDrive Report through PowerShell

Those who are familiar with the PowerShell and find it easy to use can Export OneDrive Usage report through it.

• Install Microsoft Graph Module in PowerShell by running the below scripts as an Administrator. It will take some time to install the modules.

Install-Module Microsoft.Graph -Force

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

• Now copy the below PowerShell script and paste it in your notepad. Make changes in the 5th and 6^th line of the script by putting the path where you want to export your files.

</pre>
# Connect to Microsoft Graph with necessary permissions

Connect-MgGraph -NoWelcome -Scopes "User.Read.All", "Reports.Read.All", "ReportSettings.ReadWrite.All"

&nbsp;

# Define file paths

$CSVOutputFile = "C:\Users\pjdoh\OneDrive\Desktop\OneDriveSize.csv"

$TempExportFile = "C:\Users\pjdoh\OneDrive\Desktop\TempExportFile.csv"

&nbsp;

# Remove the temporary export file if it exists

if (Test-Path $TempExportFile) {

Remove-Item $TempExportFile

}

&nbsp;

# 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

}

&nbsp;

# Retrieve detailed user account information

Write-Host "Fetching user account details from Microsoft Graph..." -ForegroundColor Cyan

&nbsp;

# Define user properties to be retrieved

$Properties = 'Id', 'displayName', 'userPrincipalName', 'city', 'country', 'department', 'jobTitle', 'officeLocation'

&nbsp;

# 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'

}

&nbsp;

# Get user account information and select the desired properties

$Users = Get-MgUser @UserParams -Property $Properties | Select-Object -Property $Properties

&nbsp;

# Create a hashtable to map UPNs (User Principal Names) to user details

$UserHash = @{}

foreach ($User in $Users) {

$UserHash[$User.userPrincipalName] = $User

}

&nbsp;

# 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

&nbsp;

# Import the data from the temporary CSV file

$ODFBSites = Import-CSV $TempExportFile | Sort-Object 'User display name'

&nbsp;

if (-not $ODFBSites) {

Write-Host "No OneDrive sites found." -ForegroundColor Yellow

return

}

&nbsp;

# Calculate total storage used by all OneDrive for Business accounts

$TotalODFBGBUsed = [Math]::Round(($ODFBSites.'Storage Used (Byte)' | Measure-Object -Sum).Sum / 1GB, 2)

&nbsp;

# Initialize a list to store report data

$Report = [System.Collections.Generic.List[PSCustomObject]]::new()

&nbsp;

# 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)

}

&nbsp;

# 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

&nbsp;

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

&nbsp;

# 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

}

&nbsp;

# Clean up the temporary export file

if (Test-Path $TempExportFile) {

Remove-Item $TempExportFile

Write-Host "Temporary export file removed." -ForegroundColor Cyan

}
<pre>

• After making changes, copy the script and run it in Windows PowerShell. The final output after completion on the script will be as below.

• Also, a window will open showing the data of the report.

• You can also go to the location where you chose to export your OneDrive Usage Report file. You can open it in Excel or any spreadsheet tool to analyze the data. Look for trends like heavy storage users or inactive accounts, which can help you manage your resources more effectively.

Conclusion:

Exporting a OneDrive usage report in Microsoft 365 is quick and easy. With just a few clicks, you can export valuable insights about how your users interact with OneDrive. Both the methods mentioned in this article are easy and can be used to export OneDrive usage report. Whether you need reports for Checking storage space or for specific reasons, this guide has got you covered.