Find All OneDrive for Business Sites using PowerShell

Requirement: Get a list of all OneDrive site collections from SharePoint Online.

I was asked to get the list of all OneDrive sites to get the usage of OneDrive for Business in our Office 365 tenant. It’s a bit tricky to find all of your OneDrive site collections, and here is how I achieved this using the Get-SPOSite cmdlet:

How to List all OneDrive for Business URLs?

OneDrive for Business is a cloud-based file storage service that is part of the Microsoft 365 suite of tools. As an admin, you may need to get a list of all OneDrive sites in your organization for auditing or management purposes. In this article, we will outline the steps to get all OneDrive sites in Microsoft 365 using the Microsoft 365 admin center and PowerShell.

To get a list of all OneDrive for Business sites in your organization through Microsoft 365 admin center, do the following:

  1. Login to Microsoft 365 Admin center at https://admin.microsoft.com
  2. Click on “Reports” in the left navigation >> Click on “Usage”.
  3. Under OneDrive Files, Click on “View More”
  4. Scroll down a bit to view the report that provides a list of all OneDrive sites. You can also export the report data into a CSV format for further analysis.List all OneDrive for Business URLs

Find All OneDrive Sites using SharePoint Online Management Shell:

OneDrive for Business provides centralized storage to save and share files. Files stored in OneDrive can be downloaded on any device and synchronized to the cloud to ensure data consistency. Let’s find all OneDrive site collections in SharePoint Online using PowerShell. This PowerShell script gets all OneDrive site collections in SharePoint Online by filtering the site template property.

$AdminSiteURL="https://crescent-admin.sharepoint.com"

#Get Credentials to connect to SharePoint Admin Center
$Cred = Get-Credential

#Connect to SharePoint Online Admin Center
Connect-SPOService -Url $AdminSiteURL -credential $Cred

#Get all Personal Site collections and export to a Text file
Get-SPOSite -Template "SPSPERS" -limit ALL -includepersonalsite $True | Select URL, Owner | Format-table -autosize | Out-string -width 8096 | Out-file C:\temp\OneDriveSites.txt 

Just change the $AdminSiteURL to your domain, supply the credentials for the SharePoint Online administrator, and run the script. This script produces the output on “C:\Temp\OneDriveSites.txt” file.

get all onedrive site collections using powershell

List All OneDrive for Business sites using PowerShell

You can also use -Filter You can also use -Filter switch in Get-SPOSite cmdlet as:

Get-SPOSite -IncludePersonalSite $true -Limit all -Filter "Url -like '-my.sharepoint.com/personal/'"

Get All OneDrive Sites using PnP PowerShell

We can retrieve the list of all OneDrive site collections using PnP PowerShell as:

#Config Variables
$TenantSiteURL =  "https://crescent-admin.sharepoint.com/"
  
#Connect to PnP Online
Connect-PnPOnline -Url $TenantSiteURL -Interactive

#Get All OneDrive Sites
Get-PnPTenantSite -IncludeOneDriveSites -Filter "Url -like '-my.sharepoint.com/personal/'"

Let’s export all OneDrive sites to a CSV file:

#Parameter
$Tenant =  "CrescentIntranet"
$CSVPath = "C:\Temp\OneDriveSites.csv"

#Connect to PnP Online
Connect-PnPOnline -Url "https://$tenant-admin.sharepoint.com" -Interactive

#Get OneDrive Site Details and export to CSV
Get-PnPTenantSite -IncludeOneDriveSites -Filter "Url -like 'https://$tenant-my.sharepoint.com/personal/'" |
    Select Title, URL, Owner, LastContentModifiedDate, StorageUsage | 
        Export-Csv -Path $CSVPath -NoTypeInformation

Get All OneDrive Sites using PowerShell – CSOM:

You can use PowerShell CSOM to get all OneDrive site collections as an alternative approach. This script fetches all SharePoint Online users using UserProfileService and checks if the user has a Personal site. Here is the script to list all OneDrive for business sites with PowerShell:

#Add references to SharePoint client assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.UserProfiles")

# Specify sharepoint online admin url 
$AdminSiteURL = "https://crescent-admin.sharepoint.com"
$MySiteURL="https://crescent-my.sharepoint.com"

#Specify the location where the list of OneDrive sites should be saved
$LogFile = "C:\Temp\OneDriveSites.txt"
 
#Setup Credentials to connect
$AdminCred= Get-Credential
$AdminCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($AdminCred.Username, $AdminCred.Password)
 
#Set User Profile Service path using SPO admin URL 
$ProxyAddr = "$AdminSiteURL/_vti_bin/UserProfileService.asmx?wsdl"
# Create a new webservice proxy to access UserProfileService
$UserProfileService= New-WebServiceProxy -Uri $ProxyAddr -UseDefaultCredential False
$UserProfileService.Credentials = $AdminCredentials
 
#Set authentication cookies
$StrAuthCookie = $AdminCredentials.GetAuthenticationCookie($AdminSiteURL)
$URI = New-Object System.Uri($AdminSiteURL)
$Container = New-Object System.Net.CookieContainer
$Container.SetCookies($URI, $StrAuthCookie)
$UserProfileService.CookieContainer = $Container
 
# Sets the first User profile, at index -1
$UserProfileResult = $UserProfileService.GetUserProfileByIndex(-1)
 
$NumProfiles = $UserProfileService.GetUserProfileCount()
$i = 1
 
# As long as the next User profile is NOT the one we started with (at -1)...
While ($UserProfileResult.NextValue -ne -1) 
{
    Write-Host "Checking profile $i of $NumProfiles" -foreground Yellow
 
    # Look for the Personal Space object in the User Profile and retrieve it  
    $Prop = $UserProfileResult.UserProfile | Where-Object { $_.Name -eq "PersonalSpace" } 
    $URL= $Prop.Values[0].Value
 
    # If "PersonalSpace" (which we've copied to $Url) exists, log it to our file...
    if ($URL) 
    {
        $SiteUrl = $MySiteURL+$URL
        # Write OneDrive site url to the console
        Write-Host $SiteUrl -foreground Green
        $SiteUrl | Out-File $LogFile -Append -Force
    }
 
    # And now we check the next profile the same way...
    $UserProfileResult = $UserProfileService.GetUserProfileByIndex($UserProfileResult.NextValue)
    $i++
}

Conclusion:

By following these simple steps, you can quickly get a list of all OneDrive sites in your organization using either the Microsoft 365 admin center or PowerShell. This can be useful for a variety of purposes, such as auditing, reporting, or management. The Microsoft 365 admin center provides a user-friendly interface for managing your OneDrive environment, while PowerShell provides greater automation capabilities. Regardless of which method you choose, the process is straightforward and easy to follow.

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

5 thoughts on “Find All OneDrive for Business Sites using PowerShell

  • I Wasted days trying different suggestions before finding your post! Thank you so much.

    Reply
  • Thank you for this script, it’s super useful.

    Reply
  • I get an empty text file after running script. I know we have OneDrive for Business where we have 100’s of OneDrives setup for users.

    Reply
  • Its really very helpful, Thank you so much!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *