Cancel all workflows in site collection efficiently

Here you will find a script to cancel all list workflows (not site workflows) running in a given site collection in the most efficient manner possible (that I can think of) while still only using the SharePoint API. A better approach still would be to query the SharePoint workflows SQL table to identify the running workflows so as to avoid iterating all site collection webs and lists. Unfortunately there is no ‘GetAllRunningWorkflows(SPSite)’ method available via the API. I imagine that this approach should be satisfactory in the majority of cases though.
There are a number of posts on the web with code somewhat similar to this, at least with code that aims to achieve the same outcome. Of all the posts which I found they all performed this function in a very inefficiency manner, iterating the SPListItemCollection for every list in the site collection. This may be fine in many circumstances but I wanted something that would run faster with less strain on the server.

I have achieved this by checking for workflow associations on a list before iterating the items as well querying the list items and where a workflow column exists checking to see if the list item needs to be returned at all. When returning the list items, I am querying with ViewFieldsOnly so that less data is returned.

This script also accepts an optional parameter that specifies which workflow associations should be canceled if you are not looking to cancel all of the workflow associations but just those of a specific name.

NB: The script contains a reference to a help function GetNestedCaml which I have defined in a separate post which can be found here.

As the script sample is quite large I suggest clicking the ‘view raw’ link at the bottom of the sample to view it.

Dynamically nesting CAML query statements

Here is a short PowerShell function that can be used when you need to dynamically generate CAML queries with many logically joined statements. I actually adapted it from a C# implementation I wrote (which is probably more useful…) but as you can rewrite it in C# very easily I won’t bother bother posting it twice.

As CAML logical join operators (And, Or) can only compare two statements, when many statements need to be compared you must nest them which is what this function achieves. The $join parameter should be passed as “And” or “Or” and the $fragments parameter should be passed as an array of CAML statement strings such as:
@("<Eq><FieldRef Name='Title' /><Value Type='Text'>title</Value></Eq>", "<Eq><FieldRef Name='FileLeafRef' /><Value Type='Text'>name.docx</Value></Eq>")

# Define method for nesting caml query statements
function GetNestedCaml([array]$fragments, [string]$join)
    if ($fragments.Length -lt 1)
        return [string]::Empty
    elseif ($fragments.length -eq 1)
        return $fragments[0]
    elseif ($fragments.length -eq 2)
        return "<$join>" + $fragments[0] + $fragments[1] + "</$join>"
    $joinFrags = @()
     $baseJoinCount = [int][Math]::Floor($fragments.length / 2)
    for ($i = 0; $i -lt $baseJoinCount; $i++)
        $baseIndex = (2 * $i)
        $fragsToJoin = @($fragments[$baseIndex], $fragments[$baseIndex + 1])
        $joinFrag = GetNestedCaml $fragsToJoin $join
        $joinFrags += $joinFrag
    if ($fragments.length % 2 -ne 0)
        $joinFrags += $fragments[$fragments.length - 1]
    return GetNestedCaml $joinFrags $join

“Manage Permissions” Permission Level

This post talks about how to create a new permission level for assigning users the right to manage permissions while maintaining the principal of least privilege. In most scenarios users who can manage permissions are also given owner rights to the site. Of course a user who can manage permissions can give themselves owner rights, but by default I needed to create a SharePoint security group to which I could add users that would then be allowed to manage the permissions at sites they would otherwise have no access to.

Creating a permission level that gives users the ability to manage permissions most likely involves more than providing the ‘Manage Permissions’ base permission. If you want to allows users with this permission level to add users to SharePoint groups (or remove them) then you will also need to add the ‘Add List Items’ and ‘Delete List Items’ along with the ‘View Application Pages’ base permissions.

"Manage Permissions" permission level base permissions.
“Manage Permissions” permission level base permissions.

With all of these base permissions you can expect a user with otherwise no access to a site to be able to manage site, list, and item permissions as well as manage the membership of the SharePoint security groups of which they are a member. This last point assumes that the SharePoint security groups are configured such that “Who can edit the membership of this group?” is set to “Group Members”.

SharePoint group set-up
SharePoint group set-up

Another option is to only provide the “Manage Permissions” base permission and ensure that the security group is the group owner of all the groups which they require the ability to manage membership of. The downside of this is that you must then remove the ‘Owners’ group from this role.

