Find and Export all OneDrive for Business users using Powershell

In this post, I am going to explain how to find and export list of Office 365 users with OneDrive for Business site provisioned. We can easily extract OneDrive sites using SharePoint management powershell cmdlet Get-SPOSite.

Before proceed, Install and Connect SharePoint Online PowerShell Module. Run the below commands after replacing your SPO Admin site url to connect SPO Online service. You can refer this post to know more about how to get SPO Admin Site Url.

$AdminSiteURL="https://<your tenant name>-admin.sharepoint.com"
#Connect to SharePoint Online Admin Site
Connect-SPOService -Url $AdminSiteURL

When you simply run the cmdlet Get-SPOSite, it will list all sites in your SPO tenant but it will not list OneDrive users personal sites by default. So to retrieve personal sites along with normal spo sites , we have to run the Get-SPOSite cmdlet with the parameter –IncludePersonalSite as True.

Get-SPOSite -IncludePersonalSite $true -Limit All

The above command returns personal sites and it also includes normal sites, so we have to filter sites by url to get only personal sites. The below powershell script lists all personal sites in office 365 and its owner detail.

#Get OneDrive for Business Sites alone
Get-SPOSite -IncludePersonalSite $true -Limit All -Filter "Url -like '-my.sharepoint.com/personal/'"

Export all OneDrive for Business users to CSV

The below powershell script get and export all personal sites and site owner name details to csv file.

$Result=@()
#Get all office 365 personal sites
$oneDriveSites = Get-SPOSite -IncludePersonalSite $true -Limit All -Filter "Url -like '-my.sharepoint.com/personal/'"
$oneDriveSites | ForEach-Object {
$site = $_
$Result += New-Object PSObject -property @{ 
UserName = $site.Owner
OneDriveSiteUrl = $site.URL
}
}
$Result | Select UserName, OneDriveSiteUrl |
Export-CSV "C:\OneDrive-for-Business-Users.csv" -NoTypeInformation -Encoding UTF8

CSV output of OneDrive for Business (ODFB) users: