Show storage overview for site-collections with PowerShell

PowerShell is very powerful and helps you to quickly get different views on your SharePoint environment. The Usage class can be used to retrieve Storage information for a particular site-collection. Combining this into a PowerShell script provides an overview of storage sizing for your personal storage sites (My Sites) for example.

In this example all My Sites are located within the namespace When querying the My Sites I’m using this as input. After the basic query I transform the output to get the report.

The first query can be used for the numbers based on megabytes (MB). I’m using the System.Math.Round() method to transform the output based on custom tables in PowerShell.

Get-SPSite -Limit ALL | ?{$_.Url -like "**"} | Format-Table Url, @{Expression={[math]::round($_.Usage.Storage/1MB, 3)};Label="Storage"} -AutoSize

The output is shown below.

PowerShell console, output storage query

When I change the formatting for better readability you get the following query. The column Storage is displayed based on string formatting.

Get-SPSite -Limit ALL | ?{$_.Url -like "**"} | Format-Table Url, @{Expression={($_.Usage.Storage/1MB).ToString("#,###.000 MB")};Label="Storage"} -AutoSize

This will impact the column Storage shown in the output below.

PowerShell console, output storage query with string Formatting

Enjoy! Have fun with PowerShell.

How to: Detect the installed SKU of SharePoint with PowerShell

Do you need to know which SharePoint SKU or edition is installed on your server? Are you sure your product is not running the trial version? Do you need SharePoint 2010/2013 SKU and/or patch level information from your customer or IT department? This article provides you an easy to use PowerShell script which checks the installed products on a server and outputs the version (patch level).

You can use the Get-SPEdition PowerShell script as is. Simply run the script or use the Functions and call Get-SPEdition. Using the PowerShell scripts results in the following output.

Windows PowerShell: out Get-SPEdition

The script reads the registry to retrieve the installed versions. This information is located in below the “HKLM:software\Microsoft\Shared Tools\Web Server Extensions\15.0\WSS\InstalledProducts” path. Every GUID represents a SKU.

$products = @{ "BEED1F75-C398-4447-AEF1-E66E1F0DF91E" = "SharePoint Foundation 2010"; "1328E89E-7EC8-4F7E-809E-7E945796E511" = "Search Server Express 2010"; "B2C0B444-3914-4ACB-A0B8-7CF50A8F7AA0" = "SharePoint Server 2010 Standard Trial"; "3FDFBCC8-B3E4-4482-91FA-122C6432805C" = "SharePoint Server 2010 Standard"; "88BED06D-8C6B-4E62-AB01-546D6005FE97" = "SharePoint Server 2010 Enterprise Trial"; "D5595F62-449B-4061-B0B2-0CBAD410BB51" = "SharePoint Server 2010 Enterprise"; "BC4C1C97-9013-4033-A0DD-9DC9E6D6C887" = "Search Server 2010 Trial"; "08460AA2-A176-442C-BDCA-26928704D80B" = "Search Server 2010"; "84902853-59F6-4B20-BC7C-DE4F419FEFAD" = "Project Server 2010 Trial"; "ED21638F-97FF-4A65-AD9B-6889B93065E2" = "Project Server 2010"; "926E4E17-087B-47D1-8BD7-91A394BC6196" = "Office Web Apps 2010"; "35466B1A-B17B-4DFB-A703-F74E2A1F5F5E" = "Project Server 2013"; "BC7BAF08-4D97-462C-8411-341052402E71" = "Project Server 2013 Preview"; "C5D855EE-F32B-4A1C-97A8-F0A28CE02F9C" = "SharePoint Server 2013"; "CBF97833-C73A-4BAF-9ED3-D47B3CFF51BE" = "SharePoint Server 2013 Preview"; "B7D84C2B-0754-49E4-B7BE-7EE321DCE0A9" = "SharePoint Server 2013 Enterprise"; "298A586A-E3C1-42F0-AFE0-4BCFDC2E7CD0" = "SharePoint Server 2013 Enterprise Preview"; "D6B57A0D-AE69-4A3E-B031-1F993EE52EDC" = "Microsoft Office Online"; "9FF54EBC-8C12-47D7-854F-3865D4BE8118" = "SharePoint Foundation 2013" } $registryPath = "HKLM:software\Microsoft\Shared Tools\Web Server Extensions\$((Get-SPFarm).BuildVersion.Major).0\WSS\InstalledProducts" Get-RegistryKeyPropertiesAndValues -path $registryPath | ForEach-Object { Write-Host "Installed product: $($products.Get_Item($_.value)) (SKU ID: $($_.value))" } Write-Host "Installed version: $((Get-SPFarm).BuildVersion)"

