how to get 'epic name' through SQL Query

Piyush_Annadate September 20, 2016

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
noamdah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 20, 2016

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.

Piyush_Annadate September 20, 2016

@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
noamdah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 20, 2016

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

Piyush_Annadate September 21, 2016

@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))
)
noamdah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 21, 2016

Glad to help. Cheers!

Peyyala Madhusudhana Rao May 16, 2017

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

1 vote
Neil Stewart February 6, 2019

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)

1 vote
noamdah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 16, 2017

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.

0 votes
Pranaychandra ravi June 18, 2019
-- 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
AUG Leaders

Atlassian Community Events