U2U Blog

for developers and other creative minds


Creating A Self-Signed Code Signing Certificate from PowerShell

In PowerShell, being able to execute scripts depends on the execution policy of your machine. You might be able to change the execution policy yourself and set it to Unrestricted, meaning you can execute scripts without signing them. If you are not an administrator, or your group policy defines the execution policy, you will need to sign your script. To see your current execution policy, execute the following command:

Get-ExecutionPolicy -List

To create a self-signed code signing certificate for PowerShell, makecert used to be the best solution. In PowerShell we have a cmdlet called New-SelfSignedCertificate which we can also use for this purpose. Since V5 this cmdlet has been updated to make it easier to do so. To create a Code Signing certificate execute the following command:

$cert = New-SelfSignedCertificate -CertStoreLocation 
Cert:\CurrentUser\My -Type CodeSigningCert -Subject "U2U Code Signing"

To verify that the certificate has been generated, run this command:

Get-ChildItem -Path Cert:\CurrentUser\My | ? Subject -EQ "CN=U2U Code Signing"

The result should look like this.

Great! Now use the certificate to sign your script:

Set-AuthenticodeSignature -FilePath .\signedscript.ps1 -Certificate $cert

Oops! That didn't work!

Our certificate is not trusted as it is in the personal store. Let's move it to a better location:

Move-Item -Path $cert.PSPath -Destination "Cert:\CurrentUser\Root"

Make sure you confirm the installation of the certificate.

Now try again!

Set-AuthenticodeSignature -FilePath .\signedscript.ps1 -Certificate $cert

Better! You should now be able to execute the signed script!

The full script looks like this:

$cert = New-SelfSignedCertificate -CertStoreLocation Cert:\CurrentUser\My -Type CodeSigningCert -Subject "U2U Code Signing"
Move-Item -Path $cert.PSPath -Destination "Cert:\CurrentUser\Root"
Set-AuthenticodeSignature -FilePath .\signedscript.ps1 -Certificate $cert

Use PowerShell and CSOM to batch create SharePoint Online Site Collections with a custom template



In this post I will show you how to create multiple Site Collections in SharePoint Online using PowerShell and the CSOM (Client Side Object Model) libraries provided by Microsoft. It will also be possible to apply a custom template to the Site Collections that we create.

As at the time of writing the possibilities of the SharePoint Online Management Shell are too limited to do this - especially regarding Sandboxed Solutions and thus applying templates – everything will be done by using the CSOM SharePoint assemblies in PowerShell. We will initialize .NET objects using PowerShell.

These are the steps we want to implement in the PowerShell Script:

  1. First we will check if the Site Collection already exists in SharePoint Online. If it does, we will delete it.
  2. Before we can create a new Site Collection at the URL of a deleted Site Collection, we need to remove it from the recycle bin.
  3. When we are sure no Site Collections exist, let's now create them!
  4. In case we want to apply a custom template, there are a few steps to perform:
    1. Upload the template to the Solutions gallery
    2. Activate the uploaded solution
    3. Apply the uploaded template to the Root Web of the Site Collection
    4. Delete the uploaded template file
  5. Set the security for the site collection

The assemblies we need to load are the following:

  • Microsoft.Online.SharePoint.Client.Tenant.dll
  • Microsoft.SharePoint.Client.dll
  • Microsoft.SharePoint.Client.Runtime.dll
  • Microsoft.SharePoint.Client.Publishing.dll

To get these DLL's, download the SharePoint Server 2013 Client Components SDK here.

The tenant DLL is used for deleting and creating site collections using your SharePoint Online admin URL.
The other DLL's are used to access the created site collections and perform operations on them. The publishing DLL specifically is required to activate sandboxed solutions.


Getting started


First we'll create a PowerShell cmdlet, which we can then call with a set of parameters to get the Site Collection creation process going. All these parameters have a default value, but can be overwritten when calling the function. Notice the SiteTemplate by default uses the Team Site template ("STS#0"). If you wish, you can get a list of available templates from your tenant as well: link.

The rest of the parameter names should speak for themselves.

