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.

[Zihni Saglam · Accepted solution]

Hi Rebecca

I think the issue might be that you haven't yet updated to Insight version 5.7.0 or higher which is required for this Insight Discovery version.

Let me know if this resolves the issue. 

[rebecca.tarling@baesystems.com]

Thanks you. I hadn't understood the dependency between the two.

I've now installed Insight and then Insight Discovery.

[Espen Sandall]

Ran into the same problem. 

When the task is to simply *install* Insight Discovery and Asset Mgmt, with no prior knowledge about these apps, it's not necessarily a given that one has to be installed before the other.

Would it really hurt to mention this dependency among the basic installation steps here? -> https://marketplace.atlassian.com/apps/1214668/insight-discovery?hosting=server&tab=installation

Regards,
Espen

[Nader Nassif]

.