Reporting on unlicensed OneDrive (for Business) accounts in Microsoft 365

Last year, Microsoft announced plans to start charging tenants for data stored in unlicensed OneDrives, that is drives that belong to unlicensed Microsoft 365 users. Unlike Exchange Online, where inactive mailboxes provide you with essentially free retention for the duration of any hold/policies assigned to the user, the functionality to preserve data for unlicensed (or removed) users’s drives in OneDrive for Business will cost you $0.05 per GB per month. I suppose this could be considered a generous offer, coming from the company that is still charging you $0.20 per GB per month for additional SPO storage in 2025…

Naturally, this opportunistic Microsoft 365 cashgrab has made some customers uneasy. As today, 17 Feb 2025 is the “cutoff” date for the switch to the new behavior, I figured it’s about time to post something on the topic. So, lets review what options has Microsoft provided us with, and also examine what additional tools we have at our disposal to identify OneDrive for Business accounts that fall into this category. The goal of course is to help you save some $$$ by making informed decisions.

We start with the built-in solution, namely the Unlicensed OneDrive accounts report/page in the SharePoint Online Admin Center. The summary view gives you the total number of unlicensed drives along with the total storage consumed. You’ll also get a breakdown by category, but to get some actionable data, you will need to go to the detailed view, or download the CSV report. And when I say actionable, do not expect any wonders. In fact the only action you can perform within the SPO admin center is to delete a selected drive, one at a time. Not even a deeplink to the user’s profile within the M365 Admin Center is provided.

The level of detail presented also leaves a lot to be desired. For some reasons, users are designated by their display name, not a full UPN or any unique-identifiable property. The corresponding ODFB URL is not even displayed by default, but you can remedy this by selecting it under Customize columns. Important pieces of information such as the last access and/or last modification timestamps are missing, and we’re also not given any clue as to whether files in a given drive are being accessed by other users in the company, or guests.

Yet another annoyance is the lack of (supported) methods for programmatic access to the report. No Graph API endpoint, to begin with, even though this is effectively a “usage” report, and one directly related to billing. As the detailed report is only generated after an action is performed in the SPO admin center, getting this data in a fully automated manner seems to be impossible. If you do have the report generated, it is actually rather easy to fetch it via the PnP module. A look at the network trace gives you all the details you need, namely the fact that its data is stored as the content of a file item within the “admin” site collection. In other words, you can do this:

Connect-PnPOnline -Url "https://tenant-admin.sharepoint.com" -ClientId xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx -CertificateBase64Encoded $base64encodedstring -Tenant tenant.onmicrosoft.com

#Fetch items in the "DO_NOT_DELETE_DOCLIB_ACTIVE_SITES_REPORT" list
$files = Get-PnPFolderItem -FolderSiteRelativeUrl /DO_NOT_DELETE_DOCLIB_ACTIVE_SITES_REPORT | ? {$_.Name -match "Sites_.*\.csv"}

#Fetch the file content
$report = Get-PnPFile $($files[0].ServerRelativeUrl) -AsString | ConvertFrom-Csv

 

Of course, we can also build our own solution, and address the missing usage information for any of the unlicensed drives in the process. Few things are needed to achieve this. To begin with, we will need the list of unlicensed OneDrive for Business drives. There are few ways we can go about this, the easiest probably being to leverage the Get-SPOSite cmdlet in order to get the list of site collections matching the “SPSPERS” template, then filter out any “licensed” ones. For example:

$OneDriveUsers = Get-SPOSite -IncludePersonalSite $true -Limit All -Template "SPSPERS" | select Status, ArchiveStatus, StorageUsageCurrent, Url, Owner

Unfortunately, no SPO cmdlet can tell us which licenses the owner of a given drive has been assigned, so we will have to also leverage the Graph SDK for PowerShell. In fact, we need to make a call to Get-MgUser anyway, as the value we retrieve via the Get-SPOSite cmdlet for the drive’s owner is a UPN, and we need its id as well, if we want to be thorough in our audit log search later on. We also need to know which service plans to check against, which includes both standalone ODFB plans and the “bundle” plans containing the SharePoint Online service.

