You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.
View groupJoin the community to find out what other Atlassian users are discussing, debating and creating.
Dear all,
I want to query the Jira database to get all the issues (from the jiraissue table) and group them by request type.
After some research I found that the customer request type of an issue is the STRING VALUE in the customerfieldvalue table. I have noticed that the pre-configured customer request type STRING values are in text, e.g. nmits/newhardware while the configured customer request type are in the form of a GUID (e.g. nmits/e05d7597-63b2-4c0a-aff3-e56222353136).
So to get what I want all issues grouped by customer request type I have to create a table with mappings from the nmits/e05d7597-63b2-4c0a-aff3-e56222353136 to a text value. Meaning that I have to add a third table to the below query.
SELECT STRINGVALUE, COUNT(*) FROM jiraissue i
LEFT JOIN customfieldvalue crt
ON i.id = crt.ISSUE
GROUP BY STRINGVALUE
Am I correct or is there an easier way?
You can get the request type by project using this SQL.
SELECT p.pname as project_name, r."NAME" as request_type_name , (po."KEY" || '/' || r."KEY") AS request_type_key FROM "AO_54307E_VIEWPORT" po, "AO_54307E_VIEWPORTFORM" r, project p WHERE po."ID"=r."VIEWPORT_ID" AND po."PROJECT_ID"=p.id ORDER BY p.pname
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
That is really close. You are on the right path. However you are also seeing a number of other string values here that are not specific to service desk request types. You can refine the selection of that SQL query even further to only return request types from service desk with a statement like this:
SELECT STRINGVALUE, COUNT(*) FROM jiraissue i
LEFT JOIN customfieldvalue cfv
ON i.id = cfv.ISSUE
where cfv.customfield = (select id from customfield where cfname = 'Customer Request Type')
GROUP BY STRINGVALUE;
I found that segment of SQL from this bug ticket, https://jira.atlassian.com/browse/JSDSERVER-1583
But it seems to work really well at finding a count to the number of each request type you have in this instance.
Hope this helps.
Andy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
How can we find the actual name of the Customer Request Type in the SQL? what table can i join to to get that?
Example - pcmg/ecfb2f7f-7545-4060-8456-21522edf2137 really is called pcmg/request#1
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I found it in another posting.
select *
from customfieldvalue cfv,
AO_54307E_VIEWPORTFORM a,
customfield b
where b.id = cfv.customfield
and b.cfname = 'Customer Request Type'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
SELECT upper(a.key) projectname, a.key||'/'||af.key request_id, af.name request_name FROM AO_54307E_VIEWPORT a, AO_54307E_VIEWPORTFORM af
WHERE
a.id=af.viewport_id
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
We often have questions from folks using Jira Service Management about the benefits to using Premium. Check out this video to learn how you can unlock even more value in our Premium plan. &nb...
Connect with like-minded Atlassian users at free events near you!
Find an eventConnect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.
Host an eventYou're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.