How to find all issue types defined in an Issue Type Scheme via SQL query.

[Gerald Lewis]

I have over 2000 projects, with 250+ Issue Type Schemes, and 100+ Issue Types. I am seeking a SQL query to find the issue types defined in each Issue Type Scheme.   Tickets may (or may not) have been created using the issue types.

For example: 

Project A has issue type scheme (named ITS-A) with issue types; Question, Task, and Report

Project B has issue type scheme (named ITS-B) with issue types; Question, Epic, Story

Project C has issue type scheme (named ITS-C) with issue types; Task, Epic, Risk, Story, Sub-task

Projects D, E, F use the same issue type scheme as Project A

Projects G, H use the same issue type scheme as Project C

and so on.  Note, some projects will have issue type schemes with 5 or more issue types.

I can use the following query to identify all the Issue Type Schemes, however, I also want to show the issue types per issue type scheme.

SELECT DISTINCT fcs.configname AS "Issue Type Scheme" FROM fieldconfigscheme fcs JOIN configurationcontext cc ON fcs.id = cc.fieldconfigscheme JOIN project p ON cc.project = p.id WHERE fcs.fieldid = 'issuetype';

Desired output:

Issue Type Scheme  | Issue Types

ITS-A  | Question

ITS-A  | Task

ITS-A | Report

ITS-B  | Question

ITS-B  | Epic

ITS-C  | Task

ITS-C  | Epic

ITS-C  | Risk

ITS-C  | Story

ITS-C  | Sub-task

and so on for all issue type schemes defined.

Thank you.

[Vikrant Yadav]

Hi @Gerald Lewis You can use the following MySQL query to find Project and Issue types mapped with Project :- 

SELECT
P.pname,
GROUP_CONCAT(DISTINCT I.pname ORDER BY I.pname ASC SEPARATOR ',') AS issuetypes
FROM
project AS P
INNER JOIN
jiraissue AS J ON J.PROJECT = P.ID
INNER JOIN
issuetype AS I ON I.ID = J.issuetype
GROUP BY
P.pname;

https://confluence.atlassian.com/jirakb/how-to-find-unused-issue-types-with-sql-1072216995.html#:~:text=Solution,to%20specific%20issue%20type%20schemes.

[Gerald Lewis]

Thanks Vikrant.   I had also viewed https://confluence.atlassian.com/jirakb/how-to-find-projects-statistics-based-off-issue-type-schemes-1206784960.html

However, I am not seeking to link to jiraissue table as some new projects will have no issues; resulting in no rows, while some other projects may only use some (not all issue types).

To give an example:

Project A has issue type scheme (named ITS-A) with issue types; Question, Task, and Report

Project X has issue type scheme (named ITS-A) with issue types; Question, Task, and Report

I create one ticket in Project A (using issue type: Question).  Next, I create one ticket in Project X (using issue type: Task).

Now, via SQL query I'd like to identify both projects what available issue types they have.   The suggested query will report Project A has "Question" and Project X has "Task".   Not what I am seeking.

Desired output is the project name plus all issue types the project's issue type scheme includes.   Thanks.

[Vikrant Yadav]

@Andy Heinzer  Can you please help us with SQL query ? 

I am not able to create SQL query to get details.

[Gerald Lewis]

No problem.  This morning I wrote the following query that solves this issue.

SELECT DISTINCT p.pname, fcs.configname AS 'Issue Type Scheme', itt.pname AS 'Issue Type'

  FROM project p

  JOIN nodeassociation na ON p.id = na.source_node_id and source_node_entity='Project'

  JOIN configurationcontext cct ON cct.project=p.id AND customfield='issuetype'

  JOIN fieldconfigscheme fcs ON fcs.id=cct.fieldconfigscheme AND fieldid='issuetype'

  JOIN fieldconfigschemeissuetype fcsi ON cct.fieldconfigscheme = fcsi.fieldconfigscheme

  JOIN optionconfiguration oc ON fcsi.fieldconfiguration = oc.fieldconfig

  JOIN issuetype itt ON oc.optionid = itt.id

[Vikrant Yadav]

@Gerald Lewis That's Great!

Glad to hear you solved this query!