Get list all the statuses used in all the active workflows from database


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

The Jira workflow definition is stored as XML in the database and fetching it via SQL can be difficult. The following is an example of a request that might require such task:

"What I need to find, is all statuses used in all my active workflows"

Environment

This was tested on Jira v9.4.0

Solution

The query below will list workflows' statuses names and IDs

The SQL query was written and tested on PostgreSQL, and might need to be amended if your Jira instance is using a *different type of database*

Postgres example
select j.id , j.workflowname, workflow_step.name as statusname , workflow_step.statusid
from jiraworkflows j, 
XMLTABLE('//workflow/steps/step'
	passing xmlparse(document descriptor)
	columns 
		id int path '@id',
		name text path '@name',
		statusid text path 'meta[@name = "jira.status.id"]' default null
) as workflow_step
join issuestatus i on workflow_step.statusid = i.id;

Sample output

Postgres sample output
|id   |workflowname            |statusname |statusid|
|-----|------------------------|-----------|--------|
|10000|classic default workflow|Open       | 1      |
|10000|classic default workflow|In Progress| 3      |
|10000|classic default workflow|Resolved   | 5      |
|10000|classic default workflow|Reopened   | 4      |
|10000|classic default workflow|Closed     | 6      |

Please note that DB-specific queries are not expected as it falls out of the Atlassian Support Offerings. We encourage you contacting your DBA for any amendment on the provided SQL query or asking a question on the Atlassian Community forum. There you can interact with a variety of Atlassians as well as experienced administrators, Atlassian solution partners and plugin vendors who play an active part in community life.

Last modified on May 9, 2023

Was this helpful?

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