With a little help from the Scripting Guy (Ed Wilson) and two MSDN articles I build the script. Enjoy!

Download: Get-SPEdition.ps1


Support for SharePoint 2010 on Windows Server 2012 + R2

When you want to run SharePoint 2010 on either Windows Server 2012 or Windows Server 2012 R2 make sure your SharePoint 2010 is at least running Service Pack 2 (SP2)!Although Hardware and software requirements (SharePoint Server 2010) does not make a note about Windows Server 2012, the KB article 2724471 does outline the support for Windows Server 2012 and Windows Server 2012 R2.

Before the release of Service Pack 2 (SP2) for Microsoft SharePoint Server 2010, Microsoft did not support SharePoint Server 2010 in a Windows Server 2012 or Windows Server 2012 R2 environment. 
However, SharePoint Server 2010 with SP2 has now been released, and this configuration is supported in Windows Server 2012 and Windows Server 2012 R2.

So, it’s supported to run SharePoint 2010 on Windows Server 2012, check out the KB article 2724471 and the description of SharePoint 2010 Service Pack 2. Enjoy all new goodies from Windows Server 2012!

[Update: SharePoint 2013 is not supported on Windows Server 2012 R2 for now. This will be supported when Service Pack 1 is released. Check KB article 2891274.]

[Update: SharePoint 2013 Service Pack 1 is released, check Announcing the release of Service Pack 1 for Office 2013 and SharePoint 2013. Resulting in SharePoint 2013 being supported on Windows Server 2012 R2]

Get disk storage for SharePoint databases

To get an indication for all database sizes in your SharePoint farm, run the following command. It shows the disk storage in bytes and MB.


The PowerShell script used for the output above, can be found below.

Add-PSSnapin Microsoft.SharePoint.PowerShell Get-SPDatabase | sort Name | ft Name, ` @{Name="DiskSizeRequired";Align='Right';Expression={[string]("{0:N0}" -f($_.DiskSizeRequired))}}, ` @{Name="Size(MB)";Align='Right';Expression={[string]("{0:N0}" -f($_.DiskSizeRequired/1mb))}}, ` Server -Autosize Get-SPDatabase | sort Name | select Name, DiskSizeRequired ` | measure-object -Property DiskSizeRequired -Sum -Maximum -Minimum -Average ` | ft Count, @{Name="Sum(MB)";Align='Right';Expression={[string]("{0:N0}" -f($_.Sum/1mb))}}, ` @{Name="Maximum(MB)";Align='Right';Expression={[string]("{0:N0}" -f($_.Maximum/1mb))}}, ` @{Name="Minimum(MB)";Align='Right';Expression={[string]("{0:N0}" -f($_.Minimum/1mb))}}, ` @{Name="Average(MB)";Align='Right';Expression={[string]("{0:N0}" -f($_.Average/1mb))}}

Why can’t I use .Include() with CSOM

While working on the SharePoint Client Browser for SharePoint 2010 and 2013 I wanted to optimize performance by using the .Include() method. Visual Studio did not show the method and the build broke. This post takes about the use of .Include() and  .IncludeWithDefaultProperties() and the reason why Visual Studio did not show/support both methods.


Not all properties within the CSOM are loaded by default when using the ClientContext.LoadQuery. This design decision is taken by the Product Team due performance optimization. They did provide an option to extend the properties loaded by using the .Include() or .IncludeWithDefaultProperties() Linq extensions for the SharePoint Client Side Object Model (CSOM).

The example code below shows the use of the .Include() method and loads all lists together with the related fields within 1 request. This optimizes performance by minimizing the overload for a second or even more queries.

The issue with using the .Include() comes when the code is slightly changed! Check the altered code example below!

