Web Solutions/Intranets

Pull OneDrive for Business Usage Using PowerShell

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:

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…

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.

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

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

And you will get back what is included in the license

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.

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.

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):

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


  1. Teddy Gonzalez February 25, 2016
  2. Chet Chudasama April 7, 2016
    • David Broussard June 27, 2016
  3. Daniel August 2, 2016
    • Alex Nicastro September 19, 2016
  4. Mike April 7, 2017
    • David Broussard April 26, 2017
  5. STGdb April 26, 2017
    • David Broussard April 26, 2017
  6. arturo May 4, 2017
  7. Hussaca November 19, 2017

Leave a Reply