Wednesday, March 23, 2016

OneDrive for Business usage report via Powershell


The solution below requires only SharePoint Online Management Shell :

1. Connect to Sharepoint Online:
Connect-SPOService -Url https://tenantname-admin.sharepoint.com

2. Get logins list you need to build report:

Ex. from existing Sharepoint Online tenant
$logins=(Get-SPOUser -Site https://tenantname-my.sharepoint.com).LoginName

or from external source Logins.csv
$logins = (import-csv logins.csv).Identity

where CSV-file content just like:
Identity
user1@domain.com
user2@domain.com
user3@domain.com
user4@domain.com
user5@domain.com

3. Build full usage report and export to CSV file:

foreach ($login in $logins) {
        if($Login.Contains('@')) {
        $login=$login.Replace('@','_');
        $login=$login.Replace('.','_');
        $login=$login.Replace('.','_');
        $login="https://tenantname-my.sharepoint.com/personal/"+$login;
        $Report = Get-SPOSite -Identity $login -ErrorAction SilentlyContinue
        $Report | Export-CSV ODFB-CSV-Report.csv -Append}  
        }

All pieces together in single PowerShell script:

# enter tenantname (ex. if appriver3651004263.onmicrosoft.com)
$tenant="appriver3651004263"

# Connect to Sharepoint Online:
Connect-SPOService -Url https://$($tenant)-admin.sharepoint.com

# Get logins list you need to build report: Ex. from existing Sharepoint Online tenant
$logins=(Get-SPOUser -Site https://$($tenant)-my.sharepoint.com).LoginName

# or from external source Logins.csv
# $logins = (import-csv logins.csv).Identity

foreach ($login in $logins) {
        if($Login.Contains('@')) {
        $login=$login.Replace('@','_');
        $login=$login.Replace('.','_');
        $login=$login.Replace('.','_');
        $login="https://$($tenant)-my.sharepoint.com/personal/"+$login;
        $Report = Get-SPOSite -Identity $login -ErrorAction SilentlyContinue
        $Report | Export-CSV ODFB-CSV-Report.csv -Append -NoTypeInformation}  
        }



Reference:
Create OneDrive for Business usage report using Powershell