How to check affected issues per workflow using JSU validators, post-functions, and conditions in Jira Data Center

Instructions

The query below returns the total issue count per workflow, and the workflows considered in the table are the ones that have any JSU module applied. (PostgreSQL)

WITH excluded_statuses AS (
    SELECT
        id
    FROM issuestatus
    WHERE LOWER(pname) IN ('done', 'canceled')
),
issue_counts AS (
    SELECT
        jw1.workflowname,
        COUNT(ji1.id) AS total_issues,
        COUNT(
            CASE
                WHEN ji1.created >= NOW() - INTERVAL '90 days' THEN 1
            END
        ) AS recent_issues
    FROM jiraissue ji1
        INNER JOIN project p
            ON ji1.project = p.id
        INNER JOIN nodeassociation nod
            ON p.id = nod.source_node_id
        INNER JOIN workflowschemeentity wse
            ON nod.sink_node_id = wse.scheme
        INNER JOIN jiraworkflows jw1
            ON wse.workflow = jw1.workflowname
    WHERE
        nod.sink_node_entity = 'WorkflowScheme'
        AND ji1.issuestatus NOT IN (
            SELECT
                id
            FROM excluded_statuses
        ) -- Exclude 'Done' and 'Canceled' statuses
    GROUP BY
        jw1.workflowname
)
SELECT
    jw.id AS workflow_id,
    jw.workflowname AS workflow_name,
    COALESCE(ic.total_issues, 0) AS total_issues,
    COALESCE(ic.recent_issues, 0) AS recent_issues
FROM jiraworkflows jw
    LEFT JOIN issue_counts ic
        ON jw.workflowname = ic.workflowname
WHERE
    (
        jw.descriptor LIKE '%com.googlecode.jsu%'
        OR jw.descriptor LIKE '%ch.beecom.jira.jsu%'
    )
ORDER BY
    total_issues DESC;

Query result:

Note:

The column total_issues returns all the issues per workflow.
The column recent_issues returns all the issues created in the last 90 days per workflow.

The workflow descriptor column can also be included in the table to better visualize the JSU modules that exist per workflow.

Example of query result

Note:

It is possible to return only a specific JSU condition, validator, or postfunction; you can achieve this by changing the query condition.

e.g.,

WHERE (jw.descriptor like '%ch.beecom.jira.jsu.workflow.function.createlinkedissue.CreateLinkedIssueFunction%')

JSU modules can be obtained from the workflow descriptor column and multiple JSU modules can belong to the same workflow.

How to check affected issues per workflow using JSU validators, post-functions, and conditions in Jira Data Center

Instructions

Related articles