Caution: PowerShell Nerd Alert

Yesterday morning I was asked to help with a SharePoint Online task that required identifying all personal MySites and modifying an attribute. This seems like a pretty straight-forward task for PowerShell, but it turns out there are some limitations on how SharePoint is accessible.

A check of my usual sources (Technet, Google, etc) informed me that what I was being asked to do wasn't directly possible. You can certainly make changes to individual MySites from PowerShell, but you cannot query for them without also knowing their URL's, which is ridiculous because how are you supposed to know the URL of something if you don't even know it exists?

Except it is possible.

The secret is to not query for the MySites, but rather for the users' email addresses, then to take that output and convert it into MySite format and append it to your tenant's URL configuration, and then query for the existence of that object.

It even turns out there's a convenient text conversion operator that's almost custom-tailored for the task. $_.windowsemailaddress -replace "\W","_" replaces all special characters in an email address with underscores.

And because running this without error control can result in a bajillion errors on users who haven't yet initialized their MySites, adding the try{} catch{} statement results in a clean output of your subset of users who have initialized their sites. You could modify the catch {} statement to generate a list of non-initialized users, if you felt so inclined.

Of course you could modify the whole thing to query only for licensed MSOLuser objects, too. That a cleaner implementation, but my goal was to reduce the number of installed components necessary on the system running the command.

The script assumes your userID is @*.onmicrosoft.com.

Happy PowerShelling!

#   This script queries SharePoint Online for a list of personal MySites and their external sharing status.

#   In order for it to run properly, the machine on which it runs will need the following software installed:

#

#      1.  Microsoft Online Sign-In Assistant (http://www.microsoft.com/en-us/download/details.aspx?id=28177)

#      2.  SharePoint Online Management Shell (http://www.microsoft.com/en-us/download/details.aspx?id=35588)

#

#   Created 10/07/2015 by Adrian Amos, Synergy Technical, LLC

import-module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking

#Get Admin Credential

$cred = get-credential

#Extract Tenant name from credential

$username = $cred.username

$tenant_full = $username | sls '(?<=@)(.*)' | select -expa matches | select -expa value | % {$_.trim()}

$tenant = $tenant_full.substring(0, $tenant_full.length - 16)

#Rename Powershell window to tenant name

$Host.UI.RawUI.WindowTitle = $tenant

#Connect to Exchange Online

write-host "Connecting to Exchange Online..."

$exol_session = new-pssession -configurationname Microsoft.Exchange -connectionuri "https://outlook.office365.com/powershell-liveid/" -authentication basic -allowredirection -credential $cred

import-pssession $exol_session -disablenamechecking

write-host ""

#Connect to SharePoint Online

write-host "Connecting to SharePoint Online..."

$sharepoint_url = "https://" + $tenant + "-admin.sharepoint.com"

Connect-SPOService -url $sharepoint_url -credential $cred

#Convert Email addresses for mailbox-enabled objects to SharePoint MySite format

write-host "Querying users..."

$email_addresses = @(get-mailbox -resultsize 2000 | select windowsemailaddress)

write-host "Converting email addresses to MySite subfolder names..."

$scrub = @(foreach ($email_address in $email_addresses) {$email_address.windowsemailaddress -replace "\W","_"})

write-host "Generating list of SharePoint MySites to query..."

$sp_mysite_list = @(foreach ($entry in $scrub) {$("https://" + $tenant + "-my.sharepoint.com/personal/" + $entry)})

write-host "Querying MySites..."

foreach ($sp_mysite in $sp_mysite_list) {try {get-sposite $sp_mysite | select url,sharingcapability} catch {}}