using System; using System.Collections.Generic; using Microsoft.SharePoint.Client; namespace SharePointCSOMConsole { class Program { static void Main() { ClientContext clientContext = new ClientContext(""); IEnumerable<List> lists = clientContext.LoadQuery( clientContext.Web.Lists.Include( list => list.Title, list => list.Hidden, list => list.Fields.Include( field => field.Title, field => field.Hidden))); clientContext.ExecuteQuery(); foreach (List list in lists) { Console.WriteLine("{0}List: {1}", list.Hidden ? "Hidden " : "", list.Title); foreach (Field field in list.Fields) Console.WriteLine(" {0}Field: {1}", field.Hidden ? "Hidden " : "", field.Title); } } } }

Visual Studio not showing .Include()

While working on the SharePoint Client Browser for SharePoint 2010 and 2013 I wanted to optimize performance by using the .Include() method. But my code did not allow me to use the .Include() method. The screenshot below shows Visual Studio source code editor with the missing extension method.

Altered code example, no allowing to use .Include()


Because I used a different using statement on the top the .Include() method was not shown. After changing the “using SP = Microsoft.SharePoint.Client;” back to “using Microsoft.SharePoint.Client;” the issue was resolved.

SharePoint Client Browser (preview) released

Last Saturday I introduced the SharePoint 2013 Client Browser (preview) tool during my session on SharePoint Saturday Holland 2013. I’ve got some enthusiastic feedback and now it’s time to finally release the preview version!

Remote SharePoint development is getting more important. Especially with SharePoint Apps. To speed up development, find hidden lists/items/documents, discover the structure or specific artifact properties use the SharePoint Client Browser which supports both SharePoint 2010 and SharePoint 2013.
This WinForm tool does not need installation but simply unzip the download and start the EXE file. Next step, connect to your on-premise or Office 365 site collection.

More information and download:

SharePoint 2013 Client Browser

DIWUG: Extending the My Site activity feed with external blog posts

Yesterday enjoyed another DIWUG user group meeting and presented the Extending the My Site activity feed with external blog posts. You can find the presentation here and I’ll make sure to create a Codeplex project soon. For now you can download the sources here.

DIWUG thanks again for arranging the event and Sogetti thanks for hosting.

Download PowerPointDownload

Can’t install Office Web Apps on SharePoint trial license

Installing SharePoint Server 2010 with a trial license is something that doesn’t happen often. Since we did install SharePoint trial edition we ran into an issue which I wasn’t aware of:

Trial editions and licensed editions of SharePoint, Project Server, and Office Web Apps products may not be installed on the same server.

The screenshot below shows the error message you get, directly after running the setup.exe.

Error installing Office Web Apps on SharePoint trial edition

For those who are wondering how to work around this, you can’t. The only solution is getting a license key and upgrade the Trial license to a Standard or Enterprise license. Afterwards you can install Office Web Apps.

Tip: Being aware of this I strongly recommend using the Standard or Enterprise license during installation instead of afterwards.

Extending the My Site activity feed with external blog posts (SPSNL)

A bit more than a month ago I presented at SharePoint Saturday Holland (SPSNL). Unfortunately I didn’t got to writing this post and providing my slide deck and demo. SPSNL was a great of SharePoint and I enjoyed it. Quite a bunch of national and international speakers gathered in Vianen. Thank you DIWUG for organizing this day of SharePoint madness. You’ll find evidence here.

I did a session on extending the activity feed. Preparing the session was quite a challenge since I had to put in a large amount of hours due to NotImplementedException exceptions and other hassle. I hope it made the session worthwhile.

Below you can find my slide deck and demo code.

Download PowerPoint Download Demo

Hide “Fabulous 40” templates

The Fabulous 40 (a.k.a. Fab40 or Application Templates) are a set of 40 application templates for Windows SharePoint Services 3.0. These were installed on a lot of SharePoint 2007 environments.

When upgrading to SharePoint 2010 these templates become a challenge. The sites based on the Site Admin Templates (.STP) will be upgraded. After the upgrade these will likely work, but you can’t create any new sites based the STP templates, because SharePoint 2010 does not support STP files for site templates.

