Using Windows PowerShell to Manage SharePoint Online
Summary: Use Windows PowerShell to Manage Office 365 using Windows PowerShell cmdlets, scripts, and batch processes.
SharePoint Online can be managed by using the SharePoint Online cmdlets. (See? Not all software names are cryptic and obscure.) In its current incarnation, the SharePoint Online implementation of Windows PowerShell is somewhat limited: there are only 30 cmdlets available to Office 365 administrators, and, at the moment, you can’t use Windows PowerShell to manage things like SkyDrive Pro, SharePoint Online searches, the SharePoint Online term store, user profiles, and so on. Does that mean there’s no reason to use Windows PowerShell to manage SharePoint Online? Let’s not be too hasty about that. As it turns out, there are several key portions of SharePoint Online that not only can be managed by using Windows PowerShell, but can be managed exceedingly well by using Windows PowerShell.
Using Windows PowerShell to manage SharePoint Online
In this topic we cover the following topics:
Working with SharePoint Online Sites
Working with SharePoint Online Users
Working with SharePoint Online Site Groups
A Quick Note Regarding the ForEach-Object Cmdlet
For more information on using Windows PowerShell to manage SharePoint Online, you might want to take a look at the SharePoint Online cmdlet reference, a set of help topics available at here.
And don’t forget these two articles, chock-full of useful Windows PowerShell commands for SharePoint Online:
Working with SharePoint Online sites
Could we give you an example of a SharePoint Online related area that can be managed by using Windows PowerShell? Of course we can. One place where Windows PowerShell really shines is helping you identify a subset of your SharePoint Online sites. In larger organizations, it’s not uncommon to have scores of sites, maybe even hundreds or thousands of sites. Is there anything wrong with that? Of course not. But suppose you have several hundred sites and you want to know how many of those sites are wikis; in other words, you want to know how many of your sites are built using the Enterprise Wiki template (a template that, officially, has the name ENTERWIKI#0.) Can you find this information using the SharePoint Online Admin center? We won’t say that you can’t find this information that way, but it won’t be easy. And it definitely won’t be as easy as running this one Windows PowerShell command:
Get-SPOSite | Where-Object {$_.Template -eq "ENTERWIKI#0"}
In other words, we use the Get-SPOSite cmdlet to return information about all our sites, then use the Where-Object cmdlet to pick out only those sites where the Template property is equal to ENTERWIKI#0.
Note
How did we know that the Enterprise Wiki template is officially named ENTERWIKI#0? That’s easy: we just ran this command:
Get-SPOWebTemplate
Or maybe you’d like a list of all the sites that have a storage quota either greater than 1000 megabytes or less than 250 megabytes. Can you do that quickly and easily using Windows PowerShell? Of course you can:
Get-SPOSite | Where-Object {$_.StorageQuota -gt 1000 -or $_.StorageQuota -lt 250}
That’s a slightly more complicated query that some of the ones we’ve seen so far, but once you know the lingo you should have no problem understanding how it works. In this example, we’re looking for sites that meet 1 of 2 criteria. Either the site: 1) has a StorageQuota greater than (-gt) 1000 megabytes; or (-or), 2) has a StorageQuota less than (-lt) 250 megabytes. Suppose we have 4 sites:
Site | StorageQuota |
---|---|
Site A |
180 |
Site B |
700 |
Site C |
300 |
Site D |
1500 |
Using this sample date, Site A will be returned because it has a StorageQuota less than 250. Site D will also be returned, because it has a StorageQuota greater than 1000. Sites B and C don’t meet either criteria, so they will not be returned.
Here’s another one, just for fun. Suppose you’d like a list of all the sites that are not owned by administrator; that is, all the sites where the Owner is equal to someone other than, in our example, admin@litwareinc.onmicrosoft.com. That command is so easy to write it’s almost embarrassing:
Get-SPOSite | Where-Object {$_.Owner -ne "admin@litwareinc.onmicrosoft.com"}
But just because it’s easy doesn’t mean it’s not useful, right?
Working with SharePoint Online users
Windows PowerShell is an extremely useful tool for working with SharePoint Online site users. For one thing, you can use Windows PowerShell and the Get-SPOUser cmdlet to return a list of all the users who have been granted access to a site. That’s easy:
Get-SPOUser -Site "https://litwareinc.sharepoint.com/sites/finance"
All you have to do is call Get-SPOUser followed by the URL of the site you’re interested in.
Or, if you want only the users (and not the groups) that have access to a site you can use this command:
Get-SPOUser -Site "https://litwareinc.sharepoint.com/sites/finance" | Where-Object {$_.IsGroup -eq $False}
That’s also easy: we just ask for the site users where the IsGroup property is set to False. (In Windows PowerShell, you use $True and $False when writing commands rather than plain old True and False.)
It’s just as easy to return only the users who are site administrators; that is, users where the IsSiteAdmin property is True:
Get-SPOUser -Site "https://litwareinc.sharepoint.com/sites/finance" | Where-Object {$_.IsSiteAdmin -eq $True}
But that’s for one site only. The power in Windows PowerShell (so to speak) often comes when you need to work with multiple objects; you know, like multiple sites. For example, it’s nice to have a list of all the users that have access to a given site. But what about the converse: what about a list of all the sites that a single user has access to? Can you do that using Windows PowerShell? Of course you can. Here’s a simple little script (admittedly, simple for those who have some Windows PowerShell experience) that returns the names of all the sites that a particular user (in this example, Ken Myer) has access to:
$x = (Get-SPOSite).Url
foreach ($y in $x)
{
$z = $Null
$z = Get-SpoUser -Site $y | Where-Object {$_.DisplayName -eq "Ken Myer"}
if ($z -ne $Null) {$y}
}
Note
How do you use a script in Windows PowerShell? Like this: copy the preceding code, paste it into Notepad (or another text editor), and then save the file using a .ps1 file extension (for example, C:\Scripts\SitesAUserCanAccess.ps1). To run the script, use Windows PowerShell to connect to SharePoint Online, then, at the Windows PowerShell command prompt, simply type the path to the .ps1 file and press ENTER:
C:\Scripts\SitesAUserCanAccess.ps1
Or what about a list of all your sites and the administrators for each of those sites? That information can be returned using a single command:
Get-SPOSite | ForEach-Object {Write-Host $_.Url; Get-SPOUser -Site $_.Url | Where-Object {$_.IsSiteAdmin -eq $True}}
You should get back something similar to this:
https://litwareinc.sharepoint.com/sites/communities
MOD Administrator admin@litwareinc.com {Communities Members, Comm...}
Sara Davis sarad@litwareinc.com {Communities Members, Communities...}
https://litwareinc.sharepoint.com/sites/finance
MOD Administrator admin@litwareinc.com {Excel Services Viewers, Hi...}
https://litwareinc.sharepoint.com/sites/hr
MOD Administrator admin@litwareinc.com {Contoso Beta Members, C...}
Granted that’s not the prettiest output in the world, but, needless to say, looks aren’t everything. And, as you gain more experience and get a little more adept with Windows PowerShell, you’ll learn how to customize that output in all sorts of cool ways.
Best of all, Windows PowerShell isn’t just a way to retrieve things: it’s also a way to configure things. You say you have several hundred sites and you need to make Ken Myer an administrator on each one? All you had to do was ask:
Get-SPOSite | ForEach-Object {Set-SPOUser -Site $_.Url -LoginName "kenmyer@litwareinc.com" -IsSiteCollectionAdmin $True}
Nice, huh?
Working with SharePoint Online site groups
Don’t worry: we didn’t forget about site groups. In fact, Windows PowerShell is a great way to manage your SharePoint site groups. The SharePoint Online Admin center has some easy-to-use methods for managing site groups, but getting to those methods can be a little cumbersome. For example, suppose you want to look at the groups, and the group members, for the site https://litwareinc.com/sites/finance. Here’s what you have to do to get that:
From the SharePoint Online Admin center, on the site collections tab, click the name of the site.
In the site collection properties dialog box, click the link that opens https://litwareinc.com/sites/finance.
On the site page, click the Settings icon (located in the upper right-hand corner of the page) and then click Site Settings:
On the Site Settings page, click Sites and permissions.
And then repeat the process for the next site you want to look at.
So is there another way to get a list of all the groups, and their users, from a given site? Well, there’s at least one other way:
$x = Get-SPOSiteGroup -Site "https://litwareinc.com/sites/finance"
foreach ($y in $x)
{
Write-Host $y.Title -ForegroundColor "Yellow"
Get-SPOSiteGroup -Site "https://litwareinc.com/sites/finance" -Group $y.Title | Select-Object -ExpandProperty Users
Write-Host
}
Admittedly, the preceding script is a tiny bit more complicated than most of the commands we’ve shown you up till now. And it is a tiny bit of a hassle as well: after all, you need to copy the code, paste into Notepad (or some other text editor), save the file using a .ps1 file extension (for example, C:\Scripts\SiteGroupsAndUsers.ps1) and then run the script from within Windows PowerShell. Although, like we said, it’s a tiny bit of a hassle; after all, running the script merely involves typing the full path to the .ps1 file:
C:\Scripts\SiteGroupsAndUsers.ps1
So yes, there’s a little bit of effort involved in all that. But look what we get back in return for that minimal amount of work:
Those are all the groups that have been created for the site https://litwareinc.com/sites/finance, as well as all the users assigned to those groups. (And, just to show off a little, we displayed the group names in yellow, to help you keep the groups, and their membership lists, separate.)
And if you think that was something, wait until you see this script:
$x = Get-SPOSite
foreach ($y in $x)
{
Write-Host $y.Url -ForegroundColor "Yellow"
$z = Get-SPOSiteGroup -Site $y.Url
foreach ($a in $z)
{
$b = Get-SPOSiteGroup -Site $y.Url -Group $a.Title
Write-Host $b.Title -ForegroundColor "Cyan"
$b | Select-Object -ExpandProperty Users
Write-Host
}
}
That script lists all the groups, and all the group memberships, for all of your SharePoint Online sites, every single one of them. Give it a try and see for yourself.
Now do you understand why you might want to use Windows PowerShell to manage SharePoint Online?
A quick note regarding the ForEach-Object cmdlet
In the Working with SharePoint Online users section, you might have noticed that we piped site information to the ForEach-Object cmdlet:
Get-SPOSite | ForEach-Object {Set-SPOUser -Site $_.Url -LoginName "kenmyer@litwareinc.com" -IsSiteCollectionAdmin $True}
That brings up an interesting question: why did we do that? After all, when working with the Azure Active Directory cmdlets we piped information directly from the Get-MsolUser cmdlet to the Set-MsolUser cmdlet:
Get-MsolUser | Set-MsolUser -UsageLocation "FR"
So then why didn’t we do something like this with our SharePoint Online command:
Get-SPOSite | Set-SPOUser -LoginName "kenmyer@litwareinc.com" -IsSiteCollectionAdmin $True
The main reason we didn’t do that is because it won’t work. With the Set-SPOUser cmdlet you must explicitly include the Site parameter followed by the name of the site. For example:
Set-SPOUser -Site "https://litwareinc.sharepoint.com/sites/communities" -LoginName "kenmyer@litwareinc.com" -IsSiteCollectionAdmin $True
That means that, if we try to pipe information directly to Set-SPOUser, the cmdlet won’t take all of our sites and make Ken Myer an administrator on each one. Instead, it will prompt us to enter the site URL:
Get-SPOSite | Set-SPOUser -LoginName "kenmyer@litwareinc.com" -IsSiteCollectionAdmin $True
cmdlet Set-SPOUser at command pipeline position 2
Supply values for the following parameters:
Site:
That means that we need to pipe site information to the ForEach-Object cmdlet instead. ForEach-Object takes each item passed to it (each site) and then does whatever we tell it to do to that item. In this case, we’re telling it to use the Set-SPOUser cmdlet to make Ken Myer an administrator on that site:
Set-SPOUser -Site $_.Url -LoginName "kenmyer@litwareinc.com" -IsSiteCollectionAdmin $True
The syntax $_.Url represents the URL of the site. The $_. Indicates that we’re using information that was piped to the ForEach-Object cmdlet by another cmdlet.
Confused? Here’s a quick example. Suppose we have three sites with the following URLs:
When we pipe that information to ForEach-Object, the cmdlet takes the first item in the collection (site1) and runs the Set-SPOUser cmdlet using the URL for that site. In other words, it runs this command:
Set-SPOUser -Site "https://litwareinc.sharepoint.com/sites/site1" -LoginName "kenmyer@litwareinc.com" -IsSiteCollectionAdmin $True
When that’s done, it then takes the second site (site2) and runs Set-SPOUser against that site:
Set-SPOUser –Site "https://litwareinc.sharepoint.com/sites/site1" –LoginName "kenmyer@litwareinc.com" –IsSiteCollectionAdmin $True
As you can see, now it’s using the URL of the second site in the collection. Eventually ForEach-Object will have run through all the sites in the collection, and made Ken Myer an administrator on each one.
For more information, take a look at this article. Needless to say, ForEach-Object is actually a handy little cmdlet to know about. For example, sometimes when you try to pipe information from one cmdlet to another you get an error message like this:
The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.
That happens because not all cmdlets accepted pipelined input. How do you get around that issue? You got it: use ForEach-Object.