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.

[Joshwa Marcalle]

Hi Jon,

Scriptrunner is nice, but for automated subtasks it is a bit heavy, specially if you need to change the schemes frequently for many users. 

Have you considered a specialised addon for managing automated subtasks? I would recommend you this one: https://marketplace.atlassian.com/apps/1218418/automatic-subtasks-professional?hosting=cloud&tab=overview

Hope this helps

Josh
Ob1