How to get running workflow instances on listitems in powershell?

Question

I have craeted a powershell script to terminate all the corrupted workflows which are older than 4 hours. But I think it is not looking only for the running workflows. It is looking for all the workflow instances. Also the workflow instances which are already terminated. How can I change the script to just terminate workflowinstances which are running?

if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{ 
            Add-PSSnapin Microsoft.SharePoint.PowerShell
}           

###########################
# Main
###########################
cls


$spSite = Get-SPSite  "https://portal.myCompany.net/teams/siterequests/" # SiteCollection

$spWeb = $spSite.OpenWeb() # Evt. (sub)Web kan hier

for($i = 0; $i -le $spWeb.Lists.Count;$i++)
{
    foreach ($item in $spWeb.Lists[$i].Items)
    {
        foreach ($workflow in $item.Workflows)
        {
            if(($workflow | where {$_.InternalState -ne "Completed"}) -ne $null) #Error Occurred
            {
                $CurrentDateTime = Get-Date 
                $ExpiredDateTime = ($workflow.Created.ToLocalTime()).AddHours(4)

                if($CurrentDateTime -gt $ExpiredDateTime)
                {
                    Write-Host ("Terminate corrupted workflow. List: {0}, item {1}, workflow status: {2}, workflow started: {3}" -f $spWeb.Lists[$i].Title, $item.Title, $workflow.InternalState, $workflow.Created.ToLocalTime())  -BackgroundColor Black -ForegroundColor Yellow
                    [Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($workflow)
                }
            }
        }
    }



}

$spWeb.Dispose()
$spSite.Dispose()

This is for example on of the corrupted workflow instances. Like these I would like to terminate in my powershell script. BUT I would like to terminate only the corupted running instances. Not the instances which already terminated.

Answer 1

It will get all the workflows because you are looping through $item.Workflows without filtering. Also, I think there might be something wrong with your if logic. You have a $workflow in the context of the loop but then you pipe it to Where. There might be a reason you did it that way but I don't get it.

Perhaps try this in place of the inner loop and if:

# ommitted previous code. start from matching loop.
foreach ($item in $spWeb.Lists[$i].Items)
{
    $badWorkflows = $item.Workflows | Where-Object {($_.InternalState -eq "Running") -and ($_.Created.ToLocalTime().AddHours(4) -lt (Get-Date))}

    foreach ($badWorkflow in $badWorkflows)
    {
        Write-Host ("Terminate corrupted workflow. List: {0}, item {1}, workflow status: {2}, workflow started: {3}" -f $spWeb.Lists[$i].Title, $item.Title, $workflow.InternalState, $workflow.Created.ToLocalTime())  -BackgroundColor Black -ForegroundColor Yellow
        [Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($workflow)
    }
}

I don't have any good test data to really try this. I just kicked off a couple of approval workflows quickly to see what statuses they get.

I chose to filter by $_.InternalState -eq "Running" in my code but you should choose an appropriate state from these SPWorkflowState values. I suggest you inspect a few failing workflows manually using similar code to find what InternalState should be in that instance.

What is the scale of this issue? Are we talking 1 list in one site? If you break the problem down to the single example you have provided, run some powershell to inspect that items properties. This example will give you all the workflows for that item and it's InternalState:

$web = get-spweb "<url of web>"
$list = $web.lists.trygetlist("<list name>")
$item = $list.Items.GetItemById(<ID of item in your example>)
$item.Workflows | fl InstanceId, InternalState

Tweak your list name or item id to find an example of the failing workflow. Then update the filter based on the InternalState of that failed workflow. You can either keep or omit the filter by Created time depending how granular you need to get.

$badWorkflows = $item.Workflows | Where-Object { $_.InternalState -eq "<state of failed workflow>" }

Site Creation Script Do you have control over this script that tries to create a site? If so, perhaps you can change that so that when it errors, it doesn't need to be deleted.

Alternative: It seems inefficient to loop through every list in the web. There are many lists that are probably irrelevant in your case. If you want to try and get more efficient than looping through all the lists and all the items, look into SPSiteDataQuery. You construct your query using CAML. This of course depends on the scale of your issue.

Answer 2

The problem is in

$workflow | where {$_.InternalState -ne "Completed"}

You are trying to enumerate through $workflow object, wich is not the collection. So as Lance said the good way is to filter workflows:

$badWorkflows = $item.Workflows | Where-Object {($_.InternalState -eq "Running") -and ($_.Created.ToLocalTime().AddHours(4) -lt (Get-Date))}

or scip completed worflows:

foreach ($workflow in $item.Workflows)
{
    if($workflow.IsCompleted -eq $true)
    {
      continue
    }
    foreach ($task in $workflow.Tasks)
    {
        if($task["WorkflowOutcome"] -eq "Error")
        {
        #stop workflow
        }
    }
    $query=New-Object -TypeName Microsoft.SharePoint.SPQuery
    $query.Query = "<Where><And><Neq><FieldRef Name='WorkflowInstance' /><Value Type='Text'>"+$workflow.InstanceId.ToString("B")+"</Value></Neq><Eq><FieldRef Name='WorkflowInstance' /><Value Type='Text'>Error</Value></Eq></And></Where>"
    $errorItems = $workflow.HistoryList["WorkflowInstance"].GetItems($query)
    if($errorItems -ne $null -and $errorItems.Count -gt 0)
    {
       #stop workflow
    }
}

Answer 3

For each item, execute this to first get the "running" workflows:

$collection = New-Object Microsoft.SharePoint.Workflow.SPWorkflowCollection ($item, [Microsoft.SharePoint.Workflow.SPWorkflowState]::Running, [Microsoft.SharePoint.Workflow.SPWorkflowState]::None)

Then filter the collection using the criterion you mentioned above and loop through the filtered items. Hope this helps!