Get list all the statuses used in all the active workflows from database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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. This article provides a way to find all the statuses used in active workflows from the database.
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
1
2
3
4
5
6
7
8
9
10
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
1
2
3
4
5
6
7
|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.
Was this helpful?