Monday, 30 January 2017

OneDrive for Business Storage Report using Powershell

You may need to see how much space your users are using in OneDrive for Business storage and need to ask them to free up space if they are getting close to your storage limit. We can retrieve the list of users with OneDrive feature provisioned by using SharePoint Online UserProfileService with Powershell.

Note: The below script use CSOM (Client Object Model) code to retrieve all OneDrive sites. If you need solution without CSOM, you can refer this post: Export OneDrive for Business Users Storage Report using Powershell

Steps to Export OneDrive for Business Size Report:

  • Fetch all SharePoint Online users using UserProfileService.
  • Find if OneDrive for Business is provisioned or not.
  • Find Current Size and Storage Limit details.
  • Export Size and Storage Quota details to csv file.
Note: Replace the variable <your tenant name> with your Office 365 tenant name in all the occurrences and provide your own admin credentials.
# Specify your organization admin central url 
$AdminURI = "https://<your tenant name>"

# Specify the User account for an Office 365 global admin in your organization
$AdminAccount = "admin@<your tenant name>"
$AdminPass = "admin_password"

$loadInfo1 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
$loadInfo2 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
$loadInfo3 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.UserProfiles")

$sstr = ConvertTo-SecureString -string $AdminPass -AsPlainText -Force
$AdminPass = ""
$creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($AdminAccount, $sstr)
$UserCredential = New-Object System.Management.Automation.PSCredential -argumentlist $AdminAccount, $sstr

# Add the path of the User Profile Service to the SPO admin URL, then create a new webservice proxy to access it
$proxyaddr = "$AdminURI/_vti_bin/UserProfileService.asmx?wsdl"
$UserProfileService= New-WebServiceProxy -Uri $proxyaddr -UseDefaultCredential False
$UserProfileService.Credentials = $creds

# Set variables for authentication cookies
$strAuthCookie = $creds.GetAuthenticationCookie($AdminURI)
$uri = New-Object System.Uri($AdminURI)
$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)
Write-Host "Starting- This could take a while."
$NumProfiles = $UserProfileService.GetUserProfileCount()
$i = 1

Connect-SPOService -Url $AdminURI -Credential $UserCredential

# 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"
# Look for the Personal Space object in the User Profile and retrieve it
# (PersonalSpace is the name of the path to a user's OneDrive for Business site. 
# Users who have not yet created a  OneDrive for Business site might not have this property)
$Prop = $UserProfileResult.UserProfile | Where-Object { $_.Name -eq "PersonalSpace" } 
$Url= $Prop.Values[0].Value
# If "PersonalSpace" exists, then OneDrive Profile provisioned for the user...
if ($Url) {
$siteUrl = "https://<your tenant name>"+ $Url.Substring(0,$Url.Length-1)

# Find size and storage limit
$temp = Get-SPOSite $siteurl -Detailed
$Result += New-Object PSObject -property @{ 
UserName = $temp.Title
UserPrincipalName = $temp.Owner
Size_inMB = $temp.StorageUsageCurrent
StorageQuota_inGB = $temp.StorageQuota/1024
WarningSize_inGB =  $temp.StorageQuotaWarningLevel/1024
# And now we check the next profile the same way...
$UserProfileResult = $UserProfileService.GetUserProfileByIndex($UserProfileResult.NextValue)
$Result | FT
You can also export the output into csv file:
$Result | Export-CSV "C:\\OneDrive-for-Business-Size-Report.csv" -NoTypeInformation -Encoding UTF8



  1. My global admin account uses multi-factor authentication. How to deal with it? Thanks.

    1. Hi Ming, sorry some part of script using CSOM code so I think currently you can't run this script with MFA or SSO (Single-sign-on) account . But I will try to prepare different script for this need.