📁 Recipe · SharePoint & OneDrive
Identify Orphaned and Inactive OneDrive Accounts via Graph Usage Reports
Cross-reference OneDrive usage data with Entra ID to surface ex-employee drives, inactive accounts, and storage hogs ready for cleanup, transfer, or retention review
Complexity
Intermediate
Impact
storage-reclamation + offboarding + compliance + cost-optimization + tenant-hygiene
Context
Why This Matters
OneDrive for Business storage grows silently. When employees leave, are disabled in Entra ID, or simply stop using OneDrive, their personal sites often remain in place — consuming licensed storage, holding potentially sensitive data, and creating retention and compliance risk.
This recipe identifies three categories of OneDrive accounts that need attention:
- Orphaned drives — the owner's Entra ID account is disabled or no longer exists in the directory (typical for ex-employees).
- Inactive drives — the owner is still active, but the OneDrive has had no file activity for 90+ days.
- Large drives — drives exceeding a configurable size threshold (e.g., 10 GB or 50 GB) that may warrant review regardless of activity.
Run this monthly or quarterly as part of routine tenant hygiene, or before a storage-tier purchase decision. It is especially valuable after bulk offboardings, M&A events, or license true-ups.
Expected Outcomes
What you will produce
- A tenant-wide inventory of every OneDrive with owner UPN, display name, storage used, last activity date, and Entra ID account status.
- A prioritized list of orphaned/disabled-owner drives sorted by storage footprint — the highest-impact cleanup candidates.
- A list of inactive drives belonging to currently-active users who may need nudging or whose data may be archivable.
- A list of large drives (above your configurable threshold) for storage capacity review.
- A downloadable CSV report (
OneDrive_Cleanup_Analysis.csv) you can share with stakeholders or feed into a follow-on transfer/deletion workflow.
Typical impact
In a 600-user tenant, expect to find 50–150 orphaned drives holding 100–500 GB of reclaimable storage — often enough to defer a storage add-on purchase.
Risks & Considerations
Before you delete anything
- Usage report anonymization. By default, Microsoft 365 anonymizes user-level reports. You must disable the "Display concealed user, group, and site names in all reports" privacy setting in the M365 Admin Center for Owner Principal Name and Site URL to populate. Re-enable it after your analysis if policy requires.
- Reporting latency.
Last Activity DateandStorage Usedlag real activity by up to 48 hours. Do not act on same-day data. - "Orphaned" is not "deletable." A disabled account may be on a legal hold, in a retention policy, or pending a manager-led data transfer. Always check eDiscovery holds and your organization's offboarding SOP before removing a OneDrive.
- Deleted-user grace period. When an Entra ID user is deleted, their OneDrive enters a retention period (default 30 days, configurable up to 3650). Surface these before the retention window closes so data owners can claim content.
- Data transfer first. For ex-employee drives with business content, reassign ownership to the manager via SharePoint Admin Center before any cleanup action. This preserves access for teams that need the files.
- Shared files break on deletion. If an orphaned OneDrive contains files shared with active users, deleting the drive breaks those links. Inventory external sharing before acting.
- Report permissions are sensitive.
Reports.Read.Allexposes usage data for every user. Scope app registrations tightly and rotate secrets regularly.
Required Permissions
| Permission | Why It's Needed |
|---|---|
| Reports.Read.All | Required to call getOneDriveUsageAccountDetail and retrieve per-user storage and activity data. |
| User.Read.All | Required to enumerate Entra ID users and check accountEnabled status for orphan detection. |
| Sites.Read.All | Optional — required if you want to enrich the report with site-level metadata (e.g., last modified, owner SPO properties). |
| Directory.Read.All | Alternative to User.Read.All when the app also needs group or directory object data for the cross-reference. |
The fastest way to get this done: just ask Dex. Copy the prompt below and paste it into your Dex conversation.
For IT Admins
Paste into Dex Pro
Manual walkthrough — Microsoft 365 Admin Center
Use this approach when you need a one-off report and don't have scripting access. Expect 30–45 minutes end-to-end.
Step 1 — Temporarily disable report anonymization
- Sign in to admin.microsoft.com as a Global Administrator.
- Navigate to .
- Uncheck Display concealed user, group, and site names in all reports.
- Click Save. Changes take effect within a few minutes.
Step 2 — Export the OneDrive usage detail report
- Navigate to .
- Set the date range to Last 90 days.
- Click the Export (↓) icon in the top-right and save the CSV.
Step 3 — Export the user directory
- Navigate to .
- Click Export users and save the CSV. This file includes Display Name, User Principal Name, and Block Sign-in columns.
- Navigate to and note any UPNs still within the soft-delete window.
Step 4 — Cross-reference in Excel
- Open the OneDrive usage CSV. Add a column
Account Status. - Use
VLOOKUPorXLOOKUPagainst the Active Users file, matching on User Principal Name, returning the Block Sign-in value. - Classify each row: missing from lookup = Orphaned; Block Sign-in = Yes = Disabled; otherwise Active.
- Add a
Days Since Last Activitycolumn:=TODAY()-[@[Last Activity Date]]. - Apply an AutoFilter: sort descending by Storage Used (Byte), filter Account Status to Orphaned/Disabled.
Step 5 — Review in SharePoint Admin Center
- For each high-priority orphaned drive, open SharePoint Admin Center.
- Go to .
- Click the site URL from your CSV, review secondary admins, sharing links, and storage breakdown.
- If action is needed, use Permissions → Manage site admins to grant yourself or the ex-employee's manager access before any transfer or deletion.
Step 6 — Re-enable anonymization
Return to Step 1's setting and re-check the anonymization box if your organization requires it.
Graph API recipe
The analysis is a two-call join: pull the OneDrive usage detail report, then pull the Entra ID user directory and cross-reference on UPN.
1. Fetch the OneDrive usage detail report
The report endpoint returns CSV by default. The $format=application/json query parameter is not supported here — requesting JSON returns HTTP 400.
GET https://graph.microsoft.com/v1.0/reports/getOneDriveUsageAccountDetail(period='D90')
Authorization: Bearer {token}
Accept: text/csvSupported period values: D7, D30, D90, D180.
Sample response (CSV)
Report Refresh Date,Site Id,Site URL,Owner Display Name,Is Deleted,
Last Activity Date,File Count,Active File Count,Storage Used (Byte),
Storage Allocated (Byte),Owner Principal Name,Report Period
2026-03-09,002a9740-...,https://contoso-my.sharepoint.com/personal/jdoe_contoso_com/Documents,
Jane Doe,False,2026-03-09,2741,276,2119011051,1099511627776,jdoe@contoso.com,902. Enumerate Entra ID users with pagination
GET https://graph.microsoft.com/v1.0/users?$select=userPrincipalName,accountEnabled,displayName,id&$top=999
Authorization: Bearer {token}Follow @odata.nextLink until all pages are consumed. For tenants > 1,000 users, plan for pagination.
Sample response
{
"value": [
{
"userPrincipalName": "jdoe@contoso.com",
"accountEnabled": true,
"displayName": "Jane Doe",
"id": "8a7b..."
}
],
"@odata.nextLink": "https://graph.microsoft.com/v1.0/users?$skiptoken=..."
}3. Classification logic
For each OneDrive row, look up Owner Principal Name (lowercased) in the user map:
- Not found → Orphaned (user deleted).
- Found, accountEnabled = false → Disabled (likely ex-employee).
- Found, accountEnabled = true, Last Activity Date > 90 days ago → Inactive.
- Storage Used (Byte) / 1073741824 > threshold → Large (flag independently).
Throttling & error handling
- The reports endpoint is throttled separately from the main Graph surface. Respect
Retry-Afterheaders on HTTP 429. - Users endpoint supports
ConsistencyLevel: eventual+$count=truefor faster advanced queries on large tenants. - Owner Principal Name will be empty strings (anonymized) if the tenant's concealment setting is on. Verify the setting before running.
PowerShell recipe
The script below uses Microsoft Graph PowerShell SDK to fetch both data sets, classify each drive, and emit a CSV. It handles pagination, throttling, and the CSV-only report format automatically.
Prerequisites
Install-Module Microsoft.Graph -Scope CurrentUser
Connect-MgGraph -Scopes "Reports.Read.All", "User.Read.All"What the script does
- Calls
Get-MgReportOneDriveUsageAccountDetail(wraps the Graph report endpoint) and loads the CSV into memory. - Pages through
Get-MgUserto build a hash table keyed on lowercase UPN. - Walks every OneDrive row, tags it with Orphaned, Disabled, Inactive, or Active, and calculates GB usage.
- Exports three CSVs: orphaned, inactive, and a combined cleanup candidates list.
- Prints a console summary with top-10 reclamation targets.
Run it
.\Get-OneDriveCleanupReport.ps1 -OutputPath "C:\Reports" -InactiveDaysThreshold 90 -LargeDriveGBThreshold 10Tuning thresholds
-InactiveDaysThreshold: default 90. Raise to 180 for quarterly-active users, lower to 30 for aggressive cleanup.-LargeDriveGBThreshold: default 10. Use 50 or 100 for large-tenant executive-focused reviews.-Period: D7, D30, D90, or D180. D90 balances recency with data completeness.
The script is idempotent and safe to run on a schedule — it only reads data.
Next steps