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 · Accepted solution]

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 ;

[Valentina C]

Hey @Vikrant Yadav !
We do not have script runner but I ran this SQL query and it worked!
I just added it.pname to the group by clause like this

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, it.pname
order by count(ji.issuetype) asc ;

Thank you very much!

[Vikrant Yadav]

Hi @Valentina C  
Glad to hear I am able to help you out :)

[Mandy P_]

Hi alltogether!

We faced a similar issue and this SQL worked like a charm, thank you very much!

To be sure: It just only returns active issues? So archived issues are not counted, are they?

Thank you very much in advance! :)

[Sreenivasaraju P]

Hi @Valentina C ,

Please refer below link to find un used issue types. I hope this helps you

https://confluence.atlassian.com/jirakb/how-to-find-unused-issue-types-with-sql-1072216995.html

[Valentina C]

Tried it already, but it's not retrieving any results unfortunately

[Sreenivasaraju P]

May be there are no used issue types. 

Please try the first query by replacing NOT IN with IN and check whether you are getting all issue types or not.

[Valentina C]

it's giving info without NOT but not the result I need whatsoever, thank you, the below answer worked for me