How to find workflows associated with a status from the database in Jira

Summary

An admin may want to understand where a given status is used across the instance in an effort to assess a cleanup or track unused statuses. This can usually be achieved by reviewing the Statues page in Jira ('/secure/admin/ViewStatuses.jspa').

In some instances, statuses can be marked as not being associated with any workflows and even allowed to be deleted on this page, however, upon attempting to do so you may come across the following error message:

Given status is currently associated with a workflow(s) and cannot be deleted

If you encounter the above error or would like to double-check the associated workflows of a status, you can do so using the following database queries.

Environment

8.20.15, 9.4.3

Diagnosis

We are not sure of the cause of the problem, but we can identify the workflows associated with the above statuses in one of the following ways. After finding the associated workflows you may remove the status from the workflow in order to allow deletion.

1. Running the below query in the database and substituting the status for the 'name' value.

   1 SELECT workflowname, regexp_matches(descriptor, 'step id="[0-9]+" name="MyStatusName"', 'g') AS matches FROM jiraworkflows;

2. This method was tested on PostgreSQL and it may require tweaking to fit to other DB products for both the XMLTable syntax as well as the data types.

   1 2 3 4 5 6 7 8 9 10 SELECT x.name::text, j.workflowname FROM jiraworkflows j, XMLTABLE('//step' PASSING xml(j.descriptor) COLUMNS id INTEGER PATH '@id', name TEXT PATH '@name') x where x.name='MyStatusName';

   • If you remove the "where" clause at the end you'll be able to find all status-workflow relations from your instance.

3. In case you have a Scriptrunner plugin from Adaptivist installed in your instance, you can run the below script to find the workflow associated with the status.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 import com.atlassian.jira.component.ComponentAccessor import com.atlassian.jira.config.StatusManager import com.atlassian.jira.workflow.WorkflowManager WorkflowManager workflowManager = ComponentAccessor.getComponent(WorkflowManager) def sb = new StringBuffer() workflowManager.getWorkflows().each { it -> //Modify the value below to your status value such as "MyStatusName" if(it.getLinkedStatusObjects().name.contains("MyStatusName")==true){ sb.append("\n${it.name}\n") sb.append("\n") } } return sb.toString()

Cause

The cause of this issue is still not well-known or documented. It can happen as a result of JRASERVER-66518 - JIRA Status is reported as "No associated workflows" while it's still used.

Solution

Once the workflow associated with the status has been found, ensure that it is not associated with any project and that removing the status won't break any of the workflows. Remove the status from the workflow in edit mode. Then, head to the statuses page and perform the delete.