Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

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

how to get 'epic name' through SQL Query

i would like to have a SQL query wherein I can get the epic name of a story.

The database architecture 'n schema appears to be more confusing.This doesn't even describe much of details (https://developer.atlassian.com/jiradev/jira-platform/jira-architecture/database-schema#Databaseschema-Customfields)

Likewise - I do have tables customfield and customfieldvalue from where I can have the required 'stringvalue' as epic name but id and issue column makes more confusion as customfieldvalue.id is from nowhere to generate?

4 answers

1 accepted

2 votes
Answer accepted

Run this query and replace the 10111 value to the relevant custom field value of your JIRA instance:

select p.pkey, ji.issuenum, cfv.STRINGVALUE
from jiraissue ji join project p on ji.PROJECT = p.ID 
join customfieldvalue cfv on ji.ID = cfv.ISSUE 
where cfv.CUSTOMFIELD = 10111;

Output will contain the issue key and the custom field value.

@Noam Dahan thanks that works - but it returns epic issuetype list. What if I want to pass a pkey and issuenum=1836 (1836 will be key for story issuetype)?

 

select p.pkey, ji.issuenum, cfv.STRINGVALUE,ist.pname
from jiraissue ji join project p on ji.PROJECT = p.ID
join customfieldvalue cfv on ji.ID = cfv.ISSUE
join issuetype ist on ji.issuetype = ist.id
where cfv.CUSTOMFIELD = 10004 and p.pkey='ATPCWS' and ji.issuenum=1836

Yes, the output columns will show: 
pkey = the project key of relevant issue
issuenum = the number of the relevant issue
stringvalue = the custom filed value for that issue

@Noam Dahan,

Query you provided worked; but output was only with EPIC issue type - wherein I was looking for a STORY ID to be passed and get the epic link 'name' (thats stringvalue). Your code really helped

I ended up creating the below one:

select ji.issuenum AS Epic_Key, cfv.STRINGVALUE AS Epic_Name
from jiraissue ji
join customfieldvalue cfv on ji.ID = cfv.ISSUE 
join issuetype ist on ji.issuetype = ist.id 
where cfv.CUSTOMFIELD = 10004 
and ji.issuenum=(
select issuenum from jiraissue where id=(
select islk.source
from 
jiraissue ji join issuelink islk on ji.id=islk.source
join issuelinktype ilt on islk.linktype=ilt.id
join jiraissue c on islk.destination = c.id
where
ilt.linkname='Epic-Story Link' and c.id=(select jre.id from jiraissue jre, project prj where jre.project=prj.id and prj.pkey='ATPCWS' and jre.issuenum=1836))
)

Glad to help. Cheers!

How do i get the epic issuetype  jira issues where the epic_name is empty. I have case like this. Any help is appreciated.

Peyyala,

SELECT p.pkey || '-' || ji.issuenum AS issueid,cfv.stringvalue AS epicname
FROM jiraissue ji
LEFT JOIN project p ON p.id=ji.project
LEFT JOIN customfieldvalue cfv ON cfv.customfield=(SELECT id FROM customfield
    WHERE cfname LIKE 'Epic Name') AND cfv.issue=ji.id
WHERE ji.issuetype=(SELECT id FROM issuetype WHERE pname LIKE 'Epic') AND (cfv.stringvalue IS NULL OR cfv.stringvalue = '')
ORDER BY p.pkey, ji.issuenum ;

Although this should never happen as the Epic Name field is required.

I was able to grab the Epic name in jira through looking at the issuelinktype table to see what id is the epic link set to.

Then I looked at the issuelink table and filtered that to only show the epic link type.

I did a left join to join based on the source(epic issuenum) and destination(task issuenum)

select
    j.id
    ,el.summary AS "Epic Name"
    ,j.issuenum

from jiraissue as j

left join (select
                 issuelink.id,
                 issuelink.linktype,
                 issuelink.source,
                 issuelink.destination,
                 jiraissue.id,
                 jiraissue.summary
               from issuelink
                 left join jiraissue
                   on (issuelink.source = jiraissue.id)
               where
                 issuelink.linktype=<insert id>
                 and jiraissue.id is not null
               ) as el on (el.destination = j.id)

-- This will print the jira issue number ,epic name and time spent by any author 
-- The query is time bound
-- mainly focused at grouping the time logged by any individual user
-- in relation to all the tasks that belong to an epic
-- give it a shot ,the source and the destination in the issuelink table help connect any
-- task to the epic it is linked to

select p.pkey || '-' || ji.issuenum AS Task_Number,(select summary from jiraadmin.JIRAISSUE where id=il.source ) as epic_name ,ji.summary Task_Name,ji.timespent/60 TIMESPENT_IN_MINUTES
from jiraadmin.JIRAISSUE ji,jiraadmin.PROJECT p ,jiraadmin.issuelink il
where
ji.id in (select issueid from jiraadmin.WORKLOG where author='<author name>' and trunc(created)>to_date('4/1/2019','mm,dd,yyyy'))
and p.id=ji.project
and ji.ID = il.destination(+)
order by epic_name desc

 

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Management

JSM June ask me anything (AMA)

Hello Community members! We’re wrapping up the end of JSM June with an Ask Me Anything (AMA) with the Jira Service Management product team. This is your chance to ask all your ITSM questions to o...

154 views 9 10
Read article

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