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.
cancelbutton
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.

param(
  [string]$siteCollectionUrl = $null, #"http://hostname",
  [array]$workflowAssociationNames = @(), # pass $null to cancel all
  [bool]$verbose = $true,
  [array]$workflowStatesToCancel = @(2,6,7)
  )

#Status  Value
#Not Started  0
#Failed on Start  1
#In Progress  2
#Error Occurred  3
#Canceled  4
#Completed  5
#Failed on Start (retrying)  6
#Error Occurred (retrying)  7
#Canceled  15
#Approved  16
#Rejected  17

Write-Host "Canceling workflows in the site collection..."

# Verify parameters
if([string]::IsNullOrEmpty($siteCollectionUrl))
{
  Write-Host "-siteCollectionUrl parameter is required" -F Red
  return
}
if($workflowAssociationNames -ne $null `
  -and $workflowAssociationNames.length -lt 1)
{
  $errMsg = "-workflowAssociationNames parameter must be provided. "
  Write-Host "$errMsg" -F Red
  return
}

# Iterate all webs
$site = Get-SPSite $siteCollectionUrl
$allWebs = $site.AllWebs
foreach($web in $allWebs)
{
  try
  {
    # Iterate all lists
    $lists = $web.Lists
    for($listIndex = 0; $listIndex -lt $lists.Count; $listIndex++)
    {
      $list = $lists[$listIndex]
      
      # Get the collection of workflow associations with running instances
      $was = @()
      foreach ($wa in $list.WorkflowAssociations)
      {
        if($wa.RunningInstances -gt 0)
        {
          $was += $wa
        }
      }
      
      # Check if the list has the workflow we want
      $checkList = $false
      $tempWorkflowAssociationNames = @()
      if($workflowAssociationNames -ne $null)
      {
        foreach($wa in $was)
        {
          if($workflowAssociationNames -contains $wa.Name)
          {
            $tempWorkflowAssociationNames += $wa.Name
          }
        }
        $checkList = $true
      }
      else
      {  
        foreach($wa in $was)
        {
          $tempWorkflowAssociationNames += $wa.Name
          $checkList = $true
        }
      }
      
      # Only investigate list if it has workflow associations
      if($checkList)
      {
        # Query for items that have a column for the workflow 
        # where the column value is not null or empty not is 
        # in the $workflowStatesToSkipCancel array.
        $spQuery = New-Object Microsoft.SharePoint.SPQuery
        $spQuery.ViewAttributes = "Scope='Recursive'"
        $spQuery.RowLimit = 2000
        $spQuery.ViewFieldsOnly = $true
        $spQuery.ViewFields = "<FieldRef Name='FileLeafRef'/>"
        
        # Map workflow association name to column name
        $failedToMapToColumns = $false
        $fieldInternalNames = @()
        foreach($wan in $tempWorkflowAssociationNames)
        {
          try
          {
            $field = $list.Fields.GetField($wan)
            $fieldInternalNames += $field.InternalName
          }
          catch [Exception]
          {
            #Write-Host "Failed to find field $wan on list $($list.Title)" -F Red
            $failedToMapToColumns = $true
          }
        }
        
        # Only attempt to query if we have a field to query against
        if($fieldInternalNames.Length -gt 0 -or $failedToMapToColumns)
        {
          # Build caml query to get items with running workflows
          if(!$failedToMapToColumns)
          {
            $camlFrags = @()
            foreach($fin in $fieldInternalNames)
            {
              foreach($state in $workflowStatesToCancel)
              {
                $camlFrags += "<Eq><FieldRef Name='$fin' />"
                $camlFrags += "<Value Type='WorkflowStatus'>$state</Value></Eq>"
              }
            }      
            $caml = GetNestedCaml $camlFrags "Or"
            $caml = "<Where>$caml</Where>"
            $spQuery.Query = $caml 
          }
          else
          {
            $spQuery.Query = ""
          }
          
          do
          {
            # Iterate all items with workflows that we wish to cancel
            $listItems = $list.GetItems($spQuery)
            $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
            for($itemIndex = 0; $itemIndex -lt $listItems.Count; $itemIndex++)
            {
              try
              {
                $item = $listItems[$itemIndex]

                $wfGuids = @()
                foreach ($workflow in $item.Workflows) 
                {
                  $wfpan = $workflow.ParentAssociation.Name
                  if($tempWorkflowAssociationNames -contains $wfpan)
                  {
                    $wfGuids += $workflow.InstanceId
                  }
                }
                
                foreach ($wfGuid in $wfGuids) 
                {
                  # Ensure that the workflow is in a 'running' state
                  $workflow = $item.Workflows[$wfGuid]
                  $wfState = [Microsoft.SharePoint.Workflow.SPWorkflowState]::Running
                  $isWfInState = ($workflow.InternalState -band $wfState) -eq $wfState
                  if($isWfInState)
                  {
                    #Cancel Workflows        
                    [Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($workflow)
                    if($verbose)
                    {
                      Write-Host "Workflow canceled: $($workflow.ParentAssociation.Name) on $($item.Name)"
                    }
                  }
                }
              }
              catch [Exception]
              {
                Write-Host "Error: $($item.ServerRelativeUrl) $($Error[0])" -F Red
              }
            }
          }
          while ($spQuery.ListItemCollectionPosition -ne $null)
        }
      }
    }
  }
  catch [Exception]
  {
    Write-Host "Error: $($web.Url) $($Error[0])" -F Red
  }
  finally
  {
    $web.Dispose()
  }
}
$site.Dispose()

Write-Host "DONE" -F Green





4 thoughts on “Cancel all workflows in site collection efficiently”

    1. The script is in this post. It is posted using a github gist. If you can’t see it, it may be because you are viewing this post via an RSS reader.

Leave a Reply

Your email address will not be published.