It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Getting the issue type scheme of all projects in database.

Hi!

It's very important for me get the issue type scheme associated of all projects in database. I dont find the way to get it. I don't know wich query I should execute.

Plese, help me.

Thanks so much!

4 answers

4 votes

Hi David,

This should return all Issue Types associated to the project "Test".

select IT.pname from project P
join configurationcontext CC on P.id = CC. project
join fieldconfigschemeissuetype FCI on FCI.fieldconfigscheme = CC.fieldconfigscheme
join optionconfiguration OC on OC.fieldconfig = FCI.fieldconfiguration
join issuetype IT on IT.id = OC.optionid
where P.pname = 'Test'

Hope this helps

Able to get the results as expected.

Thank you.

Thanks Marcus, but i need all the projects that doesnt have the default issue type scheme.

I think the correct query is:

select project.pname, pkey
from project
where project.ID not in (
select project.ID
from project
join configurationcontext
on project.ID = configurationcontext.PROJECT
where configurationcontext.customfield = "issuetype"
and configurationcontext.FIELDCONFIGSCHEME != 10000
and configurationcontext.FIELDCONFIGSCHEME is not null)

Thanks Marcus!

Hi David,

I thought you needed all issues types associated to a specific project.

My bad :)

Your query does seem to get the expected results.

If you need any help, just let me know.

Can you please help to get a list of projects which has issuetype 'Bug'?

Hi,

I was dealing this too and here is my query (MySQL).

This returns list of all projects (key, name), associated issue type scheme and list of all issue types.

Adjust this query accordingly based on your needs.

SELECT
p.pkey AS 'Project Key',
p.pname AS 'Project Name',
its.configname AS 'Issue Type Scheme',
its.issuetypes AS 'Issue Types'
FROM project p
LEFT JOIN configurationcontext cc ON p.ID = cc.PROJECT
LEFT JOIN (
SELECT
fcsit.FIELDCONFIGSCHEME,
fcsit.FIELDCONFIGURATION,
fcs.configname,
GROUP_CONCAT(it.pname ORDER BY oc.SEQUENCE SEPARATOR ', ') AS issuetypes
FROM optionconfiguration oc
LEFT JOIN issuetype it ON it.ID = oc.OPTIONID
LEFT JOIN fieldconfigschemeissuetype fcsit ON fcsit.FIELDCONFIGURATION = oc.FIELDCONFIG
LEFT JOIN fieldconfigscheme fcs ON fcs.ID = fcsit.FIELDCONFIGSCHEME
GROUP BY oc.FIELDCONFIG) its ON its.FIELDCONFIGSCHEME = cc.FIELDCONFIGSCHEME
WHERE cc.customfield = 'issuetype'
-- HAVING its.issuetypes RLIKE 'Support Ticket'
;

Using the last "HAVING" statement and RLIKE or NOT RLIKE you can filter for various  issue types.

Works Great!

Thank you

@Radek Janata Hi

I get below error, any idea how can this be addressed?

[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jiradb.fcsit.FIELDCONFIGSCHEME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

@Grzegorz Ziolek

What error do you mean? Nothing was attached.

You probably run your Jira on different DB than MySQL. You might need to change some parts to work it on different DB. 

I'm using mysql.

The error I get is:

[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jiradb.fcsit.FIELDCONFIGSCHEME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Your MySQL is configured differently. You might want to disable ONLY_FULL_GROUP_BY -- see https://stackoverflow.com/questions/23921117/disable-only-full-group-by.

Or try to adjust the GROUP BY fields with all the fields from SELECT:

 GROUP BY fcsit.FIELDCONFIGSCHEME, fcsit.FIELDCONFIGURATION, fcs.configname, oc.FIELDCONFIG) its ON its.FIELDCONFIGSCHEME = cc.FIELDCONFIGSCHEME

@Radek Janata 

Thanks, this helped :) 

One more question, when running this query I get all projects except those using  Default issue type scheme. Looks like those projects are not in configurationcontext table.

Any idea what can be done in this situation?

@Grzegorz Ziolek, good point! This is fixed query:

SELECT
p.pkey AS 'Project Key',
p.pname AS 'Project Name',
its.configname AS 'Issue Type Scheme',
its.issuetypes AS 'Issue Types'
FROM project p
LEFT JOIN configurationcontext cc ON p.ID = cc.PROJECT AND cc.customfield = 'issuetype'
LEFT JOIN (
SELECT
fcsit.FIELDCONFIGSCHEME,
fcsit.FIELDCONFIGURATION,
fcs.configname,
GROUP_CONCAT(it.pname ORDER BY oc.SEQUENCE SEPARATOR ', ') AS issuetypes
FROM optionconfiguration oc
LEFT JOIN issuetype it ON it.ID = oc.OPTIONID
LEFT JOIN fieldconfigschemeissuetype fcsit ON fcsit.FIELDCONFIGURATION = oc.FIELDCONFIG
LEFT JOIN fieldconfigscheme fcs ON fcs.ID = fcsit.FIELDCONFIGSCHEME
GROUP BY fcsit.FIELDCONFIGSCHEME, fcsit.FIELDCONFIGURATION, fcs.configname, oc.FIELDCONFIG) its ON its.FIELDCONFIGSCHEME = cc.FIELDCONFIGSCHEME OR cc.FIELDCONFIGSCHEME IS NULL AND its.FIELDCONFIGSCHEME = 10000
-- HAVING its.issuetypes RLIKE 'Support Ticket'
ORDER BY p.pkey
;

And this was removed:

WHERE cc.customfield = 'issuetype' 

The following SQL (SQL Server) will retrieve the name of the Issue Types Scheme used on project(s):

SELECT prj.pkey as 'Project Key', prj.pname as 'Project Name'
,(CASE -- get the Issue Types scheme
WHEN EXISTS(select cc.FIELDCONFIGSCHEME from [configurationcontext] cc where cc.customfield = 'issuetype' AND cc.PROJECT = prj.ID) THEN
(select top 1 fc.configname from [fieldconfigscheme] fc
where fc.ID = ( select top 1 cc.FIELDCONFIGSCHEME from [configurationcontext] cc
where cc.customfield = 'issuetype' AND cc.PROJECT = prj.ID) )
ELSE
(select fc.configname from [fieldconfigscheme] fc
where fc.ID = 10000) -- Default Issue Type Scheme
END) as 'Issue Types Scheme Name'
FROM [project] prj
WHERE prj.pkey = 'your desired project key

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Demo Den Ep. 7: New Jira Cloud Reports

Learn how to use two new reports for next-gen projects in Jira Cloud:  Cumulative flow diagram and Sprint burndown chart. Ivan Teong, Product Manager, Jira Software, demos the Cumulative ...

344 views 1 3
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you