How to get Epic Link(custom field) from JIRA Data base?

[M RAMAMOHAN REDDY]

Hi Team,

How to get Epic Link(customer field) from JIRA Database?

I have tried with below query but did not get any values for Epic Link

select CF.cfname,JI.id,JI.issuenum,CFV.stringvalue
from customfieldvalue CFV
INNER JOIN jiraissue JI ON CFV.issue = JI.id
INNER JOIN customfield CF ON CF.id = CFV.customfield
WHERE CF.cfname like '%Epic Link%'

[Roman Kersky · Accepted solution]

HI.

[M RAMAMOHAN REDDY]

Thanks Roman,

tested Epic Link value for few stories based on the above query and The above query returns Epic Name for particular story . but i am looking for Epic Link

could anyone please help me on this 

[Roman Kersky]

Epic Link contains a link to Epic. It's necessary ?

[M RAMAMOHAN REDDY]

Also one more thing we don't see any values for Epic Link in customfieldvalue table

[M RAMAMOHAN REDDY]

Yes , user is expecting Epic Link in the report. is there any alternate to pull the same info

[Roman Kersky]

Epic Link - it's link on Epic and not contains in table customfieldvalue 

and fist comment I use table "issue link".

[M RAMAMOHAN REDDY]

Thanks Roman for explaining 

is there any way that we can pull Epic Link value from JIRA Data base 

[Roman Kersky]

Please try do how on picture 

And delete this line " and cfv.STRINGVALUE = 'Global Homepage Rollout'" to show all epic link

[M RAMAMOHAN REDDY]

Thanks Roman for sharing details

Tested for few stories with the above query and i am able to get Epic link  most of the stories and not able to get few stories.

Thanks a lot for your help.

[Anudeep Gudipelli]

Hi Rammohan, 

below is the query for Jira 9.X Datacenter Version, the PSQL query returns the Epic link and also Parent issue number for a Sub-task.

SELECT
ji.id, ji.issuenum, (jp.pkey || '-' || ji.issuenum) AS "Issue Key", i.pname AS "Issue Type",cfv_epic_name.stringvalue AS "Epic Name", il.source AS link_source,
il.destination AS link_destination, ilt_epic.linkname as "Epic Link Type",
ilt.linkname as "Parent link Type",
MAX(CASE
WHEN ilt_epic.linkname = 'Epic-Story Link' AND ji.id = il_epic.source THEN ''
WHEN ilt_epic.linkname = 'Epic-Story Link' THEN source_jp_epic.pkey || '-' || source_ji_epic.issuenum
ELSE NULL
END) AS "Epic Link",
MAX(CASE
WHEN ilt.linkname = 'jira_subtask_link' AND ji.id = il.source THEN ''
WHEN ilt.linkname = 'jira_subtask_link' THEN source_jp.pkey || '-' || source_ji.issuenum
ELSE NULL
END) AS "Parent Issue Number"

FROM
project jp
JOIN jiraissue ji ON jp.id = ji.project
JOIN issuetype i ON ji.issuetype = i.id
-- Joins for Epic Name
LEFT JOIN customfield cf_epic_name ON cf_epic_name.cfname = 'Epic Name'
LEFT JOIN customfieldvalue cfv_epic_name ON cfv_epic_name.issue = ji.id AND cfv_epic_name.customfield = cf_epic_name.id
-- Joins for Epic-Story Link
LEFT JOIN issuelink il_epic ON ji.id = il_epic.source OR ji.id = il_epic.destination
LEFT JOIN issuelinktype ilt_epic ON il_epic.linktype = ilt_epic.id AND ilt_epic.linkname = 'Epic-Story Link'
LEFT JOIN jiraissue source_ji_epic ON il_epic.source = source_ji_epic.id
LEFT JOIN project source_jp_epic ON source_ji_epic.project = source_jp_epic.id
-- Joins for Issue Links
LEFT JOIN issuelink il ON ji.id = il.source OR ji.id = il.destination
LEFT JOIN issuelinktype ilt ON il.linktype = ilt.id and ilt.linkname in( 'jira_subtask_link')
-- Join to get details of the source issue
LEFT JOIN jiraissue source_ji ON il.source = source_ji.id
LEFT JOIN project source_jp ON source_ji.project = source_jp.id
--Join to get details of the destination issue
left join jiraissue dest_ji on il.destination = dest_ji.id
left join project dest_jp on dest_ji.project = dest_jp.id

