DB query to get Issues in EPIC

[Santosh V]

I need help on DB Query to get list of issues which is linked to EPIC in JIRA application.

Below is the list of issues which is linked in EPIC.

Epic Name is "My Epic"

In Above screenshot, there are 2 issues has been linked to Epic. Need help to get same data in SQL query 

Regards

Santosh

[Santosh V · Accepted solution]

Finally i found it. Below the query which gives list of issues which is linked to Epic

SELECT
li.summary as ISSUES_IN_EPIC, I.ID AS ISSUELINKID
FROM
JIRAISSUE I
INNER JOIN ISSUELINK IL
ON IL.SOURCE = I.ID
INNER JOIN ISSUELINKTYPE ILT
ON ILT.ID = IL.LINKTYPE
INNER JOIN JIRAISSUE LI
ON LI.ID = IL.DESTINATION
INNER JOIN PROJECT PJ
ON PJ.ID = I.PROJECT
INNER JOIN ISSUETYPE IT
ON IT.ID = I.ISSUETYPE
where IT.PNAME = 'Epic'

[Filipi Lima]

I did a bit of tweaking on what @Santosh V provided and I think it could be useful for others.

To get only issues linked using the "Epic-Story Link" link type, look for the ID on issuelinktype table and replace the 10101 I have at the very end.

It was tested on postgres so you might need to tweak how to concat fields depending on your DB product.

A bit of a glossary for the alias:

PJP: project of the parent
JIP: jiraissue for the parent
PJC: project of the child
JIC: jiraissue for the child

SELECT JIC.summary as child_summary,
       concat(concat(PJP.pkey,'-'),JIP.issuenum) as parent_key, JIP.ID as parent_issue_id,
       concat(concat(PJC.pkey,'-'),JIC.issuenum) as child_key, JIC.ID as child_issue_id,
       ILT.linkname
FROM
JIRAISSUE JIP
INNER JOIN ISSUELINK IL ON IL.SOURCE = JIP.ID
INNER JOIN ISSUELINKTYPE ILT ON ILT.ID = IL.LINKTYPE
INNER JOIN JIRAISSUE JIC ON JIC.ID = IL.DESTINATION
INNER JOIN PROJECT PJP ON PJP.ID = JIP.PROJECT
INNER JOIN PROJECT PJC ON PJC.ID = JIC.PROJECT
INNER JOIN ISSUETYPE IT ON IT.ID = JIP.ISSUETYPE
where IT.PNAME = 'Epic'; --and ILT.id = 10101; 

[Tarun Sapra]

Hello @Santosh V 

Why do you want to do a DB query?

You can do the query in Jira native JQL and export the results in the CSV/Excel

[Tarun Sapra]

"Epic Link" = <key of epic>

Above is based on out of the box features

And also possible using JQL functions from paid plugins like SR

https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html#_issuesinepics

[Santosh V]

I need DB query (PostgreSQL) because am building dashboard report in power BI.

so it would be great to provide db query 

Regards

Santosh

[Tarun Sapra]

Hello @Santosh V 

As per this blog - https://powerbi.microsoft.com/en-us/blog/explore-your-jira-data-with-power-bi/

I don't think it's required to write SQL queries yourself to extract the data from Jira and display via power BI

[Om Singh]

@Tarun Sapra 

I am building a dashboard in looker.
I have got all the relevant tables/views into the looker, joined on the appropriate id.

 Looks like directly pulling attributes doesn’t solve the problem .

Doest it require to write SQL query for each details and create a seperate derived table ?

@Santosh V how did you solve your problem ?