#List of ODFB plans
$OneDrivePlans = @("b4ac11a0-32ff-4e78-982d-e039fa803dec","f7e5b77d-f293-410a-bae8-f941f19fe680","13696edf-5a08-49f6-8134-03083ed8ba30","4495894f-534f-41ca-9d3b-0ebf1220a423","afcafa6a-d966-4462-918c-ec0b4e0fe642","da792a53-cbc0-4184-a10d-e544dd34b3c1","98709c2e-96b5-4244-95f5-a0ebe139fb8a")

#List of SPO plans
$SharePointPlans = @("e95bec33-7c88-4a70-8e19-b10bd9d0c014","5dbe027f-2339-4123-9542-606e4d348a72","902b47e5-dcb2-4fdc-858b-c63a90a2bdb9","63038b2c-28d0-45f6-bc36-33062963b498","6b5b6a67-fc72-4a1f-a2b5-beecf05de761","c7699d2e-19aa-44de-8edf-1736da088ca1","0a4983bb-d3e5-4a09-95d8-b2d0127b3df5")

The lists above include some non-commercial plans as well… you never know when Microsoft will change their minds 🙂

Apart from fetching some generic details about the ODFB site collection and the associated user, a useful report should also include details on the drive’s activities. The best way to fetch those remains the Unified Audit log (I said best, not fast). Thus, our script will also need to call the Search-UnifiedAuditLog cmdlet in order to cover any activities against the drive’s URL in the past XX(X) days. For example:

Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate.AddDays(1) -SessionCommand ReturnLargeSet -ResultSize 5000 -SessionId $sessionID -RecordType SharePointFileOperation -ObjectIds $userUrl

To get the most comprehensive set of details, we filter only by the site URL (provided as input for the –ObjectIds parameter) and the SharePointFileOperation RecordType. Now, I will be the first to say that not every activity is equal, so you might consider including only the ones that make sense to you, by leveraging the –Operations parameter. This should have the side effect of speeding up the process a bit, as well. For example:

-Operations FileUploaded, FileAccessed, FileDeleted, FilePreviewed, FileModified, FileRenamed, FileModifiedExtended, FileCheckedIn, FileRecycled

Another thing to consider is filtering by –UserId, which has the added benefit of filtering out some “system” events. If you do choose to go this route, you will be unable to tell whether the drive has been accessed by anyone other than its owner though, which might be an important factor in the decision whether to remove or archive items stored within said drive. Similarly, no insight will be provided as to whether any external users have accessed items in the drive.

A demo script based on the cmdlets above is available on my GitHub repo. You can run it as a Global admin, or any user with sufficient permissions to call Get-SPOSite, Get-MgUser and Search-UnifiedAuditLog. If you want to run it non-interactively, make sure to replace the connectivity bits. The script also leverages the ImportExcel module in order to generate separate sheet with all the audit entries for a given drive. This makes it easier to decide if the drive should be considered active or not.

You can combine the output with the PnP code sample above in order to enhance the built-in unlicensed Onedrives report, instead of generating a separate output file. Remember that the detailed report in the SPO admin center is not automatically generated, so do make sure to click the link/download button first!

Overall, Microsoft should have done a better job with unlicensed OneDrives, and I’m not just talking about the ridiculous pricing here. It took me half an hour to put together a script that can generate report on unlicensed OneDrives with actual usage data, and turning it into a non-interactive solution would take just few additional touches. Adding some logic to delete the user/drive or assign a license to keep it active is also quite an easy task.

As a side note, it’s sad that even in 2025 we have to rely on multiple separate admin endpoint to perform basic Microsoft 365 admin operations. The Graph API can only be used to run asynchronous audit log searches and leaves a lot to be desired on the SPO admin front. Not impressed. Also not impressed with the fact that we still have to rely on multiple endpoints and portals to get reports, let alone reports related to billing.