Share via

OneDrive for Business: usage report using PowerShell

  • Article

Introduction

OneDrive for Business with its 1TB storage offers multiple possibilities for yourself and your users. It also offers limited control over the personal sites and their usage.

One of the possibilities to retrieve a report on OneDrive Usage is the out-of-the-box option available under Office 365 Admin Center.

Prerequisites

The solution below requires only SharePoint Online Management  Shell.

Admin center

Total storage used

  

Number of deployed ODBs

Do not hesitate to send the feedback using Feedback button if you suspect that the numbers may be inaccurate. 

Get-SPOSite

For "regular" site collections it is possible to retrieve storage data using 

Get-SPOSite | fl

** ** or 

Get-SPOSite | select url, *storage*

But the personal URLs are not listed here.  They can be retrieved one-by-one using:

Get-SPO -Identity "https://tenant-my.sharepoint.com/personal/user_domain_com"

 

Personal URL

Personal URLs follow a certain pattern that allows us to create them based on the user's userprincipalname:

upn: ala.makota@arletka.cloudns.org

url:  https://tenant-my.sharepoint.com/personal/ala_makota_arletka_cloudns_org

upn: uss1@arletka2.cloudns.org

url: https://tenant-my.sharepoint.com/personal/uss1_arletka2_cloudns_org

This does not include all the "untypical" behaviour such as when you rename all your users and the personal URLs fail to update or when you recreate the user and receive URL with "1" at the end e.g.  https://tenant-my.sharepoint.com/personal/uss1_arletka2_cloudns_org1/

Retrieve users

There are multiple ways to retrieve your users' upns:

Get-Msoluser

Get-Mailbox

Hell, you may even have a ready list saved in a .csv file. In that case, do not hesitate to use it and import-csv the ready file. We don't have such a file, so we need a way to find the users.

Get-Msoluser will certainly retrieve you all the users, but that's exactly the problem - all the users. Licensed, unlicensed, groups and site mailboxes.

Get-Mailbox may have nothing to do with my users if none of my SharePoint users has Exchange Online license.

Get-SPOUser -Site https://tenant-my.sharepoint.com   is a simple and efficient way of retrieving all users with created personal sites and will do for our purposes.

Modify the URLs

There are better ways to retrieve personal URLs than this. But they involve CSOM and SharePoint Online Development Tools and the goal of this solution was to keep things simple and to a minimum.

1. Connect

Connect-SPOService -Url https://tenant-admin.sharepoint.com

2. Retrieve the logins:

$logins=(Get-SPOUser -Site https://tenant-my.sharepoint.com).LoginName

3. Modify each of the logins into URLs:

foreach ($login in $logins){if($Login.Contains('@')) { $login=$login.Replace('@','_'); $lo
gin=$login.Replace('.','_'); $login=$login.Replace('.','_'); $login="https://tenant-my.sharepoint.com/personal/"+$login;}  }

4. Retrieve the site collection information.

-Append parameter is used here as we will be appending the site collection information user by user

Get-SPOSite -Identity $login | export-csv c:\forPro3.csv -Append

5. Compiled into one cmdlet:

foreach ($login in ((get-spouser -Site https://tenant-my.sharepoint.com).LoginName)){if($Login.Contains('@')) { $login=$login.Replace('@','_'); $login=$login.Replace('.','_'); $login=$login.Replace('.','_'); $login="https://tenant-my.sharepoint.com/personal/"+$login; Get-SPOSite -Identity $login | export-csv c:\forPro3.csv -Append}  }

Modify the report 

You can select what information you want the report to include using select cmdlet. In the example below, we retrieve URLs and all storage-related attributes:

foreach ($login in ((get-spouser -Site https://tenant-my.sharepoint.com).LoginName)){if($Login.Contains('@')) { $login=$login.Replace('@','_'); $login=$login.Replace('.','_'); $login=$login.Replace('.','_'); $login="https://tenant-my.sharepoint.com/personal/"+$login; Get-SPOSite -Identity $login | select url, *storage* | export-csv c:\forPro3.csv -Append}  }

End effect

foreach ($login in ((get-spouser -Site https://tenant-my.sharepoint.com).LoginName))

{

    if($Login.Contains('@'))

    {

        $login=$login.Replace('@','_');

        $login=$login.Replace('.','_');

        $login=$login.Replace('.','_');

        $login="https://tenant-my.sharepoint.com/personal/"+$login;

        Get-SPOSite -Identity

        $login | export-csv c:\forPro3.csv -Append

    } 

}

 

foreach ($login ``in ((get-spouser -Site https://tenant-my.sharepoint.com).LoginName)){if($Login.``Contains``(``'@'``)) { $login=$login.``Replace``(``'@'``,``'_'``); $login=$login.``Replace``(``'.'``,``'_'``); $login=$login.``Replace``(``'.'``,``'_'``); $login=``"https://tenant-my.sharepoint.com/personal/"``+$login; Get-SPOSite -Identity $login | ``select`` ``url, *storage* |export-csv c:\forPro3.csv -Append}  }``

       

Requirements

Before you run the above cmdlets you need to install SharePoint Online Management Shell and run Connect-SPOService cmdlet.

Other Languages