<#
.SYNOPSIS
Cancel running Nintex workflows.
.DESCRIPTION
Cancel running Nintex workflows.
Workflow instances which are still running for deleted webs are saved to a CSV file.
.PARAMETER DBServer
Specifies the name of the database server where the Nintex database exists. If omitted, the local connection will be used.
.PARAMETER DBName
Specifies the name of the Nintex content database to target. If omitted, all content databases will be in scope.
.PARAMETER LastActivityDate
All running workflows with a last activity date before this, will be canceled.
WARNING!!! If omitted, the current date will be used and all running instances will be terminated.
.PARAMETER LogOnly
Include this switch to run in log-only mode to simulate the actual cancellations.
.NOTES
File Name: Cancel-NTXRunningWorkflows.ps1
Version : 1.0
.EXAMPLE
PS C:\> .\Cancel-NTXRunningWorkflows.ps1 -DBServer SHPDB -DBName NTX_PRD_Content -LastActivityDate "2016-01-01 12:00:00"
Description
-----------
This script will cancel all running workflows which have a last activity date on January 1st, 2016.
#>
[CmdletBinding()]
param(
[Parameter(Position=0,Mandatory=$false,ValueFromPipeline=$false)]
[string]$DBServer,
[Parameter(Position=1,Mandatory=$false,ValueFromPipeline=$false)]
[string]$DBName,
[Parameter(Position=2,Mandatory=$false,ValueFromPipeline=$false)]
[datetime]$LastActivityDate = (Get-Date),
[Parameter(Position=3,Mandatory=$false,ValueFromPipeline=$false)]
[switch]$LogOnly
)
Add-Type -TypeDefinition @"
public enum LogType
{
SUCCESS,
FAILURE,
INFO,
ERROR
}
"@
###############################
# Functions
###############################
function Write-Log([string]$message, [LogType]$MessageType)
{
$TimeStamp = $([DateTime]::Now.ToString('yyyy/MM/dd HH:mm:ss'))
$message = "$TimeStamp - $MessageType : $message"
Out-File -InputObject $message -FilePath $global:LogFile -Append
}
function Process-Database($server, $database, $parameters=@{})
{
$global:LogFile = "$ScriptLogLocation\RunningWFLCleanup_$($database)_" + $([DateTime]::Now.ToString('yyyyMMdd_HHmmss')) + ".log"
if ($LogOnly)
{
Write-Log -message "!!!!!!!! LOG-ONLY Mode activated !!!!!!!!" -MessageType INFO
}
Write-Log -message "Cleanup running instances in database '$database'" -MessageType INFO
Write-Log -message "Last Action Date : $LastActivityDateUTC" -MessageType INFO
Write-Progress -Activity "Processing Data in $database" -Status "Querying database... (might take a while)" -PercentComplete 95
try
{
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$command = New-Object System.Data.SqlClient.SqlCommand($query,$connection)
$parameters.Keys | % {
$command.Parameters.AddWithValue("@$_",$parameters[$_]) | Out-Null
}
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter
$adapter.SelectCommand = $command
$adapter.SelectCommand.CommandTimeout = 300; # Increase this timeout if the query takes too long to complete
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) >> $null
$connection.Close()
$data = $dataset.Tables[0]
Do-Cleanup -database $database
}
catch
{
Write-Log -message "Error processing database ($($_.Exception.InnerException.Message))" -MessageType ERROR
}
}
function Do-Cleanup($database)
{
$deletedSiteInstances = 0
$deletedItemInstances = 0
$instancesDeletedWebs = @()
$nbrRows = $data.Rows.Count
$CSVOutput = $global:ScriptLogLocation + "\$($database)_instancesDeletedWebs.csv"
if ($nbrRows -gt 0)
{
Write-Log -Message "# Running instances : $($nbrRows)" -MessageType INFO
if ((Get-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue) -eq $null)
{
Add-PSSnapin Microsoft.SharePoint.PowerShell
}
$i = 0
$percComplete = 0
$percRow = 100 / $nbrRows
foreach ($row in $data.Rows)
{
$i++
$percComplete += $percRow
Write-Progress -Activity "Processing Data in $database" -Status "Record $i/$nbrRows" -PercentComplete $percComplete
if ($row.SiteID -ne $null)
{
$site = Get-SPSite $row.SiteID -ea SilentlyContinue
if ($site -ne $null)
{
$web = $site.AllWebs | ? {$_.Id -eq $row.WebID}
if ($web -ne $null)
{
if ($row.ListID -ne "00000000-0000-0000-0000-000000000000")
{
# List workflow or Item workflow
$list = $web.Lists | ? {$_.Id -eq $row.ListID}
if ($list -ne $null)
{
if ($row.ItemID -gt 0)
{
$item = $null
try
{
$item = $list.GetItemById($row.ItemID)
$wfl = $item.Workflows | ? {$_.InstanceID -eq $row.WorkflowInstanceID}
if ($wfl -ne $null)
{
if (!$LogOnly)
{
# Workflow instance found, cancel it
[Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($wfl)
}
$deletedItemInstances++
Write-Log "Workflow Cancelled (Web: $($web.Url), List: $($list.Title), Item: $($item.ID), Instance: $($row.WorkflowInstanceID))" -MessageType SUCCESS
}
else
{
# Workflow instance not found
Write-Log "Workflow instance not found (Web: $($web.Url), List: $($list.Title), Item: $($item.ID), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE
}
}
catch
{
Write-Log "Item not found (Web: $($web.Url), List: $($list.Title), Item: $($row.ItemID), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE
}
}
}
else
{
Write-Log "List not found (Web: $($web.Url), List: $($row.ListID), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE
}
}
else
{
# Site workflow
$wfl = $web.Workflows | ? {$_.InstanceID -eq $row.WorkflowInstanceID}
if ($wfl -ne $null)
{
if (!$LogOnly)
{
# Workflow instance found, cancel it
[Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($wfl)
}
$deletedSiteInstances++
Write-Log "Workflow Cancelled (Web: $($web.Url), Instance: $($row.WorkflowInstanceID))" -MessageType SUCCESS
}
else
{
# Workflow instance not found
Write-Log "Workflow instance not found (Web: $($web.Url), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE
}
}
$web.Dispose()
}
else
{
Write-Log "Web not found (Site: $($site.Url), Web: $($row.WebID), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE
$instancesDeletedWebs += $row.WorkflowInstanceID
}
$site.Dispose()
}
else
{
Write-Log "Site not found (Site: $($row.SiteID), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE
}
}
}
if ($instancesDeletedWebs.Count -gt 0)
{
$instancesDeletedWebs | ConvertTo-Csv -NoTypeInformation | Select -Skip 1 | % {$_ -replace '"',''} | Out-File $CSVOutput
}
Write-Log -Message "Cleanup finished" -MessageType INFO
Write-Log -Message "# canceled site instances : $deletedSiteInstances" -MessageType INFO
Write-Log -Message "# canceled item instances : $deletedItemInstances" -MessageType INFO
}
else
{
Write-Log -Message "# Running instances : 0" -MessageType INFO
Write-Log -Message "No cleanup needed" -MessageType INFO
}
}
###############################
# Parameters
###############################
$global:ScriptLogLocation = "D:\Logs\NintexCleanup" # The location where the output of the script is saved
$global:LogFile = ""
###############################
# Main Script
###############################
if (!$PSBoundParameters.ContainsKey('LastActivityDate'))
{
Write-Host "WARNING! The -LastActivityDate parameter was not specified!! All running instances will be terminated. Are you sure? (Default is No)" -ForegroundColor Yellow
$readhost = Read-Host " (Y/N) "
switch ($readhost)
{
Y {$cancelRun = $false}
N {$cancelRun = $true}
Default {$cancelRun = $true}
}
}
else
{
$cancelRun = $false
}
if ($cancelRun) {Write-Host -ForegroundColor Red "Cancelling execution!";break}
$LastActivityDateUTC = $LastActivityDate.ToUniversalTime().ToString("yyyy-MM-dd HH:MM:ss")
$query = @"
SELECT SiteID,
WebID,
ListID,
ItemID,
WorkflowID,
WorkflowInstanceID,
WorkflowName,
WorkflowInitiator,
StartTime,
LastActionDate,
DATEDIFF(s, StartTime, getutcdate()) Duration,
DATEDIFF(s, LastActionDate, getutcdate()) IdleTime,
(ExpectedDuration * 60) as ExpectedDuration,
TaskListID
FROM dbo.WorkflowInstanceView
WHERE State = 2
AND CONVERT(VARCHAR(19),LastActionDate,120) <= @LastActivityDateUTC
ORDER BY SiteID,WebID,ListID,ItemID
"@
if ([string]::IsNullOrEmpty($DBServer) -or [string]::IsNullOrEmpty($DBName))
{
# No Database or DBServer was specified in the startup params. Process all Nintex content databases
[System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow") | Out-Null
$configdb = [Nintex.Workflow.Administration.ConfigurationDatabase]::GetConfigurationDatabase()
if ($configdb -ne $null)
{
foreach ($contentdb in $configdb.ContentDatabases)
{
Process-Database -server $contentdb.SqlConnectionString["Data Source"] -database $contentdb.SqlConnectionString["Initial Catalog"] -parameters @{LastActivityDateUTC="'$LastActivityDateUTC'"}
sleep -Seconds 5
}
}
}
else
{
# Database and server have been specified at startup. Process only that database.
Process-Database -server $DBServer -database $DBName -parameters @{LastActivityDateUTC="'$LastActivityDateUTC'"}
}