Thursday, 11 September 2014

Filtering a SharePoint List View by Document Approval status

Just a quick one on creating SharePoint list views that filter results based on a workflow status column.

Scenario: SharePoint 2013, Nintex Workflow 2013, Document Library with a workflow attached (that runs on the documents).

The workflow status is recorded in the Document Approval column (static name, Document). 

The values of this column can be retrieved using PowerShell. In this example, I'm using CSOM to access the field values, by getting the field, and looking at the SchemaXml property:

$SourceWebUrl = ""            
$SourceListName = "Project Documents";            
$account = Read-Host -Prompt "Enter the account to use to query pages";            
$password =  Read-Host -Prompt "Enter the password to use to query pages" -AsSecureString
$credentials = New-Object System.Management.Automation.PsCredential($Account,$Password);

Add-Type -Path "C:\Temp\Microsoft.SharePoint.Client.dll";            
Add-Type -Path "C:\Temp\Microsoft.SharePoint.Client.Runtime.dll";            
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SourceWebUrl)            
$ctx.Credentials = $credentials            
$w = $ctx.Web            
$l = $w.Lists.GetByTitle($SourceListName)            
$fields = $l.Fields            
#Get the document approval field and check the SchemaXxml property            
$da = $fields.GetByInternalNameOrTitle("Document Approval")            

The values are:

<Field DisplayName="Document Approval" Type="WorkflowStatus" Required="FALSE" ID="{e7cfcdf7-6990-4a20-835c-83d64fbaf87a}" SourceID="{a777c58e-b89b-4f82-8c08-36721dd8ceeb}" StaticName="Document" Name="Document" ColName="nvarchar16" RowOrdinal="0" Version="154" WorkflowStatusURL="_layouts/15/WrkStat.aspx" ReadOnly="TRUE">
        <CHOICE>Failed on Start</CHOICE>
        <CHOICE>In Progress</CHOICE>
        <CHOICE>Error Occurred</CHOICE>
        <CHOICE>Failed on Start (retrying)</CHOICE>
        <CHOICE>Error Occurred (retrying)</CHOICE>     

To use these values in a List View, open your list view (or create a new list view) in SharePoint Designer. Then create a CAML query that filters on this field, using the (zero based) index of the field values to specify the field value to filter on. The field type needs to be Integer. 

For example, the following CAML query filters all documents that the current user has authored, that have a workflow status of Starting or In Progress;

                <FieldRef Name="Author"/>
                <Value Type="Integer">
                    <UserID Type="Integer"/>
                    <FieldRef Name="Document"/>
                    <Value Type="Integer">0</Value>
                    <FieldRef Name="Document"/>
                    <Value Type="Integer">2</Value>

Tuesday, 9 September 2014

Styling the UI Bootstrap (AngularJS) Datepicker in SharePoint

I'm working on a SharePoint project at the moment using AngularJS to build a kind of SPA (Single Page Application).

There are a number of controls I need on the page, one them being a calendar / date picker. The client wanted the same experience as the SharePoint datepicker. I looked at several options for a datepicker control, and settled on using the datepicker directive included with the UI Bootstrap.

UI Bootstrap contains a number of directives for UI components, written in AngularJS by the AngularUI team, including a datepicker.

UI Bootstrap components can be downloaded here: UI Bootstrap

This datepicker works nicely, and it's a native AngularJS directive. However, the styling uses CSS classes from Bootstrap CSS. This isn't a bad thing; I like Bootstrap CSS a lot. But if I attach Bootstrap CSS to SharePoint without modification, the CSS will impact the styling of the SharePoint site.

My solution was to extract the styles used by the calendar control, slightly modify them to make the selectors more specific, and then add them to my solutions CSS file.

