List active workflows in Jira DC with project and issue type filters
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
Learn how to list active workflows in Jira DC. The query can help to filter out the workflows associated with projects and issue types.
Solution
This was written and tested using a PostgreSQL database, so you may need to tweak it depending on your database.
The below query will help to provide the list of Active Workflows in Jira DC:
SELECT DISTINCT "Workflow Name"
FROM (
SELECT pkey AS "Project Key", workflow AS "Workflow Name", pname AS "Issue Type"
FROM (
SELECT p.pkey, wse.workflow, it.pname
FROM nodeassociation n, project p, workflowscheme ws, workflowschemeentity wse, issuetype it
WHERE p.ID = n.source_node_ID
AND n.source_node_entity = 'Project'
AND ws.ID = n.SINK_NODE_ID
AND wse.scheme = ws.ID
AND wse.issuetype = it.id
AND n.sink_node_entity = 'WorkflowScheme'
GROUP BY wse.workflow, p.pkey, it.id
union
SELECT p.pkey, wse.workflow, it.pname
FROM nodeassociation n, project p, workflowscheme ws, workflowschemeentity wse, issuetype it, configurationcontext cc, optionconfiguration oc
WHERE p.ID = n.source_node_ID
AND n.source_node_entity = 'Project'
AND ws.ID = n.SINK_NODE_ID
AND wse.scheme = ws.ID
AND wse.issuetype = '0'
AND n.sink_node_entity = 'WorkflowScheme'
AND oc.fieldconfig = cc.fieldconfigscheme
AND it.id = oc.optionid
AND p.id = cc.project
GROUP BY wse.workflow, p.pkey, it.id) as temp
ORDER BY workflow
) AS subquery; Was this helpful?