How to find unused issue types on your Jira instance?

Valentina C

Hello, is there a way to find all the issue types that are not used in any project for the whole instance? A rest API, or sql query will do, tried several options but nothing really helped.

Trying to clean up out Jira DC instance without any plug-ins.

 

 

 

 

Vikrant Yadav

Hi @Valentina C  Welcome to Atlassian Community!

If you have a scriptrunner plugin, Kindly try to run following script in Script Console to get issue type issues count : 

 

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

 it.id as "Issue Type ID", 

 it.pname as "Issue Type",

 count(ji.issuetype) as "Number of Issues" 

from

 issuetype it 

left outer join

 jiraissue ji 

on

 it.id=ji.issuetype group by it.id

    order by count(ji.issuetype) asc ;

"""

Connection conn = ConnectionFactory.getConnection(helperName)

Sql sql = new Sql(conn)

String result = """

<table>

  <tr>

    <th>Issue Type ID</th>

    <th>Issue Type Name</th>

    <th>Issue Count</th>

  </tr>

"""

sql.eachRow(sqlStmt){ it ->

    result += """

        <tr>

          <td>${it.getAt("Issue Type ID")}</td>

          <td>${it.getAt("Issue Type")}</td> 

          <td>${it.getAt("Number of Issues")}</td>

        </tr>

"""

    }

return result+"</table>"

sql.close()

SQL Query : 

select
it.id as "Issue Type ID", 
it.pname as "Issue Type",
count(ji.issuetype) as "Number of Issues" 
from
issuetype it 
left outer join
jiraissue ji 
on
it.id=ji.issuetype group by it.id
order by count(ji.issuetype) asc ;