Have you wanted to see at a glance how many times a workflow has run? Do you ever wonder how many people using workflows you’ve created are experiencing errors? Do you ever think about all the workflows that are never completed and remain alive in the system and reprocess every few hours?

If you’ve ever thought about any of this, we’ve come up with a simple solution that may fit your needs!

Workflow Statistics

We created two places to better understand how our workflows were being used. The first was the addition of a new Dynamic Data block at the top of the Workflow Configuration page. With this block, I populated a Statistics panel with two categories of workflow statistics - the number of active workflows split by status and the number of completed workflows split by status. You may also notice we have added a usage panel to this page as well, but that will have to be the subject of a future recipe!

The Dynamic Data block executes two separate queries to obtain this information and uses the workflowTypeId present in the page URL to find data for the correct workflow. The full queries and HTML are available in the appendix below!

Workflow Health

The second place we created to understand workflow health was the addition of a new page in a section of our Admin Tools we call ‘Database Tools’. This page provides a full picture of all active workflows in our system in one place. We created a way to filter the page to yourself or another person to quickly find only the workflows they have modified last (names redacted below to limit public embarrassment). The page also shows the status of the Process Workflow job.

Similar to the other implementation, this page is also built with a Dynamic Data block with a workflow entry for the person selector which places the personId in the query string for the Dynamic Data block to filter the results. The SQL and HTML for this page are also available in the appendix! Note: The HTML for this block will require minimal edits - you'll need to replace a few placeholders: the pageId for your workflow configuration page, your server url, the jobId of your process workflow job, and a valid authorization token for your instance of Rock.

Appendix

Workflow Configuration SQL

SELECT Count(*) as 'ActiveCount',
wf.WorkflowTypeId,
wf.Status
FROM [Workflow] as wf
WHERE wf.WorkflowTypeId = @workflowTypeId
and wf.CompletedDateTime is null
Group by wf.WorkflowTypeId,
wf.Status	
ORDER BY 'ActiveCount' DESC;

SELECT Count(*) as 'ActiveCount',
wf.WorkflowTypeId,
wf.Status
FROM [Workflow] as wf
WHERE wf.WorkflowTypeId = @workflowTypeId
and wf.CompletedDateTime is not null
Group by wf.WorkflowTypeId,
wf.Status
ORDER BY 'ActiveCount' DESC;

Workflow Configuration HTML

<div class="panel-group">
<div class="panel panel-default">
<div class="panel-heading">
<h4 class="panel-title"><a data-toggle="collapse" href="#collapse1">Statistics</a></h4>
</div>
<div id="collapse1" class="panel-collapse collapse">
<div class="panel-body">
<div class="row">
{% comment %} SQL Table 1 - Active Workflows{% endcomment %}
{% assign results = table1.rows | Size %}
{% if results != 0 %}      
    <div class="col-md-6">
    {[ panel title:'Active Workflows by Status']}    
    {% for row in table1.rows %}
        <div class="row"><b>&nbsp;&nbsp;&nbsp;&nbsp;{{ row.Status }} </b> - {{row.ActiveCount}}</div>
    {% endfor %}    
    {[ endpanel ]}
    </div>
{% endif %}
{% comment %} SQL Table 2 - Active Workflows{% endcomment %}
{% assign results = table2.rows | Size %}
{% if results != 0 %}      
    <div class="col-md-6">
    {[ panel title:'Recent Completed Workflows by Status']}    
    {% for row in table2.rows %}
        <div class="row"><b>&nbsp;&nbsp;&nbsp;&nbsp;{{ row.Status }} </b> - {{row.ActiveCount}}</div>
    {% endfor %}    
    {[ endpanel ]}
    </div>
{% endif %}
</div></div></div></div>

Workflow Health SQL

IF @Person != ''
SELECT Count(*) as 'ActiveCount',
wf.WorkflowTypeId,
wft.Name as'WorkflowName',
wft.ModifiedDateTime,
p.FirstName,
p.LastName
FROM [Workflow] as wf
JOIN WorkflowType as wft
on wf.WorkflowTypeId = wft.Id
JOIN PersonAlias as pa
on wft.ModifiedByPersonAliasId = pa.Id
JOIN Person as p
on pa.PersonId = p.Id
WHERE (wf.CompletedDateTime is null)
and pa.Guid = @Person
Group by wf.WorkflowTypeId,
wft.Name,
wft.ModifiedDateTime,
p.FirstName,
p.LastName
ORDER BY 'ActiveCount' DESC
ELSE 
SELECT Count(*) as 'ActiveCount',
wf.WorkflowTypeId,
wft.Name as'WorkflowName',
wft.ModifiedDateTime,
p.FirstName,
p.LastName
FROM [Workflow] as wf
JOIN WorkflowType as wft
on wf.WorkflowTypeId = wft.Id
JOIN PersonAlias as pa
on wft.ModifiedByPersonAliasId = pa.Id
JOIN Person as p
on pa.PersonId = p.Id
WHERE (wf.CompletedDateTime is null)
Group by wf.WorkflowTypeId,
wft.Name,
wft.ModifiedDateTime,
p.FirstName,
p.LastName
ORDER BY 'ActiveCount' DESC

Workflow Health HTML

{% comment %} SQL Table 1 - Active Workflows{% endcomment %}
{% assign results = rows | Size %}
{% if results != 0 %}
    <div class="row">
    <div class="col-md-6">
    {[ panel title:'Active Workflows']}
    {% for row in rows %}
        <div class="row">
            {% capture editurl %}/page/136?workflowTypeId={{row.WorkflowTypeId}}{% endcapture %}
            <a href="{{ editurl }}" class="btn-sm btn-default"><i class="fas fa-external-link"></i></a> <b>{{ row.WorkflowName }}</b>
            <ul>
                <li>Active Workflows: {{row.ActiveCount}}</li>
                <li>Last Modified By: {{row.FirstName}} {{row.LastName}} at {{row.ModifiedDateTime}}
            </ul>
        </div>
        <br>
    {% endfor %}
    {[ endpanel ]}
    </div>
{% endif %}

<div class="col-md-6">
{[ panel title:'Last Process Workflow Job Statistics']}
<div class="row">
{% webrequest url:'https://<YOUR-ROCK-URL>/api/ServiceJobs/<jobId>' headers:'Content-Type^application/json|Accept^application/json|Authorization-Token^<REDACTED>' %}
&nbsp;&nbsp&nbsp;&nbsp<b>Last Run Date:</b></div>
<div class="row">&nbsp;&nbsp&nbsp;&nbsp{{results.LastRunDateTime}}</div>
<br>
<div class="row">&nbsp;&nbsp&nbsp;&nbsp<b>Last Run Status:</b></div>
<div class="row">&nbsp;&nbsp&nbsp;&nbsp{{results.LastStatus}}</div>
<br>
<div class="row">&nbsp;&nbsp&nbsp;&nbsp<b>Last Run Status Message (Truncated):</b></div>
<div class="row">&nbsp;&nbsp&nbsp;&nbsp{{results.LastStatusMessage | Truncate:65,'...'}}</div>
<div class="row">&nbsp;&nbsp&nbsp;&nbsp<a href="https://rock.lakepointe.church/page/102">View full job output here</a></div></div>
{% endwebrequest %}
{[ endpanel ]}
</div>