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

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

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:


In case you do face the above error or would like to double-check the associated workflows of a status, you may do so through the following database queries.

Environment

8.20.15, 9.4.3

Cause

The cause for this issue is still not well known or documented and can happen as a result of JRASERVER-66518 - Getting issue details... STATUS .

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.

    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.

    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.

    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()

Solution

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



Last modified on Jun 9, 2023

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.