I suspect this is a very rare case I’m describing/solving here, and I’m doubtful this post will ever help anyone. Please let me know if it did!


Link to “open in browser” with Office Web Apps

In SharePoint 2013, especially when on Office 365, you probably want to take advantage of Office Web Apps (OWA) to open Office documents (Word, Excel, PowerPoint, etc) in the browser.

EDIT: In SharePoint Online the best way to achieve this is by adding ‘web=1’ as a query string parameter to the file URL. E.g.

URL to 'Open in brower'
URL to ‘Open in brower’

A common scenario could be an implementation of the “Feedback (SharePoint 2010)” workflow which creates a task with accompanying email for many users. Some of these users may not be able to open Office documents on their client. Unfortunately, the default email content provides a link which only allows the user to download the document (which they are then unable to view) and provides no link back to the document library to find the document in a more manual fashion. In this particular scenario SharePoint Designer (SPD) is a very useful tool to update the email content with a link that allows users to take advantage of OWA. I am not going to go into how to update the workflow using SPD, I will only discuss the link URL format itself.

The URL format you are looking for is: <Site URL>/_layouts/15/WopiFrame.aspx?sourcedoc=<Doc URL>&action=default

Some things of note:

  • If you inspect the href of a document in a library you will notice that the sourcedoc parameter is not a URL but GUID. This GUID is NOT the unique ID of the document. Providing the unique ID of the document instead of the URL will fail (or potentially display another document).
  • You can provide either an absolute URL or a server relative URL for the source doc, and ensure it is encoded.

In case you are using SPD the link address you want is:
[%Workflow Context:Current Site URL%]/_layouts/15/WopiFrame.aspx?sourcedoc=[%Current Item:Encoded Absolute URL%]&action=default

EDIT 31/30/2015: I’ve been advised by a colleague that this only works when the Site URL and Doc URL are both relative to the same site. This appears to be true in SharePoint Online, however I am almost certain this was not an issue when I wrote this article (I no longer have access to that environment).


Outlining (tag collapse) not working with .master files

If you have stuggled to get outlining (Visual Studio’s ability to collapse/expand sections of a document) working when editing master pages there is a very simple solution. You will notice that some options such as line numbering, when toggled on/off for the HTML editor, are visible when editing master pages. However, there is something ‘special’ about master pages that prevents all the standard HTML editor settings applying (or functioning) correctly with them. As such, Visual Studio provides a Master Page Editior which solves these issues, yay!

Unfortunately, by default master pages are opened using the HTML editor rather than the Master Page editor. To get around this please take the following actions:

1. Right click a master page to open via the solution explorer:
2. Click ‘Open with…’
3. Select ‘Master Page Editor’
4. Click ‘Set as Default’
5. Click ‘Ok’

Open master pages in the Master Page Editor
Open master pages in the Master Page Editor

If you have been playing around under ‘Tools > Options > Text Editor’ and explicity assigning the master extension to the HTML editor (or anything similar) in a futile attempt to get this working, remember to undo your changes as they may interfere with lauching the Master Page Editor by default option.

TFS Query : Bugs ever changed by me

I debated as to whether to share this tip or not for the sole reason that I’m embarrassed. I just did something that I don’t believe I haven’t done years ago. TFS supports a “Was Ever” operator for work item queries. Using this you can very easily create a query to display all work items which you have ever interacted with. I find this very useful. See the screen shot for how I use it in the simplest case.

TFS work items that have ever been changed by me
TFS work items that have ever been changed by me

Thread-level access and discussion boards in SharePoint

Ever wanted to have a single discussion board with threads accessible only to certain audiences?

It was something that we wanted and I was very close to changing the IA model such that a new discussion board is created for each thread just in order to get around a simple permissions issue. I say simple because the solution is simple once you know what it is.


Imagine the scenario where users can see a discussion board but must only be able to contribute to particular threads based on their permission levels. The obvious solution is to give users (groups) contribute access to individual threads. My initial thought was that the ‘limited access’ provided at the list would be enough to allow users to reply to those threads at which they have contribute access. In fact, even if a user has read access on a discussion board, and contribute access to a given thread they will still be denied access when attempting to reply. With a little thought, it’s really quite clear why this doesn’t work. A thread is just a folder and a reply is actually adding a new item to the list.

