With Scriptrunner is it possible to list all Projects using Default Priority Scheme

[ChristopherChilds]

Hi All,

The Default Priority Scheme is the default Designation for all New Projects and for all new issue Priorities. As such, this Scheme has almost 50 priorities.
The problem is that we have no idea which users are using the Default Scheme. This surely can not provide a good user experience.

Is there a way to see which projects are using this?
I believe there is a workaround to query the Database:
https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-projects-using-the-default-priority-scheme-in-jira-1019395577.html

But, I do not want to have to do this. I am hoping there is a way with Scriptrunner to do this.

I have also tried with the standard APIs, but I can not see how I can map the priority list to a Project. The information is not available even when checking the Get Project API.

[Vikrant Yadav · Accepted solution]

Hi @ChristopherChilds  If you can have script runner, then it makes your task easier to run SQL query in Script Console.

Try to run following code in Script Console to get list of project using Default Priority scheme :- 

I am using SQL query mentioned in the link :- https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-projects-using-the-default-priority-scheme-in-jira-1019395577.html

import com.atlassian.jira.component.ComponentAccessor

import groovy.sql.Sql

import org.ofbiz.core.entity.ConnectionFactory

import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)

String helperName = delegator.getGroupHelperName("default")

def sqlStmt = """

SELECT pname AS "Project"

FROM project

WHERE id NOT IN (SELECT project FROM configurationcontext WHERE customfield = 'priority' AND project IS NOT NULL)

"""

Connection conn = ConnectionFactory.getConnection(helperName)

Sql sql = new Sql(conn)

try {

StringBuffer sb = new StringBuffer()

sql.eachRow(sqlStmt) { it ->

sb.append( it.getAt("project") )

sb.append('\n')

}

log.warn sb.toString()

}

finally {

sql.close()

}

 Hope it works for you!

Thanks

[Vikrant Yadav]

@ChristopherChilds @Have you tested the script ?

[ChristopherChilds]

This works perfectly. Thank you very much.