Gaining insights in your Nintex Workflow Data

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:

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.

workflow data

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.

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

By Bart

Bart is a certified SharePoint consultant / architect at CTG Belgium NV with a broad professional experience in IT, a background in software development with a specialisation in Microsoft products and technologies and a solid knowledge and experience in Microsoft SharePoint Products and Technologies. He started as a COBOL developer on a mainframe environment and grew into software development for Windows platforms. Participated in projects varying from migrations of existing applications to development of Web applications and Windows applications. Became fascinated by the SharePoint 2007 platform and strongly believed in the added business value of this platform. Is since then fully committed to SharePoint and focuses on SharePoint implementations, migrations, integrations, design and coaching. Stays on top of new developments within the SharePoint technology stack and related technologies.