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.
The solution below requires only SharePoint Online Management Shell.
Do not hesitate to send the feedback using Feedback button if you suspect that the numbers may be inaccurate.
For "regular" site collections it is possible to retrieve storage data using
Get-SPOSite | fl
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 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
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/
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.
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.
Connect
-SPOService -Url https://tenant-admin.sharepoint.com
$logins=(Get-SPOUser -Site https://tenant-my.sharepoint.com).LoginName
foreach ($login
in
$logins){if($Login.
Contains
(
'@'
)) { $login=$login.
Replace
,
'_'
); $lo
gin=$login.
'.'
); $login=$login.
); $login=
"https://tenant-my.sharepoint.com/personal/"
+$login;} }
-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
((get-spouser -Site https://tenant-my.sharepoint.com).LoginName)){if($Login.
+$login; Get-SPOSite -Identity $login | export-csv c:\forPro3.csv -Append} }
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:
+$login; Get-SPOSite -Identity $login |
url, *storage* | 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="https://tenant-my.sharepoint.com/personal/"+$login;
$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} }
); $login=$login.Replace('.','_'); $login="https://tenant-my.sharepoint.com/personal/"+$login; Get-SPOSite -Identity $login | select url, *storage* |export-csv c:\forPro3.csv -Append} }
url, *storage* |export-csv c:\forPro3.csv -Append} }
Before you run the above cmdlets you need to install SharePoint Online Management Shell and run Connect-SPOService cmdlet.
I got this error when I run this shell:
Get-SPOSite : Cannot bind parameter 'Identity'. Cannot convert value
"spo-grid-all-users/02f3d72f-b920-4405-be03-5607023b3f9a" to type
"Microsoft.Online.SharePoint.PowerShell.SpoSitePipeBind". Error:
"SpoSitePipeBind Url"
At line:1 char:13
+ Get-SPOSite $login | Export-Csv -Path C:\sites.csv
Could someone help me?
I have not found the mistake :(
Best Regards Luis Reyes
@Luis Reyes Gaspar When you check the user logins, using e.g. (get-spouser -Site tenant-my.sharepoint.com).LoginName what names do you have? user@domain.com or only spo-grid-all-users/02f3d72f-b920-4405-be03-5607023b3f9a? Is the report created for all the users (apart from EVERYONE/spo-grid-all-users) or not at all?
Thanks!
Hello - when I go to the last step to create the CSV, I get the following error:
Get-SPOSite : Cannot get site mysite-my.sharepoint.com/.../user_domain_com.
+ Get-SPOSite -Identity $login | select url, *storage* | export-csv c:\ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-SPOSite], ServerException
+ FullyQualifiedErrorId : Microsoft.SharePoint.Client.ServerException,Microsoft.Online.SharePoint.PowerShell.GetSite
In the same session, I can use Get-SPOSite -Identity mysite-my.sharepoint.com/.../my-user-name_domain_com | select url, *storage* | export-csv c:\OneDrive-Usage.csv -Append and get the output for my own OneDrive to show up, but if I do the same thing using any other user's URL, it spits out the same error. I went into the Sharepoint Admin Console and made sure my account is a site manager for the mysite-my.sharepoint.com site. Any ideas?
@Ayoung-Ounce
Please verify if you have any rights to the users' personal sites. If you are not currently an Administrator of the user's personal site, you can gain access using:
Set-SPOUser -Site $UserPersonalSiteUrl -LoginName $YourAdminUser -IsSiteCollectionAdmin $true