Pull OneDrive for Business Usage Using PowerShell | Quisitive

UPDATE: Check out my video on how to Drive Adoption using techniques from Sun Tzu

Recently I was asked how to find the usage of all of the users in our OneDrive for Business in our Office 365 tenant.  Its relatively easy to do this using PowerShell for all of your normal site collections.  For that you can use the Get-SPOSite function like so:

123456$username = “[email protected]”$password = “password”$cred = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $userName, $(convertto-securestring $Password -asplaintext -force)Connect-SPOService -Url “https://tenant-admin.sharepoint.com” -Credential $credGet-SPOSite -Limit All -Detailed | select url, storageusagecurrent, Owner

The problem is that this won’t give you the sizes of any OneDrive for Business site collections.  Those you have to explicitly pull using the Get-SPOSite like this…

1Get-SPOSite “https://tenant-my.sharepoint.com/personal/user_name_tenant_onmicrosoft_com” -Detailed | select url, storageusagecurrent, Owner

That get’s us one user’s OneDrive, but how do we get them all?  First off, we need to get the list of users that are licensed for OneDrive.  This is a bit more complex.  So, let’s go in steps.  First we need to connect to the MS Online Service and get the list of MS Online SKUs.

12Connect-MsolService -Credential $credGet-MsolAccountSku

We should get a list back that looks something like this:

123456AccountSkuId                 ActiveUnits WarningUnits ConsumedUnits————                 ———– ———— ————-tenant:ENTERPRISEPACK           496         0            182          tenant:WACONEDRIVESTANDARD      237         0            165          tenant:INTUNE_A                 100         0            9            tenant:RIGHTSMANAGEMENT_ADHOC   50000       0            4

You might be able to figure that WACONEDRIVESTANDARD and ENTERPRISEPACK are the right ones to use, but you can always check it by looking at the provisioning status of the SKU.  To do this, store the results of Get-MsolAccountSku in a variable and then look at the ProvisioningStatus field for each one.  You could do a foreach, but its an array so we can just look at the ones that we want, in this case the ENTERPRISEPACK

123$acctSku = Get-MsolAccountSku$acctSku[0].AccountSkuId$acctSku[0].ServiceStatus

And you will get back what is included in the license

123456789101112cat:ENTERPRISEPACKServicePlan           ProvisioningStatus———–           ——————INTUNE_O365           PendingActivation YAMMER_ENTERPRISE     Success           RMS_S_ENTERPRISE      Success           OFFICESUBSCRIPTION    Success           MCOSTANDARD           Success           SHAREPOINTWAC         Success           SHAREPOINTENTERPRISE  Success           EXCHANGE_S_ENTERPRISE Success

Since we have SharePoint Enterprise that includes OneDrive for Business so we want this one and the WACONEDRIVESTANDARD.  To do this, I go get all of the users in the tenant, check the licensed ones (getting rid of anyone who is an external user) and then check their License Status to see if they have an E3 or OneDrive License.

1234567891011foreach($usr in $(Get-MsolUser -All )){    if ($usr.IsLicensed -eq $true)    {        foreach($lic in $usr.licenses)        {            if ($lic.AccountSkuID -eq “tenant:ENTERPRISEPACK”) {Write-Host “$($usr.DisplayName), E3”}            elseif ($lic.AccountSkuID -eq “tenant:WACONEDRIVESTANDARD”) {Write-Host “$($usr.DisplayName), OneDrive” }            }    }}

BTW: I tried to use the Where-Object on the Get-MsolUser but it never returned anything.  I suspect that is significantly more efficient.  From this, we get a list of users with their license type, but we still don’t know the size of their OneDrive for Business.  Last step is to pull each users OneDrive site collection using Get-SPOSite.  To do that we have to massage the user’s name into the correct format.  I grabbed the UserPrincipalName and replace the “.” and “@” with “_” to match the pattern and my code now looks like this.

1234567891011121314151617181920function GetODUsage($url){    $sc = Get-SPOSite $url -Detailed -ErrorAction SilentlyContinue | select url, storageusagecurrent, Owner    $usage = $sc.StorageUsageCurrent / 1024    return “$($sc.Owner), $($usage), $($url)”}foreach($usr in $(Get-MsolUser -All )){    if ($usr.IsLicensed -eq $true)    {        $upn = $usr.UserPrincipalName.Replace(“.”,”_”)        $od4bSC = “https://tenant-my.sharepoint.com/personal/$($upn.Replace(“@”,”_”))”        $od4bSC        foreach($lic in $usr.licenses)        {            if ($lic.AccountSkuID -eq “tenant:ENTERPRISEPACK”) {Write-Host “$(GetODUsage($od4bSC)), E3”}            elseif ($lic.AccountSkuID -eq “tenant:WACONEDRIVESTANDARD”) {Write-Host “$(GetODUsage($od4bSC)), OneDrive” }            }    }}

The function get’s passed the URL for the users OneDrive for Business and returns the URL, usage in GB, Owner and then we tag on the license type.  The results look like this (I built it with commas separating it so that I could pull it into Excel and manipulate it easily):

12345[email protected], 0.0009765625, https://tenant-my.sharepoint.com/personal/user_one_tenant_onmicrosoft_com, [email protected], 0.4921875, https://tenant-my.sharepoint.com/personal/user_two_tenant_onmicrosoft_com, [email protected], 4.21875, https://tenant-my.sharepoint.com/personal/user_three_tenant_onmicrosoft_com, [email protected], 0.0009765625, https://tenant-my.sharepoint.com/personal/user_four_tenant_onmicrosoft_com, [email protected], 0.5869140625, https://tenant-my.sharepoint.com/personal/user_five_tenant_onmicrosoft_com, E3

So, with a little dedication and some Powershell mojo we where able to get the size of all of the OneDrives in our tenant.