Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal


  • Give kudos
  • Received
  • Given


  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Sql query to get active workflows from all projects in Jira

We need to get a list active workflows with groovy scripts in their post-function

This is required to as their paths have to be modified as they will need to be moved to under - <jira home>/scripts.

Currently they are in different path & they are creating lot of warning in atlassian-jira.log as below

/secure/CommentAssignIssue.jspa [c.o.scriptrunner.runner.ScriptRunnerImpl] Add a script root for this path

To resolve this, we are required to update active workflows post-functions & move to above mentioned path.

So, we need to get list of only active workflows from all projects in Jira

Below query returns both active & inactive. Not sure if anyway to filter to retrieve active ones

select * from jiraworkflows where DESCRIPTOR like '%.groovy<%';

Any clues, appreciated.

3 answers

This will return all workflows associated with projects, and therefore active.

I wrote this for MS SQL, so you may need to adjust for your DB:

SELECT AS project_id, p.pname AS project_name, p.lead AS project_lead, AS workflow_scheme,--, pc.cname AS category
wse.workflow AS workflow_scheme_associated_workflow
FROM jirauser.project p 
LEFT OUTER JOIN jirauser.nodeassociation na ON na.source_node_id = AND na.sink_node_entity = 'WorkflowScheme'
JOIN jirauser.workflowscheme ws ON = na.sink_node_id
JOIN jirauser.workflowschemeentity wse ON wse.scheme =
JOIN jirauser.jiraworkflows jw ON jw.workflowname = wse.workflow
JOIN jirauser.nodeassociation nas ON p.ID = nas.SOURCE_NODE_ID AND nas.ASSOCIATION_TYPE = 'ProjectCategory'
--Find by project category
--JOIN jirauser.projectcategory pc ON nas.SINK_NODE_ID = pc.ID
WHERE DESCRIPTOR like '%.groovy%'
ORDER BY p.pname

The script registry (Builtin Scripts -> Script Registry) will show you all the workflows where you are using scripts.

But you don't have to move them, you could just do what the log message suggests, which is to add a new root. 

You do this by adding a system property to your startup files:


(or c:/path/to/root for windows)


0 votes

You have to do it the hard way - read the workflow schemes to see if it is any of them, then read the projects to see if the workflow scheme is in use.

I would take a different approach.  Do some housekeeping, deleting all inactive workflow schemes and workflows from the "inactive" section in the UI.   Then your existing SQL will work fine because you know they're all active.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published in Opsgenie

Opsgenie and Amazon team up to launch new DevOps Guru integration - now available!

We’re proud to announce that our integration with Amazon DevOps Guru is now live. The Amazon and Opsgenie product teams have worked together to build a deep integration between Opsgenie and the new...

280 views 0 12
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you