If you work in a MS-based environment, it is likely you have seen that as of January 27th 2025, you will start getting charged for retained OneDrive data.

https://learn.microsoft.com/en-us/sharepoint/unlicensed-onedrive-accounts?WT.mc_id=365AdminCSH_spo 

The storage cost for the will be $US 0.05/GB/Month – which can add up very quickly.

https://mc.merill.net/message/MC836942

In the environment i’ve recently started working in, there was a guy who was previously working in the role, who i am growing to absolutely despise, who made sure that we would experience maximum hassle when trying to extricate ourselves from his exceedingly poor and short-sighted decisions.

Step 1 – Determine the size of the issue

• Go to SharePoint admin center > Reports > OneDrive accounts
• You will be presented with the size of your issue immediately. Here is an example from my work tenant.
• 
• Assuming you have an issue, click “Download report”
• This report will give us three important things
  • The direct urls for the onedrive sites we are interested in looking at
  • The reason the OneDrive is unlicensed
  • Why the deletion has been blocked

Step 2 – Analysing the report data

• In the “Unlicensed due to” column you will see 3 options
  • Owner deleted from EntraID
    • Reasonably self-explanatory. The account has been deleted, but importantly, no-one else has been granted ownership over the OneDrive – and it is likely safe to delete
  • License removed by admin
    • License has been removed, but the user still exists. In our environment this seemed to be mainly shared resources that were accidentally allocated a license when they were created.
  • Duplicate account
    • This generally indicates that ownership of the OneDrive has been reallocated to another current user. It can also occur when a user leaves and comes back and the “old” OneDrive is still there due to a retention policy.
• In the “Delete blocked by” column you also will see 3 options
  • Retention period
    • This is the OneDrive retention period for unlicensed users as specified here
    • https://<YourSiteName-admin.sharepoint.com/_layouts/15/online/AdminHome.aspx#/settings/OrphanedPersonalSitesRetentionPeriod
    • 
    • As you can see – ours is set 2555 days…. or 7 years.
  • Retention policy
    • This is O365 retention policy and is configured at https://purview.microsoft.com/datalifecyclemanagement/retention?tid=30013e13-ff75-43c6-919a-8a3326fc3806
    • Given Microsoft’s desire to change names and URL’s of their cloud stuff so often – it probably will be called something else by the time you read this
  • Owner active in Entra ID
    • This means that another user, who is active has been granted access over the OneDrive.
• For us at least, all entries are blocked by both the OneDrive retention period and the retention policy. Some are (in addition to the other two) also blocked by “Owner active in Entra ID”

Step 3 – an approach to sorting through this mess

• Take the .csv downloaded in step 1 and open it in Excel
• Enable auto-filter – and class the deleted OneDrives into 3 categories
  • “Owner deleted from EntraID”
    • In my instance, there was general agreement that where the user has simply been deleted, that we didn’t need to do any further analysis on these accounts. An email would be sent out to business to explain that old OneDrive accounts would no longer be available as of x. The problem is here of course, that many users don’t know if/when they are accessing a file from a shared OneDrive.
  • “License removed by admin”
    • We need to find out more details here, such as if the account is a shared mailbox that was accidentally licensed. Who the owner is. Does the OneDrive contain any data etc.
  • “Duplicate account”
    • These are the OneDrives that are most likely to be in active use and require further analysis

Step 4 – Slightly more detailed analysis

• As per anything that is in anyway related to sharepoint, this is way harder than it needs to be.
• Filter the Excel document to your desired list (.e.g duplicate accounts)
  • Copy and paste the urls into a txt document and use it to drive the following script
  • Note : This script will grant your admin account “SiteCollection” admin rights! If you need to seek permission to do this from management first – do that before running this script!

# Connect to SharePoint Online Admin Center
$adminSiteUrl = “https://<YourSite>-admin.sharepoint.com”
Connect-SPOService -Url $adminSiteUrl

# Path to the text file with the list of OneDrive URLs
$onedriveUrlsFilePath = “C:\Temp\DuplicateAccount.txt”

# Path to the output CSV file
$outputCsvFilePath = C:\Temp\DuplicateAccount.csv”

# Import the OneDrive URLs from the text file
$onedriveUrls = Get-Content -Path $onedriveUrlsFilePath

# Initialize an empty array to store the results
$onedriveDetails = @()

