Query to check the usage of JSU post-functions, validators, and conditions

This guide provides detailed, step-by-step instructions on how to check JSU (Jira Suite Utilities) usage in active Jira Data Center workflows by querying the database. This method is necessary because the JSU app does not support this functionality through its user interface.

Instructions

1. Identify Workflows with JSU Conditions, Validators, or Post-Functions

Access your database management tool connected to your Jira database.

Run the following SQL query to list all workflows containing JSU components. This query checks the descriptor field in the jiraworkflows table for references to JSU:

SELECT * 
FROM jiraworkflows 
WHERE descriptor LIKE '%com.googlecode.jsu%' 
   OR descriptor LIKE '%ch.beecom.jira.jsu%';

This will return all workflows where JSU modules are present.

2. Determine the Projects Associated with These Workflows

To find out which projects are using these workflows, execute the following SQL query. This will provide details about the project and its associated workflow scheme:

SELECT p.id         AS project_id,
       p.pname      AS project_name,
       p.lead       AS project_lead,
       ws.name      AS workflow_scheme,
       wse.workflow AS workflow_scheme_associated_workflow
FROM PROJECT p
         LEFT OUTER JOIN nodeassociation na ON na.source_node_id = p.id 
         AND na.sink_node_entity = 'WorkflowScheme'
         JOIN workflowscheme ws ON ws.id = na.sink_node_id
         JOIN workflowschemeentity wse ON wse.scheme = ws.id
         JOIN jiraworkflows jw ON jw.workflowname = wse.workflow
WHERE jw.descriptor LIKE '%com.googlecode.jsu%' 
   OR jw.descriptor LIKE '%ch.beecom.jira.jsu%';

This query links projects to their workflow schemes and identifies which workflows (and thus which projects) are using JSU.

3. Extract Information on JSU Usage in Workflow Transitions (Advanced/Optional)

If you need to identify exactly which transitions within a workflow use JSU, you must parse the XML stored in the descriptor field of each relevant workflow.

Steps:

Manually extract the XML content from the descriptor field for each workflow identified above.
Use XML processing tools (such as XQuery or XPath) to search for elements referencing JSU modules.

Note: Depending on your database and tooling, this may require exporting the XML and using external scripts or tools for analysis.

Best Practices & Tips

Backup Before Querying: Always ensure you have a recent backup of your database before running these queries, especially in production environments.
Optimize Performance: These queries can be resource-intensive. Run them during off-peak hours to minimize system impact.
Regular Updates: Update your queries if your Jira instance or JSU app is upgraded, as data structures may change.
Security Considerations: Limit access to these queries and their results, as they may reveal sensitive information about your workflows and projects.
Documentation: Document your findings and keep records for future audits or troubleshooting.