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*
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
|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.