You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
Next: Root
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
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.
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;
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Andy Heinzer Can you please help us with SQL query ?
I am not able to create SQL query to get details.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Gerald Lewis That's Great!
Glad to hear you solved this query!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.