Wouldn’t it be nice if you could just ask SharePoint a simple question and get a straight answer? Unfortunately, SharePoint through the web interface doesn’t always give us the answers we need. For example, through the web interface, you cannot get a list of all the websites and how much space they use. Having that kind of information is important for SharePoint governance. Knowing your growth is fundamental. SharePoint governance is an area where sometimes getting a good, simple answer isn’t as easy as it should be.

What is PowerShell?

For those of you who don’t know what PowerShell is, it’s Microsoft’s newer command shell. It looks similar to the classic command prompt window we all hold dear to our hearts. PowerShell is much more powerful though. While installing SharePoint 2010, 2013 or 2016 a set of special PowerShell commands are installed at the same time. These commands will allow us to essentially “talk to the machine” and get usable information from it.

The difference between “get” and “set”

When I usually start talking about PowerShell, I see a lot of trepidation in the eyes of admins and content owners. Just to be clear, yes, PowerShell is very powerful and yes, you can cause a lot of damage if not used properly. Just to be reassured, the commands we will cover will not make any changes to your farm or content.

Format-List vs. Format-Table

These two little commands are invaluable. Depending on which format command you use, your data will come back drastically different. I recommend trying out each command so you can get a feel for the type of output.

The Pipe command “|”

The pipe command is basically means “then do this next.” If I were to say “Get a bucket of rocks and then tell me about them” it would be written as:

“Get bucket of rocks | tell me about them”

Let’s add a little more to it…“Get a bucket of rocks and then tell me about their size, shape and weight.”

“Get bucket of rocks | Format-Table Size, Shape, Weight”

The command above will get the rocks and then will arrange them in a table displaying the properties of the rocks. This next command, while very similar, will display the properties of the rocks drastically different:

“Get bucket of rocks | Format-List Size, Shape, Weight”

Instead of a table, we will get each rock and it will show each property in a list format.

Tables are very useful when you want to look at lots of things with few properties. Lists are best if you want lots of detail on each thing.

Properties of an object

An often asked question is how to get a list of the properties I can filter on. In our rock example, so far we know size, shape and weight. When you look at a rock you know there are lots of other properties the rock has. We can get a list of all the properties that are available to show by using the get-member() command. The left and right parentheses are required for this command. The command would look something like this:

rock | get-member()

This produces a list of all the things you can do with a rock.

PowerShell and SharePoint

OK, let’s stop talking about rocks and start taking about SharePoint.

Following are some useful PowerShell commands that you can use to help report on, monitor and control (i.e., govern) your SharePoint environment.  All the commands can be typed in directly as they are listed in this document. If you wish to copy and paste, be sure to paste them to notepad first and verify there are no HTML formatting artifacts. Each image has a copy of the command in full so you can see what it will look like in PowerShell.

I recommend logging into one of your SharePoint servers and opening a SharePoint command shell. The next few commands are report only and pose no threat to accidentally deleting or altering SharePoint items.

Database Reporting

In SharePoint, we have content databases and SharePoint databases. When you look in SQL server, it can be very difficult to decipher which is which. We can easily distinguish between them using two commands

The first is get-spcontentdatabase. This command will tell us every content database in the farm with basic information.

image 1

The get-spdatabase command shows us every database including system databases. (I’m using format-table for clarity; try it without the pipe to get even more information).

image 2

SharePoint Designer Usage

In environments where SharePoint Designer is restricted, we may want to know what site collections have SharePoint designer enabled. This is easy to accomplish with the command:

Get-spsite -limit All | ft url, ContentDatabase, AllowDesigner

image 3

If the allow designer is set to false, you know that SharePoint Designer is restricted.

Site Collection Usage

One of the questions I receive most often is how can I get a list of all my SharePoint site collections and how much space they consume.  This task gets a little more complex because that data is not directly available, and we must do some calculations with it. This command will return all site collection URLs, titles and sizes. NOTE: this command is run on one line and may look like 2 lines while reading this post.

Get-spsite -limit all | ft url, contentdatabase, @{expession={$_.usage.storage/1024/1024};label=”DiskUsageMB”}

image 4

We are required to use the expression syntax because the site collection disk usage isn’t available to us directly, we need to calculate it.

Using data in other applications like Excel

This data can also be exported to a text file. Import the file into Excel, and with some simple math, we can get a nice idea of what our SharePoint growth looks like over time. Once you have the data in Excel, you can start building graphs and pivot tables.  Data usage is extremely valuable for SharePoint governance.  Here is the full command to export to a text file

Get-spsite -limit all | ft url, contentdatabase | out-file -path “c:\yourfolder\textfile.txt”

image 5

Go be Adventurous!

This is just a small sampling of commands you can run with PowerShell to help you better monitor and manage your SharePoint environment. Once you get a feel for how the commands work, you can start building on them and even start automating repetitive processes.  Go be adventurous!

Reference Section

If you would like some more information on some of the topics we covered, please visit these sites:

Formatting Tables: https://technet.microsoft.com/en-us/library/ee692794.aspx

Everything PowerShell: https://ss64.com/ps/

If you like the smell of paper, I recommend this book: https://www.amazon.com/Beginning-PowerShell-SharePoint-Nikolas-Charlebois-Laprade/dp/1430264721

Starter Video: https://youtu.be/UuLho-HecXA

This tip written by Abel Solutions Senior SharePoint Consultant James Cragle.