MySQL query - Get list of projects using a specific workflow scheme and issue type

[Joanna Ramsay]

Hi, 

I'm trying to write a query to find the projects using a particular workflow scheme and issue type. 

Any query suggestions, or documentation, would be greatly appreciated. I've been googling for a while, and I'm going around in circles. 

Thanks in advance!

[Kurt Klinner]

@Joanna Ramsay 

Hi Joanna

directly accessing the database is not the best approach, so you might think of using an alternative solution like for example a groovy script via scriptrunner

import com.atlassian.jira.component.ComponentAccessor

def projectManager = ComponentAccessor.projectManager
def workflowManager = ComponentAccessor.workflowManager
def workflowSchemeManager = ComponentAccessor.workflowSchemeManager

def wfName = "NAME of YOUR WORKFLOW"
def allProjects = projectManager.getProjectObjects()
allProjects.findAll { project ->
    def scheme = workflowSchemeManager.getWorkflowScheme(project) 
    wfName in workflowManager.getWorkflowsFromScheme(scheme)*.name
}*.key

If you still want to follow up with the SQL approach, you should first of all  take a look to the database schema https://developer.atlassian.com/server/jira/platform/database-schema/

Below an example on how to list projects and the associated workflow schemes

select p.pname, w.workflow f
rom project p join workflowschemeentity w join nodeassociation n 
on n.sink_node_id=w.scheme and p.ID = n.source_node_id 
where n.sink_node_entity like 'WorkflowScheme'

 Hope that helps

Kurt