Working with workflows in SharePoint is always a challenging task. Especially when you are an administrator and people start complaining about performance issues, workflows that don’t start or resume, and so on. Using Nintex Workflow doesn’t change anything, but it gives you a bit more leverage.
The first thing I always do, is try to get some insights in the size of the issue. So, we’re talking about data. How much workflow data is involved. When Nintex is involved, there are 2 places where I’m going to look:
- Workflow History Lists (SharePoint)
- WorkflowProgress table (SQL Database)
Nintex has an article on both topics which I can definately recommend.
https://support.nintex.com/SharePoint/Forms/SharePoint_Maintenance_for_Nintex_Workflow
This article links to a PDF which explains how to maintain the WorkflowProgress table. You can find that PDF here.
In this PDF, you will find a SQL query which queries this table and the WorkflowInstance table to give you an insight in the sheer amount of data which is stored and which might be the root cause of your issues with workflows.
Just for you lazy people, here’s the query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT I.WorkflowName, I.WorkflowInstanceID, I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State, COUNT(P.WorkflowProgressID) as ActionCount FROM WorkflowInstance I INNER JOIN WorkflowProgress P ON I.InstanceID = P.InstanceID GROUP BY I.WorkflowName, I.WorkflowInstanceID, I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State order by COUNT (P.WorkflowProgressID) DESC |
This gives you a lot of information which you can use in your remediation strategy.
Just export that information to a CSV and load it in Excel and you can have the following information at hand in no time.
This is a table which gives you, per Nintex content database, the amount of records in the workflowProgress table (ActionCount) and the number of workflow instances involved. And it splits up this information for the different states workflows can be in (running, completed, canceled, error). Whoever came up with the concept of Pivot tables… thank you! 🙂
While this query is great for getting that information and report on it, it’s not that useful for people who are responsible for creating or maintaining those workflows. Look at the query. It gives you the site, web, list and even item that’s involved. But those are the ID’s. You still need to resolve these to a URL or title of a list to be useful for anyone who’s not an administrator on SharePoint and knows his way around in PowerShell.
You could customize this query and include the SharePoint content databases in there to get you the needed information but you know you shouldn’t touch those databases in SQL! So, that’s not an option.
I decided to make my life a little less complicated and use PowerShell to solve it.
The script below is going to execute the query above (with some minor adjustments to get some extra information) and after getting the results, it will get the corresponding URL’s for site and web, and the title of the list (if needed). All of this information is exported to a CSV.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 |
<# .SYNOPSIS Get Action Count and instance statistics for a Nintex Content Database .DESCRIPTION Get Action Count and instance statistics for a Nintex Content Database. .NOTES File Name: Get-WorkflowProgress.ps1 Author : Bart Kuppens Version : 1.0 .PARAMETER DBServer Specifies the name of the database server where the Nintex content database is located. .PARAMETER DBName Specifies the name of the Nintex content database. #> [CmdletBinding()] param( [parameter(Position=0,Mandatory=$true,ValueFromPipeline=$false)] [string]$DBServer, [parameter(Position=1,Mandatory=$true,ValueFromPipeline=$false)] [string]$DBName ) function Run-SQLQuery ([string]$SqlServer, [string]$SqlDatabase, [string]$SqlQuery) { $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDatabase;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $Sqlquery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() $DataSet.Tables[0] } $query = @" SELECT I.WorkflowName, I.WorkflowInstanceID, CONVERT(VARCHAR(24), I.StartTime, 120) as StartTime, I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State, CONVERT(VARCHAR(24),MAX(P.TimeStamp),120) as LastActionDate, COUNT(P.WorkflowProgressID) as ActionCount FROM WorkflowInstance I INNER JOIN WorkflowProgress P ON I.InstanceID = P.InstanceID GROUP BY I.WorkflowName, I.WorkflowInstanceID, CONVERT(VARCHAR(24), I.StartTime, 120), I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State ORDER BY I.SiteID, I.WebID, I.ListID ASC "@ Write-Progress -Activity "Getting WorkflowProgress Data" -Status "Running SQL Query" -PercentComplete 100 $data = Run-SQLQuery -SqlServer $DBServer -SqlDatabase $DBName -SqlQuery $query $siteUrl = "" $webUrl = "" $listTitle = "" $DataArray=@() $SiteArray = @{} $WebArray = @{} $ListArray = @{} if ($data.Count -gt 0) { if ((Get-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue) -eq $null) { Add-PSSnapin Microsoft.SharePoint.PowerShell } $i = 0 $nbrRows = $data.Count $percComplete = 0 $percRow = [Math]::Floor(100 / $nbrRows) foreach ($row in $data) { $i++ $percComplete += $percRow Write-Progress -Activity "Processing Data" -Status "Record $i/$nbrRows" -PercentComplete $percComplete if ($row.SiteID -ne $null) { # Check if we already processed this site before if ($SiteArray.ContainsKey($row.SiteID)) { $siteUrl = $SiteArray.Get_Item($row.SiteID) } else { $site = Get-SPSite $row.SiteID -ea SilentlyContinue if ($site -eq $null) { $siteUrl = "$($row.SiteID) (DELETED)" } else { $siteUrl = $site.Url } $SiteArray.Set_Item($row.SiteID,$siteUrl) } # Check if we already processed this web before if ($WebArray.ContainsKey($row.WebID)) { $webUrl = $WebArray.Get_Item($row.WebID) } else { if ($site -eq $null) { $webUrl = "$($row.WebID) (DELETED)" } else { $web = $site.AllWebs | ? {$_.Id -eq $row.WebID} if ($web -eq $null) { $webUrl = "$($row.WebID) (DELETED)" } else { $webUrl = $web.Url } } $WebArray.Set_Item($row.WebID,$webUrl) } # Check if we already processed this list before if ($row.ListID -ne "00000000-0000-0000-0000-000000000000") { if ($ListArray.ContainsKey($row.ListID)) { $listTitle = $ListArray.Get_Item($row.ListID) } else { if ($web -eq $null) { $listTitle = "$($row.ListID) (DELETED)" } else { $list = $web.Lists | ? {$_.Id -eq $row.ListID} if ($list -eq $null) { $listTitle = "$($row.ListID) (DELETED)" } else { $listTitle = $list.Title } } $ListArray.Set_Item($row.ListID,$listTitle) } } if ($web -ne $null) { $web.Dispose() } if ($site -ne $null) { $site.Dispose() } $state = "" switch ($row.State) { 2 { $state = "Running" } 4 { $state = "Completed" } 8 { $state = "Cancelled" } 64 { $state = "Errored" } } $item = @{ "WorkflowName" = $row.WorkflowName "WorkflowInstanceID" = $row.WorkflowInstanceID "StartTime" = $row.StartTime "Site" = $siteUrl "Web" = $webUrl "List" = $listTitle "ItemID" = $row.ItemID "WorkflowInitiator" = $row.WorkflowInitiator "WorkflowID" = $row.WorkflowID "State" = $state "LastActionDate" = $row.LastActionDate "ActionCount" = $row.ActionCount } $DataRow = New-Object PSObject -Property $item $DataArray += $DataRow } } $DataArray | Export-Csv -NoTypeInformation -Path wflProgress.csv -Delimiter ";" } |
This not only gives me information I can work with immediately, but it also allows me to do it all from my SharePoint server. I don’t need a SQL Management studio anymore or physical access to the database server.
Depending on the amount of rows that is returned from SQL, the execution of the script can take some time though. I tried to minimize the calls to SharePoint by using arrays where I store the URL’s and titles for found GUID’s. This reduces the stress on SharePoint a bit but it still requires some time to go through each row.
Using this list, you can prioritize the things to focus on during a cleanup. And it gives you also the ability to predict the impact of a purge on that table using specific parameters.
