Vadim Tabakman

This scenario has come across my table a few times.

I want to know all the workflows that are currently in progress in my Site Collection.  I may need to terminate them.  Or I may need to terminate and restart them.

If you have Nintex Workflow with an Enterprise License, there is a report called "Workflows in Progress".  This will give you a nice list of all the currently running workflows.

Site Settings

Select the "Workflows in Progress" report.

View Reports

As you can see, you have a number of links that you can click on manually.  This is an awesome feature of Nintex Workflow.  The report has a number of columns of data.  You can see who started the workflow, how long it's been running and how long it's been idle for.  All very useful information if you want a smooth running business.

In my case, this is almost what I need, but I need to go a step further.  Automating the retrieval of this information and having the ability to build logic into a workflow that does something with this information.

Scenario

There are 300 workflows running in different sites and different lists.  I need to terminate each one, but before doing this, I need to have a nice report I can send to all the managers in my company.  Also, if I do need to termiante them, then manually doing this is just no feasible.

Right now, I can view the reports, but I really need a nice way to show the report, and I also need to email this information to people.  So this is my first task.

Pseudo-algorithm

  1. Get a list of Site IDs and Urls (all sites in the current Site Collection).
  2. Get all the running workflows
  3. Iterate through each workflow and build a Workflow Progress Url
  4. Build a HTML Report to be sent via Email

Step 1 - Get Site IDs

Given that I have 300+ workflows in progress, I don't want make a web service call for each one.  So I will be utilizing a data structure I talked about in my previous post - a dictionary.

To get the subsites in the site collection, make a web service call to Webs.asmx.  Specifically, the GetWebCollection web method.  Parse the results, and extract the Site ID and the Site Url, and add it to our Dictionary.

I'm not going to go into details on how to parse the Xml data from the web service. That's a task for the reader.  You can look at the workflow at the end of this post if you want to see the actions.  I've tried to label each action descriptively, so hopefully that helps.

The result will be a dictionary (xml) in a Text variable that looks like this :

<dictionary

 <web id="" url=""/>

 <web id="" url=""/>

</dictionary>

Step 2 - Get a list of Running Workflows

Time for some reuse.  I mentioned the "Workflows in Progress" report.  Well, this report is based on data from a Stored Procedure in the Nintex Workflow database.  The Stored Procedure that it uses is "rptWorkflowsInProgress".  We can call this workflow, passing it the current Site Collection ID ( a Common Property in the current workflow ).

Execute SQL

To call this Stored Procedure, we use the Execute SQL action.  You'll need a connection string to the Nintex Workflow database.  So this is really a workflow that should only be used by something you trust in the company.

This stored procedure returns many columns of data.  With build v2.3+ of Nintex Workflow 2010, you can now return not just one column, but all the columns you want.  How awesome is that? 

Step 3 - Iterate through Each Workflow

So with that, I created a Collection variable for each column, and a Number variable to be my index into the collections.  I then iterated through each value in a collection using the For Each action.  Inside the For Each, I have a Run Parallel Actions, with a branch for each collection I need to interogate.

After the Run Parallel Actions, I have a text variable, date variable or a number variable with the data I need from each column in one record from that Stored Procedure.  I can now do something with all that data.

Run Parallel

One of the piece of data we have, is the Web ID.  This is ID of the subsite in the Site Collection.  As our end result is to have links to the workflows (progress page), we need the url for the ID.  So this is where we query our Dictionary to the Url for the associated site ID.

This is where the action goes:

Run Get Site Url

Here is what the Query XML looks like, to get the Site Url :

Query Xml Dictionary

Now that we have the url, and other data, I need to build the url to the Workflow Progress page.  It's all good to build a Url, but lets not lose focus on the fact that we want a nice report to email managers.  So the plan is to put the built up urls and workflow names into an XML format.  I'll tell you why later :).

I have a variable that I am using that will store my XML.  So I am appending it here:

Build Xml Report

It's not pretty, but it's XML and that's what we want at the moment.

Step 4 - Build a HTML Report to be Sent by Email

Now that we have the XML, we need to transform it in a way so that we have something legible to send to managers.

In comes something that I think is an underutilized function of Nintex Workflow.

XSL Transformations

The Query XML action allows you use XPath expression to get data out.  But it also allows you to run XSL Transformations over XML to transform it into something cool.  In this case, we'll do a simple one, which will convert the XML we've built up, into a nice HTML table with links.

This is what the Query XML action looks like:

XSL Transformation

If you want to see the XSL, it's not overly exciting. You can download the workflow at the bottom of this post, or even take a look here:

https://www.w3schools.com/xml/tryxslt.asp?xmlfile=cdcatalog&xsltfile=cdcatalog 

My XSL is basically a copy and paste of that with some minor alterations.

When I run my workflow, I get an email with a nice pretty HTML table with workflow names and links to the progress page:

HTML Report

Ok.... I may have been exaggerating slightliy on the "prettyness" level of this report.  But it does show you that you can do some cool stuff with Nintex Workflow, if you put your mind to it.  I've see some pretty fancy XSL that made some awesome looking HTML emails.

Conclusion

This a fairly elaborate workflow, just for a report.  But it really isn't just that.  This the bare bones logic of what you need to get all the running workflows, and get all the data necessary to do someting with that.  In this case, we are creating a report.  But you could elaborate on this and terminate each of the workflows before you do a migration.  Or if these are stuck workflows, you could terminate them and then restart them.