where jp.pkey = 'ABC' -- change the project key here 

GROUP by ji.id, ji.issuenum, (jp.pkey || '-' || ji.issuenum), i.pname,cfv_epic_name.stringvalue,il.source,il.destination,ilt.linkname,ilt_epic.linkname;

[Roman Kersky]

select link.DESTINATION, cfv.STRINGVALUE from db.[issuelink] link

join db.customfieldvalue cfv on cfv.ISSUE = link.SOURCE and 

cfv.CUSTOMFIELD = (select ID from db.customfield CF WHERE CF.cfname = 'Epic Name')

join db.jiraissue ji on ji.ID = link.SOURCE

where LINKTYPE = (select ID from db.[issuelinktype] where LINKNAME = 'Epic-Story Link')

[Roman Kersky]

HI,

select link.DESTINATION, cfv.STRINGVALUE from db.[issuelink] link
--
join db.customfieldvalue cfv on cfv.ISSUE = link.SOURCE and
cfv.CUSTOMFIELD = (select ID from db.customfield CF WHERE CF.cfname = 'Epic Name')
join db.jiraissue ji on ji.ID = link.SOURCE

where LINKTYPE = (select ID from db.[issuelinktype] where LINKNAME = 'Epic-Story Link')

[Roman Kersky]

Hi, 

select link.DESTINATION, cfv.STRINGVALUE from db.[issuelink] link
join db.customfieldvalue cfv on cfv.ISSUE = link.SOURCE and
cfv.CUSTOMFIELD = (select ID from db.customfield CF WHERE CF.cfname = 'Epic Name')
join db.jiraissue ji on ji.ID = link.SOURCE

where LINKTYPE = (select ID from db.[issuelinktype] where LINKNAME = 'Epic-Story Link')

[Roman Kersky]

HI. Try this 

select link.DESTINATION, cfv.STRINGVALUE from db.[issuelink] link
join db.customfieldvalue cfv on cfv.ISSUE = link.SOURCE and
cfv.CUSTOMFIELD = (select ID from db.customfield CF WHERE CF.cfname = 'Epic Name')
join db.jiraissue ji on ji.ID = link.SOURCE

where LINKTYPE = (select ID from db.[issuelinktype] where LINKNAME = 'Epic-Story Link')

[Roman Kersky]

Hi. 

select link.DESTINATION, cfv.STRINGVALUE from db.[issuelink] link

join db.customfieldvalue cfv on cfv.ISSUE = link.SOURCE and
          cfv.CUSTOMFIELD = (select ID from db.customfield CF WHERE CF.cfname = 'Epic Name')
join db.jiraissue ji on ji.ID = link.SOURCE

where LINKTYPE = (select ID from db.[issuelinktype] where LINKNAME = 'Epic-Story Link')

[Roman Kersky]

HI. Epic Link contains table issuelink =)

select link.DESTINATION, cfv.STRINGVALUE from db.[issuelink] link

join db.customfieldvalue cfv on cfv.ISSUE = link.SOURCE and
cfv.CUSTOMFIELD = (select ID from db.customfield CF WHERE CF.cfname = 'Epic Name')
join db.jiraissue ji on ji.ID = link.SOURCE

where LINKTYPE = (select ID from db.[issuelinktype] where LINKNAME = 'Epic-Story Link')

[Tarun Sapra]

Hello @M RAMAMOHAN REDDY 

Using the Java APIs you can easily get the value of this field, can you tell me the business case as to why you need to query the DB to get this field value.

[M RAMAMOHAN REDDY]

Thanks Tarun 

Basically we are creating a report for all sub tasks with some fields and then we are doing roll up to parent task level 

[Tarun Sapra]

Hello @M RAMAMOHAN REDDY 

SQL quering Jira DB isn't easy and can be error prone if anything minor is wrong in the query. Hence, I suggest to you jira dashboards or jira reporting plugin to show relevant sub-tasks.

There are plenty of plugins available on marketplace which support JQL functions which can query hierarchy from epic till sub-task level.

[Tarun Sapra]

https://marketplace.atlassian.com/apps/1214791/jql-search-extensions-for-jira-reports?hosting=cloud&tab=overview

Script runner

https://marketplace.atlassian.com/apps/6820/scriptrunner-for-jira?hosting=server&tab=overview

[M RAMAMOHAN REDDY]

Thanks Tarun for your help