function CreateSiteCollections{
        [string]$Domain = "*yourid*.onmicrosoft.com",
        [string]$UserName = "*adminusername*@$domain",
        [string]$UserPassword = "*yourpassword*",
        [string]$AdminUrl = "https://*yourid*-admin.sharepoint.com",
        #site details
        [string]$SiteUrl = "https://*yourid*.sharepoint.com",
        [string]$SiteTitle = "My Team Site",
        [string]$SiteOwner = "*siteowner*@$domain",
        [string]$Prefix = "*SiteUrlPrefix*",
        [string]$Suffix = "",
        [string]$SiteTemplate = "STS#0",
        #site numbers
        [int]$StartIndex = 1,
        [int]$NumberOfSites = 5,
        [int]$BatchSize = 5
    #We’ll add the rest here

Inside this function, the first thing we are going to do is load the CSOM assemblies from their installation path. When installing the SDK the assemblies will be found at C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI – the Tenant DLL assembly - and C:\Program Files\SharePoint Client Components\16.0\Assemblies.
As a first step in the script we will verify that both paths exist.

As we only need the publishing assembly when applying a custom template, we will only load it when the $IsCustomTemplate switch parameter is passed in. When executing this script we are assuming there is a folder Templates at the execution path from which we will load the custom template when required. When $IsCustomTemplate is provided to the function call, we expect the parameter $SiteTemplate to contain the filename of the site template, i.e. "MyCustomTemplate.wsp".

If so desired, you could also create a local folder containing your assemblies and point the Add-Type calls to them.

Write-Host "Loading Assemblies`n" -ForegroundColor Magenta
$ClientAssembyPath = resolve-path("C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI") -ErrorAction Stop
$TenantAssembyPath = resolve-path("C:\Program Files\SharePoint Client Components\16.0\Assemblies") -ErrorAction Stop
Add-Type -Path ($ClientAssembyPath.Path + "\Microsoft.SharePoint.Client.dll")
Add-Type -Path ($ClientAssembyPath.Path + "\Microsoft.SharePoint.Client.Runtime.dll")
Add-Type -Path ($TenantAssembyPath.Path + "\Microsoft.Online.SharePoint.Client.Tenant.dll")

Add-Type -Path ($ClientAssembyPath.Path + "\Microsoft.SharePoint.Client.Publishing.dll")
$CurrentPath = Convert-Path(Get-Location)
$PackagePath = resolve-path($CurrentPath + "\Templates\" + $SiteTemplate) -ErrorAction Stop

Write-Host "Can't load assemblies..." -ForegroundColor Red
Write-Host $Error[0].Exception.Message -ForegroundColor Red

Batch create sites

For this script, I've chosen to create the Site Collections in batches. Calling ExecuteQuery after each operation takes too long, but creating 20 sites with one ExecuteQuery call is just too much for the web service to handle. We'll pick 5 as a default batch size, but feel free to choose what works best for you.
The idea is that we launch for instance 5 site collection creation operations in one go, and then wait for all the operations to complete. This will improve the execution time of our script.

First, we will generate the site collection names and put them in the $SiteNames variable. Each site name is a combination of the $Prefix parameter, the current index and the $Suffix parameter.
We will then grab a batch sized chuck of the site names array and copy them into the $BatchSiteNames variable. We will then perform all required operations for the sites in that batch.

$indexes = $StartIndex..($StartIndex + $NumberOfSites - 1)
[string[]]$SiteNames = @() 
$indexes | % { $SiteNames += ($SiteUrl + "/sites/" + $Prefix + $_ + $Suffix) }
Write-Host "The following sites will be created:" -ForegroundColor Magenta

$CurrentIndex = 0

While($CurrentIndex -lt $SiteNames.Length){ 
      	if($CurrentIndex + $BatchSize -le $SiteNames.Length){
$BatchSiteNames = $SiteNames[$CurrentIndex..($CurrentIndex+($BatchSize - 1))] 
$BatchSiteNames = $SiteNames[$CurrentIndex..($SiteNames.Length - 1)] 
Write-Host "`nProcessing batch of sites $CurrentIndex -> $($CurrentIndex + $BatchSiteNames.Length)" -ForegroundColor Yellow

#Site Creation logic goes here

$CurrentIndex += $BatchSiteNames.Length

Write-Host "Something went wrong..." -ForegroundColor Red
throw $Error[0]

Create Sites

Let's start with the first operation, which is deleting the site collection if it already exists! First, we'll need to get all the existing site collections from the SharePoint Online tenant, then we'll match the url's with the ones we are trying to exist. If we have a match we will delete the site collection.

#create the sites   
Write-Host "`nCreating the site collections`n" -ForegroundColor Magenta    
CreateSites -Sites $BatchSiteNames 

We will split up the logic into functions. Each function will execute one step of the site creation process. First we'll create the CreateSites function. This function will accept a set of site names, namely the ones we want to create in the current batch.

The function should be inside the CmdLet body, in order to have the parameters available.

We need to initialize a SiteCreationProperties object with our site name, url and other configuration properties.

You'll notice that we get a ClientContext object from the GetContext function, so we'll implement that one in the next step. When working with a ClientContext, we first need to perform a ClientContext.Load() on all the objects we want to use and read properties from, followed by an ExecuteQuery() to actually get the objects. With the Load we are queueing things to be loaded, while the EecuteQuery will then execute the load.

When we perform operations on the TenantAdministration.Tenant namespace in CSOM, what we get back is an SpoOperation object. On this object we can check if the operation has completed by reading the IsComplete property. As mentioned before, we will create a batch of operations first, execute the query, and then wait for all the operations to complete. As this can be implemented in a generic way for all the operations we are going to perform we'll write a function WaitForOperations that will do exactly that.

Notice that we will also create a generic ExecuteLoad function, as well as a generic ProcessError function.

function CreateSites($Sites){
        #Set Admin Context
        $Context = GetContext -Url $AdminUrl
        #Get the Tenant object
        $Tenant = New-Object Microsoft.Online.SharePoint.TenantAdministration.Tenant($Context)
        $Operations = @()
        $Sites | % {
            $SiteCreateUrl = $_
                #Set the Site Creation Properties values
                $properties = New-Object Microsoft.Online.SharePoint.TenantAdministration.SiteCreationProperties
                $properties.Url = $SiteCreateUrl
                $properties.Title = $SiteTitle
                    $properties.Template = $SiteTemplate
                $properties.Owner = $UserName
                $properties.StorageMaximumLevel = 250
                $properties.UserCodeMaximumLevel = 50
                $properties.TimeZoneId = 3
                $properties.Lcid = 1033
                #Create the site using the properties
                Write-Host "Creating site collection at url $SiteCreateUrl"
                $Operation = $Tenant.CreateSite($properties)
                $Operations += $Operation
                ProcessError("Error creating site $SiteCreateUrl")

        ExecuteLoad -ExecContext $Context

        WaitForOperations -JobContext $Context -Operations $Operations -Description "Site creation"


Let's impletement the GetContext function next. First we'll get a ClientContext object. Then we will add some Credentials to it, using the parameters that were passed in through the CmdLet.

On the ClientContext, we will change a few settings, as the defaults kept giving me errors.

Some errors that I had before these changes:

  • The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.
  • Service endpoint not found

By switching the http protocol to version 1.0 and disabling KeepAlive on the webrequest, these errors seem to go away. I would have to spend some more time to figure out why these errors occur using the SPO web service.

function GetContext($Url){

$Context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)      
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials(
$(ConvertTo-SecureString $UserPassword -AsPlainText -Force)
$Context.Credentials = $credentials
$Context.RequestTimeout = -1
$Context.PendingRequest.RequestExecutor.RequestKeepAlive = $true
$Context.PendingRequest.RequestExecutor.WebRequest.KeepAlive = $false
$Context.PendingRequest.RequestExecutor.WebRequest.ProtocolVersion = [System.Net.HttpVersion]::Version10

return $Context

Next, let's implement the ExecuteLoad function. In this function we will call the ExecuteQuery method on the ClientContext. The reason I put this in a separate function is that it allows us to build our own retry or reload logic when desired.

function ExecuteLoad([Microsoft.SharePoint.Client.ClientContext]$ExecContext, $ReloadObject, $Retry = 0){
            Write-Host "Something went wrong..." -ForegroundColor Yellow
            Write-Host $Error[0].Exception.Message -ForegroundColor Yellow
            if($Retry -lt 5){
                $Retry += 1
                Write-Host "Retrying ($Retry)..." -ForegroundColor Yellow
                ExecuteLoad -ExecContext $ExecContext -ReloadObject $ReloadObject -Retry $Retry 
                ProcessError -Message $Error[0].Exception.Message 

Once ExecuteQuery has been called, we have to wait for all the operations to complete. For this we'll write the WaitForOperations function. It will accept a collection of SpoOperation objects, on which there is a method RefreshLoad available, to check whether they are complete or not. It is important not to perform a Load on the SpoOperation again of course, as that will try to execute the operation again, which will cause errors like "Site already exists". Once the RefreshLoad method has been called, we have to call ExecuteQuery again to perform the actual refresh. While not complete we will wait for 10 seconds and loop.

function WaitForOperations($JobContext, $Operations, $Description){
    Write-Host "$Description executing..."
    $TotalOperations = $Operations.length
    if($TotalOperations -eq 0){
        Write-Host "Nothing to execute!"
    while ($true)
        $CompletedJobs = (($Operations | ? IsComplete -EQ $true) | Measure-Object).Count
        Write-Host "$Description status: $CompletedJobs of $TotalOperations completed!"
        if($CompletedJobs -eq $TotalOperations){
            Write-Host "Operation completed!" -ForegroundColor Green
        Sleep -Seconds 10
        $Operations | % { 
        ExecuteLoad -ExecContext $JobContext -ReloadObject $Operations

Also, let's implement the ProcessError method. This method will store the error in a collection variable we will print at the end of the script execution.
First, add this to the start of the CmdLet:

$ErrorLog = @() 

Implement the ProcessError method like this:

function ProcessError($Message){
    Write-Host "$Message`n" -ForegroundColor Red
    Write-Host $Error[0].Exception.Message -ForegroundColor Red
    $ErrorLog += $Message

Add the following code to the end of the cmdlet body:

if($ErrorLog.Count -ne 0){
    Write-Host "`nThe script was executed successfully, but some errors occured:" -ForegroundColor Red
    $ErrorLog | % {
        Write-Host "`n$_" -ForegroundColor Red
    Write-Host 'Check the $Error variable for more information' -ForegroundColor Red
    Write-Host "`nALL DONE! Virtual pat on the shoulder and everything!`n" -ForegroundColor Magenta   

Delete Sites

Before we (re)create sites, we should check if they exist already. If so, we will delete them. For this we need two steps:

  1. Delete the site
  2. Remove the site from the recycle bin – don't forget this step as creating sites at a URL is not possible when a site that was at that URL is still in the recycle bin

The CmdLet content should now look like this:

#Delete sites first if they exist!
Write-Host "`nDeleting existing sites`n" -ForegroundColor Magenta
DeleteSites -Sites $BatchSiteNames

#Recycle sites first if they exist!
Write-Host "`nRecycling deleted sites`n" -ForegroundColor Magenta
RecycleSites -Sites $BatchSiteNames

#now create the sites   
Write-Host "`nCreating the site collections`n" -ForegroundColor Magenta    
CreateSites -Sites $BatchSiteNames 

Let's implement the DeleteSites function.
In this function we will call the GetSiteProperties method to get all the existing site collections. In my opinion this is more efficient than trying to get a site collection by identity for each site collection you want to create. This depends on the number of site collections your tenant has of course.
We'll loop over the site collections and check if they are in the batch of sitenames we want to create. If so, we'll delete the site.

function DeleteSites ($Sites){
    Write-Host "Getting Sites"
    #Set Admin Context
    $Context = GetContext -Url $AdminUrl
    #Get the Tenant object
    $Tenant = New-Object Microsoft.Online.SharePoint.TenantAdministration.Tenant($Context)
    #Get all site collections
    ExecuteLoad -ExecContext $Context
    $Operations = @()
    $TenantSiteCollections | % {
        if($_.Url -in $Sites){
            $SiteDeleteUrl = $_.Url
                Write-Host "Site with url $SiteDeleteUrl will be deleted" -ForegroundColor Yellow
                $Operation = $Tenant.RemoveSite($SiteDeleteUrl)
                $Operations += $Operation
                ProcessError("Error deleting $SiteDeleteUrl")
    ExecuteLoad -ExecContext $Context -ReloadObject $Operations

    WaitForOperations -JobContext $Context -Operations $Operations -Description "Site deletion"


And in a pretty similar way, the RecycleSites function.

function RecycleSites($Sites){
    Write-Host "Getting Deleted Sites"
    #Set Admin Context
    $Context = GetContext -Url $AdminUrl
    #Get the Tenant object
    $Tenant = New-Object Microsoft.Online.SharePoint.TenantAdministration.Tenant($Context)
    ExecuteLoad -ExecContext $Context
    $Operations = @()
    $OperationsStarted = 0
    $TenantDeletedSiteCollections | % {
        if($_.Url -in $Sites){
            $SiteRecycleUrl = $_.Url
                Write-Host "Site with url $SiteRecycleUrl will be removed from the recycle bin" -ForegroundColor Yellow
                $Operation = $Tenant.RemoveDeletedSite($SiteRecycleUrl)
                $Operations += $Operation
                ProcessError("Error removing $SiteRecycleUrl from recycle bin")
    ExecuteLoad -ExecContext $Context -ReloadObject $Operations
    WaitForOperations -JobContext $Context -Operations $Operations -Description "Recycled sites deletion"

Let's now check if we need to apply a custom template.
A template would typically be saved from a site you created on SharePoint Online that you want to reuse. You may try with solutions from on-prem or that you found on the internet, but none are guaranteed to work as some features might be missing.
Add this to your CmdLet body:

#now apply the template if needed
Write-Host "`nApplying Custom Template`n" -ForegroundColor Magenta    
ApplyTemplate -Sites $BatchSiteNames

Let's implement the ApplyTemplate function. First we need to upload the solution file. Then we'll activate it. Next, we'll apply it to the rootweb of the site collection. After this we'll remove the uploaded file.

function ApplyTemplate($Sites){
    $Sites | % {
        $SiteApplyUrl = $_

            Write-Host "Uploading and applying template $SiteTemplate to $SiteApplyUrl" -ForegroundColor White

            $Context = GetContext -Url $SiteApplyUrl

            $PackageFileStream = New-Object System.IO.FileStream($PackagePath, [System.IO.FileMode]::Open) 
            Write-Host "Uploading template" -ForegroundColor Gray
            $SolutionGallery =  $Context.Web.Lists.GetByTitle("Solution Gallery") 
            $SolutionGalleryRootFolder = $solutionGallery.RootFolder

            $SPFileInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation 
            $SPFileInfo.Overwrite = $true 
            $SPFileInfo.ContentStream = $PackageFileStream 
            $SPFileInfo.URL = $SiteTemplate 

            $UploadedFile = $SolutionGalleryRootFolder.Files.Add($SPFileInfo)
            ExecuteLoad -ExecContext $Context

            Write-Host "Activating template" -ForegroundColor Gray

            $PackageInfo = New-Object Microsoft.SharePoint.Client.Publishing.DesignPackageInfo
            $PackageInfo.PackageName = $SiteTemplate 
            $PackageInfo.PackageGUID = [GUID]::Empty 
            $PackageInfo.MajorVersion = "1" 
            $PackageInfo.MinorVersion = "0"
            [Microsoft.SharePoint.Client.Publishing.DesignPackage]::Install($Context, $Context.Site, $PackageInfo, $UploadedFile.ServerRelativeUrl)
            ExecuteLoad -ExecContext $Context

            Write-Host "Applying template" -ForegroundColor Gray

            $AvailableTemplates = $Context.Site.GetWebTemplates(1033, 0)
            ExecuteLoad -ExecContext $Context

            $Context.RequestTimeout = 480000
            $Context.Site.RootWeb.ApplyWebTemplate($AvailableTemplates[$AvailableTemplates.Count - 1].Name)
            ExecuteLoad -ExecContext $Context

            Write-Host "Deleting temporary file" -ForegroundColor Gray
            ExecuteLoad -ExecContext $Context

            Write-Host "`n"
            ProcessError("Error applying template to site $SiteApplyUrl")

As a final step we'll add the user from the $SiteOwner parameter as a site collection administrator.
Add this to the CmdLet body:

Write-Host "`nSetting Security`n" -ForegroundColor Magenta    
SetSecurity -Sites $BatchSiteNames 

Let's implement the SetSecurity function:

function SetSecurity ($Sites){
    $Sites | % {
            Write-Host "Adding site collection admin $SiteOwner to $_" -ForegroundColor White

            $Context = GetContext -Url $_

            $User = $Context.Web.EnsureUser($SiteOwner)
	        ExecuteLoad -ExecContext $Context

	        $User.IsSiteAdmin = $true
	        ExecuteLoad -ExecContext $Context

            ProcessError("Error applying security to site $_")

That's it! We should be good to go and create endless batches of SharePoint Online site collections!

Have fun ;)

You can download the script, it is attached to this post...







U2U.CreateOnlineSiteCollections.ps1 (15,4KB)

Creating a Calendar View with PowerShell

Some days ago someone asked me if it is possible to create a calendar view on a SharePoint list. And yes, it is possible.

First let’s take a look at the .NET code to create a calendar view.

using (SPSite site = new SPSite("http://wss.u2ucourse.com"))
    using (SPWeb web = site.OpenWeb())
        SPList sourcelist = web.Lists["Course Calendar"];

        string querystring =
            "<OrderBy><FieldRef Name='Title' /></OrderBy>"
          + "<Where><DateRangesOverlap><FieldRef Name=\"EventDate\" />"
          + "<FieldRef Name=\"EndDate\" /><FieldRef Name=\"RecurrenceID\" />"
          + "<Value Type=\"DateTime\"><Week /></Value></DateRangesOverlap></Where>";

        SPView newview = sourcelist.Views.Add("DemoCalView", null,
               querystring, 3, false, false,
               SPViewCollection.SPViewType.Calendar, false);

        newview.ViewFields.Add(sourcelist.Fields["Start Time"]);



If you go to your SharePoint site and inspect this new view you will see that only the entries for this week are visible because of the DataRangesOverlap query that is set to weekly, but that it shows the data in a monthly view. This is because of the calendar scope that you can set in the user interface, but how to create a calendar view with a weekly scope programmatically?

Now to PowerShell. To create that same view with PowerShell you have to execute the following commands:

$siteUrl = "
$site = new-object Microsoft.SharePoint.SPSite($siteurl)
$web = $site.OpenWeb()
$list = $web.Lists["Course Calendar"]
$querystring = "<OrderBy><FieldRef Name='Title' /></OrderBy>"
$querystring += "<Where><DateRangesOverlap>"
$querystring += "<FieldRef Name='EventDate' /><FieldRef Name='EndDate' />"
$querystring += "<FieldRef Name='RecurrenceID' />"
$querystring += "<Value Type='DateTime'><Week /></Value>"
$querystring += "</DateRangesOverlap></Where>"
$newview = $list.Views.Add("DemoCalView", $null, $querystring, 3, $false, $false, "CALENDAR", $false)
$titlefield = $list.Fields["Title"]
$datefield = $list.Fields["Start Time"]

I hope to have helped someone out :)

Retrieving Items from a SharePoint List with Powershell and the SharePoint Lists.asmx

There is not yet much information on the net on how to combine Powershell with the SharePoint web services. One of the most used methods of the Lists.asmx, which is a standard SharePoint web service, is the GetListItems method. With this method you can retrieve all list items but also a subset of data when you use a CAML query. You can also limit the number of columns returned if you use a ViewFields node.

If you read previous posts and saved the necessary environment variable declarations in your profile, the only thing you should do to start is:

$listservice = New-Object Lists


Otherwise you have to go back to my post and execute the first steps to declare the environment variables and compile and load the lists dll.

In its most simple form, you can retrieve all list items of a certain list:

$result = $listservice.GetListItems("Customers", $null, $null, $null, $null, $null, $null)

$result is an xml node containing the following:


It's the data element that contains the list items.

If you execute something like the following, you will get the last name of each customer in the result set:

$result.data.row | foreach ($_.ows_Title)


When working with the Lists.asmx SharePoint web service, all fields are prefixed with ows_.

If you want to retrieve a subset of list items you have to pass a CAML query to the GetListItems method. For example, if you want to retrieve all customers living in the city Bendigo, you have to create following CAML query:

<Query><Where><Eq><FieldRef Name='WorkCity' />

<Value Type='Text'>Bendigo</Value></Eq></Where></Query>

You execute the GetListItems method as follows:

$query = "<Query><Where><Eq><FieldRef Name='WorkCity' /><Value Type='Text'>Bendigo</Value></Eq></Where></Query>"

$result = $listservice.getlistitems("Customers", $null, $query, $null, $null, $null, $null)


You can always build your more complex queries with the U2U Caml Query Builder which exists in windows version (download) and feature version (download). Consult my blog or the U2U web site for more detailed information about building CAML queries with the tools. 

Another way to limit the number of rows returned is specifying a row limit, with or without a query:

$result = $listservice.getlistitems("Customers", $null, $query, $null, 3, $null, $null)

If you want to view the complete content of each row, you can execute the following:


You can also limit the number of columns returned in the result set by specifying a ViewFields node. Lets only retrieve the Title and Email for customers living in Bendigo:

[xml]$viewfields = "<ViewFields><FieldRef  Name='Title' /><FieldRef Name='EMail' /></ViewFields>

$result = $listservice.getlistitems("Customers", $null, $query, $viewfields, $null, $null, $null)

As you view the result you will notice that indeed the Title and the Email field are returned but also another bunch of fields. These are the system fields and will always be returned, even if you use the IncludeMandatoryColumns element of the QueryOptions (because this only works for custom fields defined as required). For completeness, you define QueryOptions as follows:

[xml]$queryoptions = "<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>"

$result = $listservice.getlistitems("Customers", $null, $query, $viewfields, $null, $queryoptions, $null)

You can read more about the GetListItems method on the msdn site.

If you need extra information about this method or about other method on other SharePoint web services, you can leave me a comment.

Nick, now you owe me a beer!

Iterating through the columns of a list with Powershell and the SharePoint Lists.asmx web service

Yesterday I got a question on how to iterate through the columns of a list by using Powershell and the SharePoint Lists.asmx web service.

If you read previous post and saved the necessary environment variable declarations in your profile, the only thing you should do to start is:

$listservice = New-Object Lists


Otherwise you have to go back to my last post and execute the first steps to declare the environment variables and compile and load the lists dll.

You can get access to the list by executing the GetList method on the Lists.amx web service. The required argument is the name of the list:

$list = $listservice.GetList("Employees")

You can view the title of the list:


Or the Guid:



You can get access to the fields (or columns) defined on your list by storing the field collection into a variable:

$fieldsnode = $listservice.GetList("Employees").Fields

But the return value of this Fields collection is an xml node. You can view the content of the field collection by executing the $fieldsnode.get_OuterXml() method but this returns you more than a page full of xml. But Powershell is smart enough to be able to handle the child nodes as objects. You can  view the different fields by executing f.e. the $fieldsnode.get_ChildNodes().

And now we come to iterating through the field collection. A field exposes properties like Name (which is the InternalName when working with the SharePoint object model), the ID, the DisplayName, Type and a lot of other properties. You can iterate through the field collection and retrieve the internal name as follows:

$fieldsnode.get_ChildNodes() | foreach-object { $_.Name }



Nick this one is for you! Thanks for reading my blog.

Update a SharePoint List remotely with PowerShell

Yesterday I got a comment on my blog post Update a SharePoint List with PowerShell asking if it is possible to remotely update a SharePoint list. This means that you have to execute the UpdateListItem method of the Lists.asmx web service of SharePoint.

It is possible to work with the SharePoint web services from within SharePoint. There are already some good posts around:

- Calling a Web Service from PowerShell which explains the steps to take before you can consume a Web Service

- Consuming SharePoint Web Services from PowerShell which explains how to get a list from the Lists Web Service.

In this post I'll explain the steps how to execute the UpdateListItem method.

First load the Visual Studio tools into your Powershell environment:

$env:VSINSTALLDIR="C:\Program Files\Microsoft Visual Studio 8"

$env:VCINSTALLDIR="C:\Program Files\Microsoft Visual Studio 8\VC"




$env:FrameworkDir=$(split-path $FrameworkPath -Parent)

$env:FrameworkVersion=$(split-path $FrameworkPath -Leaf)

$env:PATH="$env:VSINSTALLDIR\Common7\IDE;$env:VCINSTALLDIR\BIN;$env:VSINSTALLDIR\Common7\Tools;$env:VSINSTALLDIR\Common7\Tools\bin;$env:VCINSTALLDIR\PlatformSDK\bin; $env:FrameworkSDKDir\bin;$env:FrameworkDir\$env:FrameworkVersion;$env:VCINSTALLDIR\VCPackages;$env:PATH"


$env:LIB="$env:VCINSTALLDIR\ATLMFC\LIB; $env:VCINSTALLDIR\LIB;$env:VCINSTALLDIR\PlatformSDK\lib;$env:FrameworkSDKDir\lib;$env:LIB"


As explained in other posts, you can put this in your profile so that these tools are loaded each time you run Powershell.

Then execute the following commands to compile the Lists web service:

PS>wsdl http://[site url]/_vti_bin/Lists.asmx

PS>csc /t:library Lists.cs


Once this is done you can place the following statement in your profile so that it is executed each time you run Powershell:


Instantiate the lists web service:

PS> $listservice = New-Object Lists

You have to be authenticated to contact the web service:


My example uses a computers list:

powershell updatelistitems before

There is a mistake in the title of the second item, which need to be corrected.

The method you need to use to update a list item is the UpdateListItems. This method asks for 2 arguments: the name of the list and a batch query. First a string is build with the CAML needed to update the list item and then the UpdateListItems method is executed:

PS>$query = "<Batch OnError='Continue'><Method ID='1' Cmd='Update'><Field Name='ID'>2</Field><Field Name='Title'>Laptop 2</Field></Method></Batch>"

PS>$listservice.UpdateListItems("Computers", $query)

In fact the second argument of the UpdateListItems method is an xml node but if the query string contains valid xml, it is implicitly converted into an xml node before it is passed to the UpdateListItems method. If you want to explicitly use an xml node you can write the following:

PS>[xml]$query = "<Batch OnError='Continue'><Method ID='1' Cmd='Update'><Field Name='ID'>2</Field><Field Name='Title'>Laptop 2</Field></Method></Batch>"

And here is the result of the udpate:

powershell updatelistitems after

For more information about working with xml nodes, i refer to the post of Dan Sullivan.

As you can see I can use CAML in a lot of different ways :)

SharePoint PowerShell for Beginners - continued

In my first post on PowerShell for SharePoint developers I described how you could use the SPSite and SPWeb object to get information on its object model. In this post I will explain a few methods and properties of SPList.

Perhaps some of you know the featue I wrote last summer to view all properties of a SharePoint list. One of the things that you could inspect with this feature is the fields that are defined on the list. It shows you its name, internal name, id and data type. For more information see my post on the List Properties feature.

Today I will show you how to get this information by using PowerShell.

To get to the list you want to investigate, you need to perform some preliminary tasks:

Load the SharePoint assembly:


Open the SharePoint web:

$siteUrl = "your sharepoint site url"

$webName = "your webname"

$spSite = new-object Microsoft.SharePoint.SPSite($siteurl)

$spWeb = $spSite.OpenWeb($webName)

Instantiate the SharePoint list: 

$listName = "your listname"

$spList = $spWeb.Lists[$listName]

The fields of a list are defined in its Fields collection property. You could execute the following but it will return you a long list of all fields with all of its properties.


View column properties of a SharePoint list

So if your interested in only a few properties you could first inspect the metadata of the field object. A field is of type SPField. As we are not able to create a new object of type SPField from a default constructor, we will get an instance of the first field of the list. To view the different properties defined on SPField, execute the following statement:

$spList.Fields[0] | gm -membertype property

Following screenshot shows you the result.


So, if you want to view a few properties like display name, internal name, id and data type of all fields on a list, use the following syntax:

$spList.Fields | ft Title, InteralName, Id, TypeAsString

This returns you the requested data in table format. If not all information like f.e. the Id is completely visible, you could customize the columns of the table or use the fl formatter, which stands for format-list.

Add a column to a SharePoint list

The second task is to add a column to a SharePoint list. We will create a field with name "Extra column" and of type string. First of all, you need to know which method you need to execute on the list object and what are the arguments you need to specify.

Execute $spList.Fields.Add to get an overview of the different overloads of the Add mehtod:


As you can see, there are 2 overloads to add a new item to a SharePoint list. The overload we are going to use is the one that needs a column name, a field type and the required boolean. Execute the following:

$spList.Fields.Add("Extra column", "Microsoft.SharePoint.SPFieldType.Text", 0)

This returns you the following error:


As you can see in the error message returned by PowerShell, we didn't specify a correct value for the field type. If you tak a good look at the error message you will notice that PowerShell returns all possible values for that argument in the error message!

Specify boolean values as 0 and 1. If you pass the string "false", it is accepted as "different from false" and your field will be added as a required field.

Following statement will add the column to the list:

$spList.Fields.Add("Extra column", "Text", 0)

In addition, PowerShell returns you the internal name of the column you just added, i.e. Extra_x0020_column

Return to Internet Explorer and browse to the Settings Page of your list. You will see that the column is added to the list:


You can add it to the default view as you want.


In a next post I will explain how you can write a function that adds a column to a SharePoint list. You will be able to use this function for adding columns to each different type of list.

Update a SharePoint List with PowerShell

Some time ago I wrote an introductary post on Powershell and SharePoint 2007. Based on that post I recently got a question on how to run a WMI query to determine the IP address of a server and place the result in an IP field of a custom SharePoint list.

WMI stands for Windows Management Instrumentation and is the management infrasturcture for the Windows operating system. It also has an object model that you can use within a .NET application, but also from within PowerShell. A good starting point for using WMI within Powershell is the following tutorial:


To test my sample code of this blog post I created a custom list with name "Servers" and some additional fields like IPAddress and CPUSpeed. 

When opening the PowerShell command prompt, the first thing you have to do is load the SharePoint assembly:


To get an instance of a certain SharePoint list, you could use Powershell code like the following: 

$siteUrl = "your sharepoint site url"

$webName = "your webname"

$spSite = new-object Microsoft.SharePoint.SPSite($siteurl)

$spWeb = $spSite.OpenWeb($webName)

$spList = $spWeb.Lists["Servers"]

For querying WMI with PowerShell you use the get-wmiobject method. To get information about the computer processor you need to query the WMI namespace Win32_Processor. This namespace returns information about architecture, availability, name, cpu status, processor speed, devices, manufactures, etc.

$computername = "your computer name"

$processor = get-wmiobject Win32_Processor -computername $computername

To get information about the IP Adress you need to query the WMI namespace Win32_NetworkAdapterConfiguration. This namespace returns information about DHCP, the IP Address, DNS domain and service name of the queried server. 

$configs = get-wmiobject Win32_NetworkAdapterConfiguration -computername $computername

As for the calculation of the CPU speed, you can use the CurrentClockSpeed or the MaxClockSpeed property of the processor instance. As the data type of these properties are both unsigned integers, it will need some formatting before storing it in the SharePoint list:

"{0:0.##}" -f ($processor.MaxClockSpeed/1000) + " GHz"

As the CPU speed is also part of the processor name, you can also strip it out from the $processor.Name string.

Adding a new item to a SharePoint list using PowerShell is not so different from using the SharePoint object model from within a .NET application:

$spitem = $spList.Items.Add()

$spitem["Title"] = $config.HostName

$spitem["IPAddress"] = $config.IPAddress

$spitem["CPUSpeed"] = $cpuspeed


When using the above syntax, pay attention that you use the internal name of the field. F.e. if you see in the SharePoint list view a column with title "CPU Speed", chances are great that the internal name of this field is "CPU_x0020_Speed".

Now putting it all together, the sample code looks like the following. You can place it within a foreach loop to loop through a list of all servers.

$siteUrl = "http://yoursharepointsite"

$webName = "yourweb" 

$spSite = new-object Microsoft.SharePoint.SPSite($siteurl)

$spWeb = $spSite.OpenWeb($webName)

$listName = "your list name"

$spList = $spWeb.Lists[$listName]

$computername = "yourcomputername"

$processor = get-wmiobject Win32_Processor -computername $computername

$configs = get-wmiobject Win32_NetworkAdapterConfiguration -computername $computername

foreach ($config in $configs)


   $spitem = $spList.Items.Add()

   $spitem["Title"] = $config.ServiceName

   $ipaddress = $config.IPAddress | out-string

   $spitem["IPAddress"] = $config.IPAddress + "" 

   $spitem["CPUSpeed"] = "{0:0.##}" -f ($processor.MaxClockSpeed/1000) + " GHz"



 This is a screenshot of my test list after execution of previous code:


I hope this example is helpful. In case of questions, don't hesitate to post a comment.

SharePoint PowerShell for Beginners

This is a first post of a series on how you can use PowerShell on the SharePoint object model and its web services. This post will give you an introduction on how to work with the SPSite and SPWeb objects.

Open a PowerShell command prompt. As we are going to explore the SharePoint object model, you need to have PowerShell installed on a machine where you installed SharePoint.

The first step is to load the SharePoint assembly:


Instantiate an SPSite object:

$siteurl = "http://boske.litwareinc.com"

$mysite=new-object Microsoft.SharePoint.SPSite($siteurl)

Now you have a variable mysite, which contains an instance of type SPSite:

You can explore the content of the mysite variable by executing following command:


Or to have the information listed per page:

$mysite | more 

To list all the members of the site object:

$mysite | get-member | more 

or the shorter notation:

$mysite | gm | more 

You can also list only the properties of the site object:

$mysite | gm -membertype property | more 

To view the content of a property of the site object:


But this shows the whole content of all webs in the site collection. If you want to see only a few properties of each web, you could execute the following:

$mysite.AllWebs | format-table Url, ID, Name, AllUsers

or the shorter version

$mysite.AllWebs | ft Url, ID, Name, AllUsers

To get the webs ordered by the last time that the contents have been changed:

$mysite.AllWebs | sort LastItemModifiedDate | ft Url, ID, Name, LastItemModifiedDate  

To loop through all webs of the site object and show the web Url and the Title of each list:

$mysite.AllWebs | foreach { $_.Lists | ft $_.Url, Title}

You should see something similar like this:

You can also execute a methods of objects. Just for fun, I will show you how to execute a CAML query:

$mytestweb = $mysite.OpenWeb("testsite")

$listguid = new-object System.Guid("f0715075-5159-43d7-99b7-3897824fbbff")

$productlist = $mytestweb.Lists[$listguid]

$query = new-object Microsoft.SharePoint.SPQuery();

$query.Query = "<Where><BeginsWith><FieldRef Name='ProductModel' /><Value Type='Text'>Mountain</Value></BeginsWith></Where><OrderBy><FieldRef Name='ProductModel' /></OrderBy>"

$productlist.GetItems($query) | ft Name

You can also view all static members of the SPSite class:

[Microsoft.SharePoint.SPSite] | get-member -static 

If you want to execute a static member:


I hope you liked the introduction on how to use PowerShell in combination with the SharePoint object model.