Speaking with a few people recently, there came about the question of knowing what Nintex Workflows were running at a particular point in time, or still running for that matter.
To the bat-cave Robin!!! I mean, to SQL Management Studio.
Tinkering around with the Nintex Workflow database, I whipped up a SQL query that I believe helped me answer the question.
SELECT DISTINCT tblResult.WorkflowName FROM ( SELECT WI.InstanceID, WI.WorkflowName, MIN(WP.TimeStamp) as 'StartWork', MAX(WP.TimeStamp) as 'EndWork' FROM WorkflowInstance WI INNER JOIN WorkflowProgress WP ON WI.InstanceID=WP.InstanceID GROUP BY WI.InstanceID, WI.WorkflowName ) as tblResult WHERE tblResult.StartWork <= '2012-10-11 21:47:40.693' AND tblResult.EndWork >= '2012-10-11 21:47:40.693'
You simply need to place the date/times with what you need and you should get a bunch of distinct workflow names that were running during that time period.
It will also show workflows that are still considered running, which was a bonus, as I didn’t have to build more logic into that SQL query.
I’ll admit, I haven’t perform a million tests on this query. But I did compare the results to workflow instances I saw in SharePoint and I did start some workflows to see if they appeared, and they did.
NOTE: This doesn’t tell you each instance of a workflow that ran. It gives you a unique list of workflow names that were running.
The below image shows a SharePoint list where I have 2 items with long running workflows.
With the script above, I would get 1 result, because it returns a unique list of workflow names that were running at a particular time:
If you want to see a non-distinct list of workflow names, remove the DISTINCT keyword from the query.
Hopefully this is handy to some of you.