nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Roman Kersky

HI.

All comments

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.

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

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,

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

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.

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

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

Epic Link Related.jpg

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

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;