I'm sure there are a bunch of other things you could with this skeleton workflow.  Atleast we now have something to work from.  The hard part is done.

Something to note, is that I've put no Error Handling in this workflow.  If you use this for production, I'd highly recommend it and also to put some checks around places this workflow could fail.  Such as assuming data from a Query XML or a Web Service call will always contain data.

Downloads

Copyright Vadim Tabakman
Rate this Content 5 Votes

Comments

Tuesday, March 20, 2012 1:15:11 PM
NJ Lechnir

re: Nintex Workflow - Running Workflows Email Report

Excellent job on this procedure, and great website Vadim!

Tuesday, March 20, 2012 3:42:31 PM

re: Nintex Workflow - Running Workflows Email Report

Thanks NJ :)

Thursday, March 22, 2012 3:41:41 PM
NJ Lechnir

re: Nintex Workflow - Running Workflows Email Report

Hey Vadim,

I got the workflow to run on my end, but it only shows a handful of workflows that I have running.  I actually have hundreds more that are not listed in the final email report.  What do I need to adjust?

Sunday, March 25, 2012 7:29:51 PM

re: Nintex Workflow - Running Workflows Email Report

Hey NJ,

the report that is run by the workflow should bring back all the workflows.  I don't think I'm limiting the results in any way.  I'd suggest trying to run the same Stored Procedure from SQL Management Studio.  Are you getting the same number of results?

Friday, August 17, 2012 7:22:36 AM
Kevin

re: Nintex Workflow - Running Workflows Email Report

Awesome work! - But I have a long ways to go for to be able to use this.

The problem is that this only goes one subsite level deep in a site collection.  If you have a site that is at the third level (http://MySiteCollection/SubSite1/SubSite2), then that site (SubSite2) will not have the stored procedure ran against it.

 

If Nintex would just provide the reports as a SQL report, then by using SharePoint's built in SQL Reporting you can leverage the scheduling tool and have SharePoint functionality to create the snapshot report and email it to you.

This workflow would need use nested actions to go down each SubSite path to retreive all of the site.  I think it would be better to use a loop control than a foreach command - that way you have a better control of how the workflow is progressing through each collection of webs.  You would also need to use a statemachine, because you don't know how many levels of webs that you have to travel down to get all of the webIDs.

 Is it worth the effort - you do I just buy Enterprise.  $10K for reporting is a steep fee though.

Friday, August 17, 2012 7:57:05 AM

re: Nintex Workflow - Running Workflows Email Report

Hi Kevin,

that is a great point.  You are more than welcome to tweak this workflow to go down further levels of subsites.  I guess in my tests I only did a root site with a single subsite.  But you thoughts do make sense and would definitely make for a better, more complete workflow.

Have SQL reports is also an awesome idea.  I should look into this.  I haven't played with that in many years.

cheers,

Vadim

Thursday, October 25, 2012 7:51:58 AM
Cory

re: Nintex Workflow - Running Workflows Email Report

First of all, thanks for the great starting point!

Question -

If I'm not mistaken, 'rptWorkflowsInProgress' basically gets rows from this table: 'dbo.WorkflowInstanceView', where the state = 2.

However, when I run view reports, I get entries that don't match that criteria ( I looked at the list using Select * From dbo.WorkflowInstanceView).  What do you think might be causing that?  Does the report view use data from somewhere else?  I've already modified it to include more than 20 rows.

Thanks, any imput would be appreciated!

Thursday, October 25, 2012 9:49:47 AM

re: Nintex Workflow - Running Workflows Email Report

Hi Cory,

it looks like the rptWorkflowsInProgress stored proc does a little more than just querying the WorkflowInstanceView.  Feel free to look in SQL Management Studio at the rptWorkflowsInProgress proc.  It's not pretty though :).

Thursday, October 25, 2012 11:51:06 AM
Cory

re: Nintex Workflow - Running Workflows Email Report

Unfortunately, I don't have access to Sql Management Server, so I'm just calling "sp_helptext rptWorkflowsInProgress;" :(. I can't seem to find what else it does...  It looks to me like it just filters and sorts data from that view?

 

In either case, why wouldn't:  exec rptWorkflowsInProgress @MaximumRows=2000

return the same as the report ?

 

*(note the view returns ~800 items, the call returns ~45 items)

 

Thursday, October 25, 2012 11:52:28 AM

re: Nintex Workflow - Running Workflows Email Report

Hi Cory,

it's possible that because the stored proc takes about 7 parameters, that you results are differing to what you would see in the UI.

Monday, November 26, 2012 7:50:42 AM
Cory

re: Nintex Workflow - Running Workflows Email Report

I finally solved my issue, turns out I was pointing to the test server instead of the production server.  Embarrasing haha.  Thanks again for help and the great workflow!

Adding to Kevin's comment, using Webs.asmx - GetAllSubWebCollection command, you can get every single subweb on the site, so you don't need to know how many levels there are (it takes care of that for you).

Monday, November 26, 2012 8:22:10 AM

re: Nintex Workflow - Running Workflows Email Report

lol Cory.  I've done that too :).

Glad you figured it out.

Vadim

Thursday, July 16, 2015 12:15:10 AM
Prem
Gravatar

re: Nintex Workflow - Running Workflows Email Report

Hi Vadim,

I am getting the "Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" while running the reports. Can you please suggest what might be the cause of this issue?

You must sign in to this site to post comments.
Already Registered?
Sign In
Not Yet Registered?
Register





 

Statistics

  • Entries (272)
  • Comments (1768)

Categories