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:

• Creating sites and adding users using Windows PowerShell

• Use Windows PowerShell to manage users and groups

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.

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:

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}
    }

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:

1. From the SharePoint Online Admin center, on the site collections tab, click the name of the site.

2. In the site collection properties dialog box, click the link that opens https://litwareinc.com/sites/finance.

3. On the site page, click the Settings icon (located in the upper right-hand corner of the page) and then click Site Settings:

   

4. 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:

• https://litwareinc.sharepoint.com/sites/site1

• https://litwareinc.sharepoint.com/sites/site2

• https://litwareinc.sharepoint.com/sites/site3

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.

See Also

Best ways to manage Office 365 with Windows PowerShell