# Loop through each OneDrive URL
foreach ($onedriveUrl in $onedriveUrls) {
# Get site details (StorageUsageCurrent and LastContentModifiedDate)
Write-host “Getting details for account: $onedriveUrl”
$siteDetails = Get-SPOSite -Identity $onedriveUrl -Detailed

#Grant siteAdmin permissions
Set-SPOUser -Site $oneDriveUrl -LoginName <YourAdminUsername> -IsSiteCollectionAdmin $true

$SiteAdmins = Get-SPOUser -Site $onedriveUrl -Limit All | Where-Object { $_.IsSiteAdmin -eq $true }
$SiteMembers = Get-SPOUser -Site $onedriveUrl -Limit All | Where-Object { $_.IsSiteAdmin -eq $false }

# Store the details in a PowerShell object
$onedriveInfo = [PSCustomObject]@{
URL = $onedriveUrl
Owner = $siteDetails.Owner
StorageUsageCurrent = $siteDetails.StorageUsageCurrent
LastContentModifiedDate = $siteDetails.LastContentModifiedDate
SiteAdmins = ($SiteAdmins | ForEach-Object { $_.LoginName }) -join “; ”
SiteMembers = ($SiteMembers | ForEach-Object { $_.LoginName }) -join “; ”
}

# Add the details to the array
$onedriveDetails += $onedriveInfo
}

# Export the details to a CSV file
$onedriveDetails | Export-Csv -Path $outputCsvFilePath -NoTypeInformation

The output from this script will give you a csv with

• The URL
• Owner
• Current usage
• Last content modified date
• Login ID’s of accounts that have SiteAdmin permissions
• Login ID’s of accounts that are site members – meaning that at some stage, the original user has shared a folder or document with one of these users from their OneDrive.

From here – you now are starting to get enough information to track down possible usages of these “old” OneDrives.

Now – if your anything like us – there is waaaay to many permissions in order for anyone to track all these down by contacting the users in question.

Step 5 – Potential “solutions”

• Now… solutions may be a bit of a strong word here…. so perhaps lets go with “ways of gradually vetting then removing access to data prior to it being deleted to improve your chances of not deleting something important”
• If someone wants to view a OneDrive contents before being deleted, you can grant them site collection admin via
  • GUI : <TheURLtoTheUsersOneDrive>/_layouts/15/mngsiteadmin.aspx (copy the url from the spreadsheet you created in step1)
  • Powershell : Set-SPOUser -Site <TheURLtoTheUsersOneDrive> -IsSiteCollectionAdmin:$true -LoginName <UPNofUserToGrantAccessToo>
• Due to a large number of members potentially having access to files in someone’s onedrive – and not knowing that they are accessing it from someone’s OneDrive, we can
  • Record who has access using the script above
  • Perform a scream test for a period of time by removing member access by using the following script

# Connect to SharePoint Online Admin Center
$adminSiteUrl = “https://<YourSite>-admin.sharepoint.com”
Connect-SPOService -Url $adminSiteUrl

# Path to the text file with the list of OneDrive URLs
$onedriveUrlsFilePath = “C:\Temp\Testing2.txt”

# Define the log file path
$logFilePath = “C:\Temp\Testing2.log”

# Import the OneDrive URLs from the text file
$onedriveUrls = Get-Content -Path $onedriveUrlsFilePath

# Function to log the removal of a user
function Log-UserRemoval {
param (
[string]$siteUrl,
[string]$userName
)
$timestamp = Get-Date -Format “yyyy-MM-dd HH:mm:ss”
$logEntry = “$timestamp – Removed user: $userName from site: $siteUrl”

# Append the log entry to the log file
Add-Content -Path $logFilePath -Value $logEntry
Write-Host $logEntry
}

# Loop through each OneDrive URL
foreach ($onedriveUrl in $onedriveUrls) {
Write-host “Getting details for account: $onedriveUrl”
# Define the OneDrive or SharePoint site URL
$siteUrl = $onedriveUrl

# Get all users from the site
$users = Get-SPOUser -Site $siteUrl -Limit All

# Loop through each user and check if they are not a site admin and if their username ends with @<yourDomainSuffix>
foreach ($user in $users) {
if ($user.IsSiteAdmin -eq $false -and $user.LoginName -like “*@<yourDomainSuffix>”) {
# Remove the user from the site if their login ends with <yourDomainSuffix>
Write-Host “Removing user: $($user.LoginName)”
Remove-SPOUser -Site $siteUrl -LoginName $user.LoginName

# Log the removal of the user
Log-UserRemoval -siteUrl $siteUrl -userName $user.LoginName
}
}

}

That’s my current best shot at this…. will be interested to hear if any of you have additional/different ways of tackling this mess, while the rest of organisation is like the below:

Other

Other random commands i found helpful

Build a list of all OneDrive urls

from https://learn.microsoft.com/en-us/sharepoint/list-onedrive-urls

$TenantUrl = Read-Host “Enter the SharePoint admin center URL”
$LogFile = [Environment]::GetFolderPath(“Desktop”) + “\OneDriveSites.log”
Connect-SPOService -Url $TenantUrl
Get-SPOSite -IncludePersonalSite $true -Limit all -Filter “Url -like ‘-my.sharepoint.com/personal/'” | Select -ExpandProperty Url | Out-File $LogFile -Force
Write-Host “Done! File saved as $($LogFile).”

In particular, update the “Url -like ‘-my.sharepoint.com/personal/'” to include part of a username you are interested in e.g. “Url -like ‘-my.sharepoint.com/personal/mike'”