Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

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

Edited

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.

1 answer

0 votes
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.
Oct 29, 2023

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.

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Oct 30, 2023

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

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

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

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.
Nov 02, 2023

@Gerald Lewis That's Great!

Glad to hear you solved this query!

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events