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

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,459,792
Community Members
 
Community Events
176
Community Groups

Customer Request Type in Jira Database

Edited

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?

3 answers

4 votes

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

how can I get to issue level customer request types ?

@Vikram Gokhale Quick way, you can export the issues into CSV file.

Or, you can run this SQL:

SELECT (p.pkey || '-' || i.issuenum) AS issuekey,cfv.stringvalue
FROM customfieldvalue cfv
JOIN jiraissue i ON cfv.issue=i.id
JOIN project p ON i.project=p.id
WHERE customfield=(SELECT id FROM customfield WHERE cfname='Request Type')
ORDER BY p.pkey,i.issuenum;
  • You can further narrow down the searches by project with p.pkey=project_key

You can run this SQL queriy:

 

SELECT A.NAME AS ProjectName,
A.ID AS ProjectID,
B.NAME AS CustomerRequestType,
B.ID AS CustomerRequestTypID
FROM
"AO_54307E_VIEWPORT" AS A
INNER JOIN "AO_54307E_VIEWPORTFORM" AS B
ON B.VIEWPORT_ID = A.ID
WHERE A.PROJECT_ID = "ProjectID"

0 votes
Andy Heinzer Atlassian Team Mar 12, 2018

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

 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

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' 

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

Like Michael Raj likes this

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events