The key here is the ‘View Application Pages’ base permission. This base permission is provided first with the contribute permission level. In order to make the above scenario a reality you must give users this base permission. This can be achieved by creating a new permission level (e.g. “View Forms”) and binding it with ‘All Authenticated Users’ to the discussion board. I found it most appropriate to perform this action with PowerShell, I can include the script in the post if someone requests it but I think it’s pretty straight-forward.

Good luck!

Deployment failure causing Get-SPSite to fail

I run a scripted deployment process each time new (SharePoint) solutions are ready for QA, or any environment for that matter. We script a series of commands, specifically:
Uninstall-SPSolution, Remove-SPSolution, Add-SPSolution and finally Install-SPSolution.

Before running these commands, along with a number of other checks, we perform Get-SPSite to ensure the site is available and fail early if need be. If our custom solution has not been deployed then viewing any page under any of the web application’s site collections fails with an exception due to a failure to find one of the solution assemblies. This is because we have custom membership and claims providers which are defined in the solution assembly and are referenced in the web.config for the web application. Despite this, any SPSite and SPWeb objects can be obtained safely via PowerShell as forms authentication is not taking place.

So I was surprised to find that the Get-SPSite check failing during deployment today with a failure to locate the assembly containing the membership and claims providers. I did not discover the root cause as to why this suddenly occurred but will outline what it took to fix it.

In the end I was able to ” Install-SPSolution -force ” to recover from this situation but not before stopping and starting the SharePoint Administration Service across all Web servers in the farm. The service was not stopped on any of the machines however the Install job was never completing despite the timer job history in Central Administration stating that the job had been successful. Upon restarting all this service the Install-SPSolution command would then complete.

Credit to Andreas’ blog for putting me in the right direction.

Scripting WSP deployment

I won’t take this opportunity to evangelise the benefits of scripted deployments, I’m going to assume that you already do it (as you should be!) and provide a tiny bit of script that will identify when your deployment doesn’t run quite so smoothly. Before I do, I’ll briefly mention my experience as to why a deployment may fail.

I have found that the by far the primary reason that a SharePoint full-trust deployment fails is that one or more of the assemblies being deployed is locked in the GAC and cannot be removed/replaced. An ISSRESET fixes this in the majority of cases (consider performing a remote ISSRESET across all the farm SharePoint servers as part of your deployment process. Note to self: future blog topic…) and in the remainder of cases stopping related services (v4 Timer, SSRS etc) on the affected server will release the assembly. The easiest way to identify the servers at which the deployment failed is via CA:

Central Administration > System Settings > Farm Management :: Manage farm solutions > "Your WSP"
Central Administration > System Settings > Farm Management :: Manage farm solutions > “Your WSP”

WSPs are deployed using a timer job. When performing deployment actions we need to wait on the timer job and upon it completing then verify the deployment status of the solution is as we expect. If we don’t wait for the action and ensure that it has run successfully we run this risk of not detecting a failed to deployment until we attempt to access the site. This can be a real time sink if we run lengthy scripts after deployment or are scripting the deployment a number of solutions in succession. The following snippet shows how easy it is to achieve this in PowerShell.

May your deployment failures be immediately detected.

Modifying content type field properties

A SharePoint site column has a number of properties which, as a developer, you may need to alter. These properties include Name, Type, ShowInEditForm, ShowInDisplayForm, Hidden etc. Some of these properties can be changed once at the site column level and the changes can be propagated to all lists in the site collection. This is surfaced in the UI for a selection of properties:

Using the SharePoint UI to propagate field property changes to all lists
Using the SharePoint UI to propagate field property changes to all lists

The same (with the ability to set many more properties) can be achieved with PowerShell (or, as always, in C#):

The properties that are not listed in the UI may not “update all list columns based on this site column” as you would expect if you are using content types. Content types have a collection of field references (or links) to the list columns which get updated, but the links themselves have their own set of properties which override those set for the list field. This means that if you run the PowerShell snippet above, any existing content types which reference that field will continue to show the field on edit forms. To get around this issue, one must update the content types individually. The PowerShell script below is an example of how you might achieve this.

This is all pretty obvious when you really think about it. It is a necessity to allow for the flexibility of content types on a list to handle list data in their own way.