How to use SQL query to look for workflows without a specific screen in any transition
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
Jira's workflow definition is stored as XML in the database and manipulating it via SQL can be difficult. The following is an example of a request that might require such manipulation:
"What I need to find, is how many workflows do not have a resolve screen (or any screen) attached to a transition into a Done category status."
Environment
All versions of Jira Core 7 and 8.
Solution
The query below will list workflows' transition and status names and their field screen for any transition into the Done category status that doesn't have a Field Screen attached.
Postgres example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select j.id , j.workflowname , workflow_action.name as transition_name , workflow_action.view , workflow_action.fieldscreen , workflow_step.name as statusname , workflow_step.statusid
from jiraworkflows j,
XMLTABLE('//workflow/common-actions/action'
passing xmlparse(document descriptor)
columns
id int path '@id',
name text path '@name',
view text path '@view' default null,
fieldscreen int path 'meta[@name = "jira.fieldscreen.id" and text() != ""]' default null,
next_step int path 'results/unconditional-result/@step'
) as workflow_action join
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 on workflow_action.next_step = workflow_step.id join
issuestatus i on workflow_step.statusid = i.id
where i.statuscategory = 3 and workflow_action.fieldscreen is null;Sample output
Postgres sample output
1
2
3
4
5
6
7
8
9
10
11
12
13
|id |workflowname |transition_name |view |fieldscreen|statusname|statusid|
|------|------------------------------------------------------------------------------------|------------------|-----------|-----------|----------|--------|
|10.204|ITSM: Incident Management workflow for Jira Service Management |Cancel |fieldscreen|10.122 |Canceled |10104 |
|10.204|ITSM: Incident Management workflow for Jira Service Management |Resolve |fieldscreen|10.122 |Completed |10113 |
|10.201|ITSM: Service Request Fulfilment workflow for Jira Service Management |Cancel request |fieldscreen|10.111 |Canceled |10104 |
|10.201|ITSM: Service Request Fulfilment workflow for Jira Service Management |Resolve this issue|fieldscreen|10.111 |Resolved |5 |
|10.202|ITSM: Service Request Fulfilment with Approvals workflow for Jira Service Management|Resolve this issue|fieldscreen|10.114 |Resolved |5 |
|10.202|ITSM: Service Request Fulfilment with Approvals workflow for Jira Service Management|Cancel request |fieldscreen|10.114 |Canceled |10104 |
|10.203|ITSM: Change Management workflow for Jira Service Management |Decline |fieldscreen|10.119 |Declined |10100 |
|10.203|ITSM: Change Management workflow for Jira Service Management |Cancel |fieldscreen|10.119 |Canceled |10104 |
|10.205|ITSM: Problem Management workflow for Jira Service Management |Cancel |fieldscreen|10.125 |Canceled |10104 |
|10.205|ITSM: Problem Management workflow for Jira Service Management |Complete |fieldscreen|10.125 |Completed |10113 |
|10.206|ITSM: Jira Service Management default workflow |Mark as Done |fieldscreen|10.128 |Done |10001 | Was this helpful?