How to find unused issue types on your Jira instance?

Valentina C March 5, 2024

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.

 

 

 

 

2 answers

1 accepted

0 votes
Answer accepted
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 5, 2024

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 March 6, 2024

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!

Like Vikrant Yadav likes this
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 6, 2024

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

1 vote
Sreenivasaraju P
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 5, 2024

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 March 5, 2024

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

Sreenivasaraju P
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 5, 2024

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 March 6, 2024

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

Suggest an answer

Log in or Sign up to answer