how to get 'epic name' through SQL Query

[Piyush_Annadate]

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?

[noamdah · Accepted solution]

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]

@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]

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]

@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]

Glad to help. Cheers!

[Peyyala Madhusudhana Rao]

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

[Neil Stewart]

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)

[noamdah]

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.

[Pranaychandra ravi]

-- 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