Sites which are based on the Server Admin Templates (.WSP) are more challenging because some Fab40 templates cannot be used for creating new sites in SharePoint 2010. To perform an upgrade you need to install the templates which are currently in use in the SharePoint 2007 environment.

To keep the new SharePoint 2010 environment as clean as possible you want to disable the creation of new sites based on the Fab40. You could choose to update the XML files on the file system (webtemp files in 14-hive), but the downside of this is you need to update all servers in the farm and when a new server is added this needs to be adjusted manually.

So I started looking into the order SharePoint reads the webtemp files when the application pool process (W3WP.exe) spins up on initial load. I used Process Monitor to research the file access in the “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\1033\XML\” folder. Based on the order shown in the screenshot below, it seems the “webtemp???.xml” files are loaded on alphabetical order. 

Process Monitor - Capturing the W3WP.exe process

Meaning if I would place a webtemp XML file which filename starts with “webtempaaa” it would load first and equally the filename “webtempzzz” would load at the end. Since underscores “_” are often used I wanted to check that particular filename as well. It seems a filename “webtemp_” is loaded at the end, instead of at the start (what I assumed).

The next question is: “Does SharePoint keep the settings which it reads in the first files or do these settings get overridden by settings in files loaded afterwards?”. The answer to this is it only stores the settings which it reads first. Meaning if I want to hide the templates I need to add a webtemp file with the name “webtempaaaHideFab40.xml”. Within the file I need to hide all the Fabulous 40 Server Admin Templates.

The XML file below shows the contents of the “webtempaaaHideFab40.xml” file:

<?xml version="1.0" encoding="utf-8" ?> <!-- Hides all Fabulous 40 templates, filename needs to begin with "webtempaaa..." to be loaded as the first one --> <Templates xmlns:ows="Microsoft SharePoint"> <Template Name="absence" ID="75811" SetupPath="SiteTemplates\absence" > <Configuration ID="0" Title="Absence and Vacation Schedule " Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="projmulti" ID="75819" SetupPath="SiteTemplates\projmulti" > <Configuration ID="0" Title="Budgeting and Tracking Multiple Projects" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="BT" ID="75801"> <Configuration ID="0" Title="Bug Database" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="callcenter" ID="75812" SetupPath="SiteTemplates\callcenter" > <Configuration ID="0" Title="Call Center" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="projchange" ID="75818" SetupPath="SiteTemplates\projchange" > <Configuration ID="0" Title="Change Request Management" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="comproc" ID="75813" SetupPath="SiteTemplates\compproc" > <Configuration ID="0" Title="Compliance Process Support Site" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="CM" ID="75802"> <Configuration ID="0" Title="Contacts Management" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="DR" ID="75803"> <Configuration ID="0" Title="Document Library and Review" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="eventplan" ID="75814" SetupPath="SiteTemplates\eventplan" > <Configuration ID="0" Title="Event Planning" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="exreports" ID="75815" SetupPath="SiteTemplates\exreport" > <Configuration ID="0" Title="Expense Reimbursement and Approval Site" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="helpdesk" ID="75816" SetupPath="SiteTemplates\helpdesk" > <Configuration ID="0" Title="Help Desk" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="IT" ID="75804"> <Configuration ID="0" Title="Inventory Tracking" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="itteam" ID="75817" SetupPath="SiteTemplates\itteam" > <Configuration ID="0" Title="IT Team Workspace" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="JRIM" ID="75805"> <Configuration ID="0" Title="Job Requisition and Interview Management" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="KB" ID="75806"> <Configuration ID="0" Title="Knowledge Base" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="LL" ID="75807"> <Configuration ID="0" Title="Lending Library" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="PATM" ID="75808"> <Configuration ID="0" Title="Physical Asset Tracking and Management" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="projsing" ID="75820" SetupPath="SiteTemplates\projsing" > <Configuration ID="0" Title="Project Tracking Workspace" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="RER" ID="75809"> <Configuration ID="0" Title="Room and Equipment Reservations" Hidden="True" DisplayCategory="Application Templates" /> </Template> <Template Name="ST" ID="75810"> <Configuration ID="0" Title="Sales Lead Pipeline" Hidden="True" DisplayCategory="Application Templates" /> </Template> </Templates>