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…
| 1 | Get-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.
| 12 | Connect-MsolService -Credential $credGet-MsolAccountSku |
We should get a list back that looks something like this:
| 123456 | AccountSkuId 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
| 123456789101112 | cat: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.
| 1234567891011 | foreach($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.
| 1234567891011121314151617181920 | function 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.