Taking Inventory with PowerShell: SharePoint Online

* This is an outdated blog post and no longer maintained *

This blog will cover some recent experiences with PowerShell for SharePoint Online (SPO). Some excellent blogs and articles on the subject are available on the Web. For example, Mike Rand, one of my 3Sharp co-workers, recently posted a nice blog on the PowerShell Command Builder that can be used to connect to and communicate with an SPO tenant (as well as with other SharePoint versions). Another 3Sharp-ie, David Gerhardt, posted this blog on connecting to an SPO tenant with Command Builder. An excellent overview of PowerShell and SPO is this post by Gary Lapointe. It’s not my purpose to re-visit these blogs, but rather to show some specific examples of what you can do with the SPO cmdlets. If you’re short of time, here’s an executive summary: PowerShell for SPO is a good start, but pretty limited right now. I hope more functionality will be coming soon.

Limitations in PowerShell for SPO could certainly be expected, given that the number of cmdlets available for SPO is relatively small—about 30—compared to 500 or more for SharePoint Server. (The exact number depends on the version of SharePoint and also the version of PowerShell that you use.) For this blog, I limited myself to a quick exploration of cmdlets that retrieve information from an SPO tenant. Such cmdlets would be useful for periodic status checks, taking a site collection inventory, preparing for content migration, and other common administrative functions. So, I’ll not be discussing control (“Set-“) cmdlets this time; perhaps I’ll get to those in another post.

The PowerShell cmdlets that have to do with retrieving SPO information (or more formally, objects) all start with “Get-SPO“. I chose the following cmdlets because they looked promising:

  • Get-SPOTenant – Retrieve information about the subscription tenant

  • Get-SPOSite – Retrieve an existing site collection

  • Get-SPOUser – Get an existing user

  • Get-SPOWebTemplate – Get a list of all available web templates

  • Get-SPOSiteGroup – Get a list of groups defined for the site collection

To explore these cmdlets, I fired up the latest (and much improved) PowerShell ISE v3, which is PowerShell’s own scripting environment with all the latest helps, full IntelliSense support, and other useful features.

Note: Instead of the scripting environment, you can also use the PowerShell v3 console. For details on how to get ISE v3 and PowerShell v3, see this TechNet article.

The first thing to do is establish a connection to the SharePoint Online Enterprise tenant. Loading a script I built for this blog into ISE, and then running a section of code similar to the following, started things off. Please note, the login must be to the admin site with appropriate permissions.


Let’s start at the tenant level. The cmdlet for that is Get-SPOTenant; here is what it brings back:

Well, there is some information here; but it’s pretty limited…let’s move on to the site collection level:


In this default “list” output mode, we get details of the site collections, one at a time. Here we see items related to an externally-facing site collection named “Contoso Bistro”. In this tenant, there are a number of other collections displayed after this one.

Console output is OK, and it can be tweaked using various output switches to provide other views. But once the console session is closed, the information is gone. For a more permanent and easily formatted record of a tenant’s status, output to files is much more useful.

There are several familiar output formats available in PowerShell: free text, HTML, and character-separated values (CSV). CSV files can be readily imported into Excel, and would be ideal. Unfortunately, in my experience some of the cmdlets did not output correctly to CSV (the output did not match what you see on the console). These cmdlets output to text files more accurately. Fortunately, with only a little more work, text files can also be imported into Excel. Here is the section of script to get and then export five types of tenant data. Note the first three exported to CSV; the remaining two exported to text. In this code section are some variables that define the site collection ($site) and the names and paths for the output files ($sitesFile, etc.)


In just a matter of minutes, I was able to import the files into Excel, consolidate them into one workbook, and format the data as tables. The tables can then be sorted and filtered as desired. As an example, here is the tenant information previously obtained on the console:


Here’s the tab for site collections. Note that it is truncated in the screen shot, but it’s fully populated in the Excel file.


This site template list is useful as a reference:


Next, we have the Users list:


…and finally, the Groups list:


Well, that’s an overview of the main “Get-SPO” cmdlets for SharePoint Online. These cmdlets do return some fundamental information about a tenant, its site collections, users, and groups. However, the information is quite limited—especially compared to SharePoint Server. For example, you cannot generate an inventory of lists, libraries, or documents in a site collection; at least I could not find a way to do it. These would be essential on a tenant where users are actively contributing content. In contrast, there are many ways to obtain such data from SharePoint Server using PowerShell; for example, see this article.

In summary, I have to agree with the rather unflattering “Parting Thoughts” in Gary Lapointe’s blog I referenced earlier. But as also stated there, this is a good start. PowerShell for SPO just needs to be brought closer to par with SharePoint Server. With the growth of Office 365 and SharePoint Online, I’m betting (hoping?) that will happen soon.

* This blog post is out of date and is no longer supported *