Nintex Workflow – Running Workflows Email Report
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.
Select the “Workflows in Progress” report.
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
- Get a list of Site IDs and Urls (all sites in the current Site Collection).
- Get all the running workflows
- Iterate through each workflow and build a Workflow Progress Url
- 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 ).
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.
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:
Here is what the Query XML looks like, to get the Site Url :
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:
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:
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:
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