I modified the styles to:
* Remove the glyphicons and replace them with images or text (to make the control like more like the SharePoint calendar control).
* Added an additional selector to many of the CSS classes, to ensure the styles only targeted the datepicker.
* Make the datepicker slightly smaller (smaller buttons, fonts, etc). This made it a little more like the SharePoint OOTB calendar (only it's better!)

The end result looks like this:
When expanded, it looks like this:

I used the developer tools in Google Chrome to inspect the classes the datepicker uses, and then extract them out into a separate file. I then wrapped the datepicker in a parent DIV, to which I applied a CSS class to use to target the datepicker with new styles from Bootstrap.

The HTML markup for the control (I'm using the highlighted CSS class to enable me to specifically target the datepicker with the Bootstrap CSS):

<div class="ebcs-calendar">
    <p class="input-group ebcscontrol-long-calendar">
        <input type="text" class="form-control ebcscontrol-date-input" datepicker-popup="{{rec.calendar.format}}" name="Endorser1DueDate" ng-model="rec.item.eBriefEndorser1DueDate" is-open="rec.calendar.calendars['Endorser1DueDate']" min-date="rec.calendar.minDate" max-date="'2030-12-31'" datepicker-options="rec.calendar.dateOptions" ng-required="true" close-text="Close" />
        <span class="input-group-btn">
            <button type="button" class="btn btn-default ecbs-calendar-button" ng-click="$event, 'Endorser1DueDate')"><i class="glyphicon glyphicon-calendar"></i></button>

The CSS Styles (I've highlighted the CSS selector used to specifically target the datepicker with the Bootstrap CSS).

Note: You can download the cut-down version of the Bootstrap CSS I used here:

<style type="text/css">
div.ebcs-calendar {
    font-family: "Helvetica Neue",Helvetica,Arial,sans-serif;
    font-size: 14px;
    line-height: 1.42857143;
    color: #333;
    background-color: #fff;
div.ebcs-calendar button,
div.ebcs-calendar input,
div.ebcs-calendar optgroup,
div.ebcs-calendar select,
div.ebcs-calendar textarea {
    color: inherit;
    font: inherit;
    margin: 0;
div.ebcs-calendar input[type="button"],
div.ebcs-calendar input[type="submit"],
div.ebcs-calendar input[type="reset"],
div.ebcs-calendar > button {
    align-items: flex-start;
    text-align: center;
    cursor: default;
    color: buttontext;
    padding: 2px 6px 3px;
    border: 2px outset buttonface;
    border-image-source: initial;
    border-image-slice: initial;
    border-image-width: initial;
    border-image-outset: initial;
    border-image-repeat: initial;
    background-color: buttonface;
    box-sizing: border-box;

The snippet of styles below is where I've replaced the glyphicons with the default SharePoint calendar image, and some text for the backwards / forwards text.

<style type="text/css"
.glyphicon-calendar:before {
    content: " ";
    background-image: url('/_layouts/15/images/calendar_25.gif');
.glyphicon-chevron-left:before {
    content: "<";
    font-weight: 900;
    font-style: oblique;
.glyphicon-chevron-right:before {
    content: ">";
    font-weight: 900;
    font-style: oblique;
    background: url("/_layouts/15/images/calendar_25.gif") no-repeat scroll 0 0 transparent;
    border: 0;
    background: url("/_layouts/15/images/calendar_25.gif") no-repeat scroll 0 0 transparent;
    -webkit-box-shadow: none;
    box-shadow: none;

Monday, 8 September 2014

Kill all of the Processes Accessing a Microsoft SQL Server Database

I had an issue today trying to take a Microsoft SQL Server database offline (for a SharePoint Search Service). It was taking a very long time, and I was getting impatient!

Because there were still connections to the database, the command to take it offline line wouldn't complete. What a pain!

Time to kill all of the processes with open connections to the database. I'd previously taken the SharePoint Search Service offline, so I wasn't worried about forcefully closing connections.

I used sp_who2 to return a list of processes accessing the Search Service database (called SvcApp_Search_Admin). I saved this to a temporary table (to filter and group the results), and then used a cursor to kill all the processes associated with the database.

DROP TABLE #sp_who2
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
INSERT INTO #sp_who2 EXEC sp_who2
Select * from #sp_who2 where DBName = 'SvcApp_Search_Admin'
Select SPID from #sp_who2 where DBName = 'SvcApp_Search_Admin' Group By SPID
DECLARE @dbname NVARCHAR(1000)
DECLARE c CURSOR FOR Select distinct(SPID) as Int,DBName from #sp_who2 where DBName = 'SvcApp_Search_Admin'
FETCH NEXT FROM c INTO @spid,@dbname
    PRINT ' Killing Connection to: ' + @dbname + ' with ID ' + CAST(@spid as nvarchar)
    declare @sqls nvarchar(50)=''
    SELECT @sqls = 'kill ' + CAST(@spid as varchar(20))+';'
    FETCH NEXT FROM c INTO @spid,@dbname

I borrowed the idea of saving the results from sp_who2 into a temporary table from SQLMatters, here:

Friday, 22 August 2014

Creating a Report of Broken Web Pages in SharePoint

Recently I presented a session at SharePoint Saturday Melbourne on Being Crafty with PowerShell. One of the examples was how to audit all the aspx pages in a large farm. I want to share the first part of that script here now.

The example came from a large migration project I was working on. I needed to give the client confidence site pages where working ok, before business users started testing. The challenge was:

1. How to check a large farm with tens of thousands of pages
2. How to determine if the page is ok?

I addressed the issue using a PowerShell script that did the following for each page in the farm:

1. Hit the page, and get the HTTP response code
2. If the response code was 200, then download the page as HTML text, and check the contents for an occurences of "Correlation Id"
3. Log the results in a custom PSObject that could be saved and analysed later

I'll add the full script in a follow up post. In this post, I just want to demonstrate how to achieve these objectives for a single page.

Step 1. Create a custom PSObject to store the results.

For each page that I hit, I'm going to store the results into one of these objects. Each object will then be added to a collection (or an array). The object will record the URL's, page name, HTTP response code and if the page contains "Correlation Id".

$PageInfo = New-Object PSObject            
$PageInfo | Add-Member -MemberType NoteProperty -Name "WebUrl" -value ""            
$PageInfo | Add-Member -MemberType NoteProperty -Name "PageUrl" -value ""            
$PageInfo | Add-Member -MemberType NoteProperty -Name "Response" -value ""            
$PageInfo | Add-Member -MemberType NoteProperty -Name "ResponseCode" -value ""
$PageInfo | Add-Member -MemberType NoteProperty -Name "MatchFound" -value ""

Step 2. Get the response code for the page.

#url to check            
$urlToCheck = ""            
# Create a credential object that can be used to authenticate against SharePoint            
$credentials = Get-Credential -UserName $env:USERNAME -Message "Enter credentails for SharePoint"            
# Use the System.Net.WebRequest class to create a web request            
$wrq = [System.Net.WebRequest]::Create("");

# Set the Credentials property of the WebRequest object            
$wrq.Credentials = $credentials;            
# The GetResponse method returns a System.Net.WebResponse object, which has property called Status code.            
$wrp = $wrq.GetResponse()
# One thing to note, is that if the request fails (e.g. with HTTP 401), an exception is raised. So you need to capture the exception, and then use the Response property of the Exception (System.Net.WebException) to get the status code            
try {            
    #GetResponse() returns a System.Net.WebResponse object            
 $wrp = $wrq.GetResponse()            
} catch [System.Net.WebException] {            
    #If an exception was thrown, you can get the System.Net.WebResponse object from the Exceptions Response property            
 $wrp = $_.Exception.Response            
#Get the status code            
# The StatusCode is an enum, and can be cast to an Int to get the HTTP response number            

Step 3. If the page returned an HTTP 200 response, download the pages contents as text (HTML) and check it for the presence "Correlation Id"

if(([int]$wrp.StatusCode) -eq 200)            
    # The System.Net.WebClient class has a nice function called DownloadString(). We'll call it, passing in the same url, then check to see if the string returned contains "Correlation Id"            
    $wc = New-Object System.Net.WebClient;            
    # Reuse the credential object we created earlier            
    $wc.Credentials = $credentials;            
    # Store the downloaded string into a variable            
    $content = $wc.DownloadString($urlToCheck);             
    # To find if the page has an error, parse the html content looking for the text "Correlation Id"            
    $content.Contains("Correlation Id");            
    # Finally, dispose the WebClient object            

Step 4. Put all the information into the custom PSObject we created, and add it to an array.

# Create a instance of the object            
$pi = $PageInfo | Select-Object *            
# Set the properties of the custom object (like you set properties on any other object)            
$pi.WebUrl = $Web.Url;            
$pi.PageUrl = $urlToCheck;            
$pi.Response = $wrp.StatusCode            
$pi.ResponseCode = [int]$wrp.StatusCode            
$pi.MatchFound = $content.Contains("Correlation Id");            
#Print the contents            
#Create an array to store the object(s) in            
$results = @();            
#Add the current custom object to the array.             
$results += $pi;

Wednesday, 25 June 2014

Script Creating SharePoint User MySites using PowerShell

While working on a large migration project (SharePoint 2010 to SharePoint 2013), I had a requirement to script the creation of user MySites for hundreds of users. I knocked this little PowerShell function together to do just that!

Function Create-MySite            
  [Parameter(Mandatory = $True,Position=2,valueFromPipeline=$true)][String]$Username,            
     [Parameter(Mandatory = $True,Position=1)][String]$MySiteRootURL              
 $site=new-object Microsoft.SharePoint.SPSite($MySiteRootURL);            
  $serviceContext = Get-SPServiceContext $site;            
  $upm = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($serviceContext);             
  if($upm.UserExists($Username) -eq $false)            
   Write-Host "User $Username was not found in the profile store." -f yellow;            
  $userProfile = $upm.GetUserProfile($Username);            
  if($userProfile.PersonalSite -eq $Null)            
   Write-Host "Creating MySite for user $Username" -f darkyellow;            
   Write-host "Successfully created MySite for user $Username" -f green;            
   Write-Host "User $Username already has a MySite." -f darkgreen;            
  Write-Host "Encountered an error creating a MySite for user $Username. Error:"$_.Exception -f Red;            

You can call the function like this (for a single user):

#Example - Create a MySite for user  "tonyj"            
Create-MySite -MySiteRootURL "" -Username "tonyj"

Or call it like this, for a batch of users:

#To Create for an array of users            
$users = @('tonyj','bobh','markf','billd')            
$users | Foreach {Create-MySite -MySiteRootURL "" -username $_}

Monday, 16 June 2014

Recursively Disabling a SharePoint Feature throughout a Farm

I'm working on a large SharePoint migration project at the moment. It's required quite a bit of PowerShell to automate tasks that ensure the project is a success.

One of those tasks was to find everywhere a feature was activated through the farm, and optionally disable the feature. Whenever we disable a feature throughout the farm, we need to keep a report of all the places the feature was previously enabled (there's about ten thousand webs in this farm).

So I wrote a script, that does exactly that! The script takes a feature-id, an SPWeb and two switches as parameters, and returns an object collection that records details about the feature instances disabled (feature id and display name, the web url, the feature status and the time it was last activated).

The two parameters, -Recurse, and -Report, control the scripts function. The -Recurse parameter tells the script to check all of the input webs sub-webs. The -ReportOnly parameter tells the script to... well, create a report of everywhere the feature is currently active, without actually disabling it!

Here's the function:

function Outst-SPFeature            
            [parameter(Mandatory=$true,Position = 0,valueFromPipeline=$true)][Microsoft.SharePoint.SPWeb]$Web,            
 #Define an object that can store the features properties             
 $FeatureInfo = New-Object psobject            
 $FeatureInfo | Add-Member -MemberType NoteProperty -Name "Id" -value ""
 $FeatureInfo | Add-Member -MemberType NoteProperty -Name "DisplayName" -value ""
 $FeatureInfo | Add-Member -MemberType NoteProperty -Name "WebUrl" -value ""
 $FeatureInfo | Add-Member -MemberType NoteProperty -Name "Status" -value ""
 $FeatureInfo | Add-Member -MemberType NoteProperty -Name "TimeActivated" -value ""             
 #create an empty array variable            
 $matches = @();            
 Write-Host "Checking web"$Web.Url -f Green -b Yellow;#pipe the list of features ($web.Features) to the Where-Object cmdlet (aliased as '?'), and look for a feature with the same FeatureId that was passed to the script as a parameter            
 $f = $Web.Features | ?{$_.DefinitionId -like $FeatureId}            
    #If the feature was found, $f won't be null            
 if($f -ne $null)            
        #If the feature was found, check the SPFeature.Definition.Status property, to see if the feature is activated (online)            
  if($f.Definition.Status -eq "Online")            
   Write-Host "Disabling feature,"$f.Definition.DisplayName -f Green            
            #If the feature is online, then record the features details in a new custom object (this will get returned at the end of the function)            
   $fm = $FeatureInfo | Select-Object *;            
   $fm.Id = $f.Definition.Id;            
   $fm.DisplayName = $f.Definition.DisplayName;            
   $fm.WebUrl = $Web.Url;            
   $fm.Status = $f.Definition.Status;            
   $fm.TimeActivated = $f.TimeActivated            
   #Add the custom object to the $matches array (a list that will contain a custom object for each instance of a feature found)            
   $matches += $fm;            
    #Check the -ReportOnly switch. If the switch wasn't passed to the function, it will be false.             
   if($ReportOnly -eq $false)            
                #Disable the feature            
    Disable-SPFeature -Identity $f.Definition.Id -Url $Web.Url -Confirm:$false
  #Check the -Recurse switch. If the switch wasn't passed to the function, it will be false. Also check if the web has any sub-webs. If both of these checks are true, then check the sub-webs            
 if($Recurse -eq $true -and $Web.Webs.Count -gt 0)            
  Write-Host "Checking sub webs" -f Blue            
  foreach($sw in $Web.Webs)            
  {   #Here we want to call the same function again, against the sub-webs. The $matches variable is incremented with the results returned from the new call to the function.             
   if($ReportOnly -eq $false)            
    $matches += Outst-SPFeature -Web $sw -FeatureId $FeatureId -Recurse            
    $matches += Outst-SPFeature -Web $sw -FeatureId $FeatureId -Recurse -ReportOnly            
 #The $matches array (containing the list that contains custom objects for each instance of a feature found) is returned.            
 return $matches;            

And this is how it can be called:

#Run this command to disable the feature on all webs in all webapplications.            
$disabledfeature = Get-SPWebApplication | Get-SPSite -Limit All | Get-SPweb -Limit All |Foreach {Outst-SPFeature $_ -FeatureId "a5557f3e-102c-401e-9eae-1e7fcf4340d0" -Recurse}

And this is how you export the results to an xml file that can reloaded at a later time:

#Run this command to disable the feature on all webs in all webapplications, and save a report of all the feature instances that were disabled to C:\Temp\DisabledFeatureReport.xml
Get-SPWebApplication | Get-SPSite -Limit All | Get-SPweb -Limit All |Foreach {Outst-SPFeature $_ -FeatureId "a5557f3e-102c-401e-9eae-1e7fcf4340d0" -Recurse} | Export-Clixml -Path C:\Temp\DisabledFeatureReport.xml

Wednesday, 4 June 2014

Renaming a HNSC (Host Named Site Collection) in a SharePoint Farm with Multiple Web Applications

Recently I've been involved in migrating a large SharePoint farm from SharePoint 2010 to SharePoint 2013. The farm has multiple web applications, and HSNC's have been created in different web applications. Part of the migration has required us to rename a number of HNSC's (Host Named Site Collections) for the development, testing and staging environments.

I'll start by saying, Microsoft recommends using one web application (SPWebApplication) per farm if you are planning on using HNSC (host named site collections). But that doesn't stop people using HSNC's in a farm with multiple SharePoint web applications.

So the short of this is, if you have a SharePoint farm with multiple web applications, and you want to rename a HNSC within one of those web applications, you have to add a new URL (using New-SPSiteUrl) to an "unused" zone, and then manually add the IIS bindings into SharePoint Web Applications IIS site, for each server in the farm that accepts requests for the site collection. To see some PowerShell on how to do this, skip to the bottom of the article!

If you google or bing "rename SPSIte", you'll get a number of articles and posts about renaming both "path based" and "host named" site collections. There are a few PowerShell ways for achieving this, including Rename-SPSite, and SPSite.Rename(newurl).

In our case, none of these approaches worked. Renaming "path based" site collections works fine, and renaming "host named" site collections also works fine... as long as you have configured your farm in the supported way for HNSC's (with one web application). The environment we were upgrading had many web applications, and HNSC's in more than one of those web applications.

So after a bit of investigation, we (and by we, I mean a colleague of mine, Elaine Van Bergen) pointed out that IIS has to know which IIS site it should send the request to(there is always a single IIS site behind each SharePoint web application). Each IIS site has bindings (for domain names) that tell IIS which urls the site will serve requests for.

After checking the IIS bindings on the IIS site behind one of the SharePoint web applications where we had added a new URL for a HNSC, I found there was no binding there for the new URL (domain) I'd added to the HNSC.

If that doesn't already make sense, hopefully the following example explains it clearer.

For the example, we have an IIS site listening on port 80, with a binding for This IIS site will respond to any requests sent to the address* (e.g. We also have another IIS site with a binding for Both of these IIS Sites have SharePoint web applications behind them, and each web application has a single SharePoint site collection. The site collections are host named site collections.

In this scenario, we have a SharePoint web application for the, with an IIS site behind it listening on port 80, with the binding Web requests for* will be accepted by this IIS site, which will pass it onto SharePoint to look up the site, and respond to the request.

If a new URL, "", is added to the "intranet" zone of the web application, what would happen? When the request is received by the web server, IIS looks for an "IIS" site that has a binding for In our example there are no sites with this binding, but there is a site with a binding for "*" (anything), so IIS will send the request to that "IIS" site (Default Website). Because the Default IIS site doesn't actually have any sort of site behind it, an HTTP 404 is returned.

Once a binding for is added to the IIS site, requests for will now be responded to by the IIS site with the binding. If this is an IIS site with a SharePoint web application behind it, the request is then handed off to SharePoint to fulfil.

So, getting back to the original problem, how do we rename a HNSC on a SharePoint farm with multiple web applications (without doing a backup-spsite, delete-spsite, restore-spsite)? It's not ideal. The way we found was to use Add-SPSiteURL cmdlet to add the new URL to a new zone (one other than the default zone). After doing this, we needed to manually add an IIS binding for the SharePoint web application's IIS website, on all the SharePoint WFE's (web servers), to tell the IIS site to respond to requests for the new URL.

Building on our example, the commands for this looks like this:

#Add a new URL for the HNSC            
$site = Get-SPSite            
Add-SPSiteUrl $site -URL -Zone Intranet            
#Add the IIS binding, for on port 80 (remember, this will need to be performed on each WFE)            
Import-Module "WebAdministration"            
New-WebBinding -Name $site.WebApplication.Name -Protocol http -Port 80 -IPAddress "*" -HostHeader

It's not pretty, but it is a workaround if you find yourself in this position.

As I said at the start, this is probably one reason why Microsoft recommends using HNSC with one web application. If you are using one web application, you can configure it with a binding of "*". Doing this ensures the IIS site responds to all requests, and hands them off to SharePoint to satisfy. SharePoint contains the list of HNSC's and takes care of responding to valid requests.