Querying Issue Links information with Atlassian Analytics

[João Pedro Gomes Ramalho]

Hello, 
I'm trying to build a dashboard using Atlassian Analytics that will showcase the correlation between two specific issue types within a certain project. These types of issues are linked in Jira, but I don't find any mentioning of this relationship within the tables that Atlassian provides.

I've searched on this documentation page: 

https://support.atlassian.com/analytics/docs/schema-for-jira-family-of-products/

but I still found nothing.

In the past I was using the PowerBI Connector to retrieve data from the Jira API, and there was a table under "Jira Work Management" called IssueLinks. This is the information that I'm trying to find within the Atlassian Data Lake tables.

Any idea on how I can get it? 
Thanks in advance! 

Best regards,
-João

[Kishan Sharma · Accepted solution]

Hi @João Pedro Gomes Ramalho 

Unfortunately, this feature is not available at the moment, but there's a feature request currently open for issue link data to be added to the Atlassian Data Lake. I would suggest you to vote for it and watch it for future updates.

In the meantime, you can use the SQL query below as a starting point for your chart. 

with tbl1 as (
  SELECT a.issue_id AS issue_id,
         a.issue_key AS issue_key,
       c.prev_value AS previous_value,
       DATE_FORMAT(c.started_at, 'yyyy-MM-dd HH:mm:ss') AS second_of_started_at,
       c.prev_value_string AS prev_value_string,
       c.value AS value,
       c.value_string AS value_string,
         CASE 
              WHEN c.prev_value_string is NULL then substr(c.value_string, 12, charindex(c.value, c.value_string)-13) 
              ELSE substr(c.prev_value_string, 12, charindex(c.prev_value, c.prev_value_string)-13) 
          END as link_type,
          CASE
              WHEN c.prev_value is NULL then c.value 
              ELSE c.prev_value 
          END as linked_issue,
          CASE 
              WHEN c.prev_value is NULL then "Added"
              ELSE "Removed" 
          END as change_type
FROM jira_issue AS a
INNER JOIN jira_project AS b ON a.project_id = b.project_id
INNER JOIN jira_issue_history AS c ON c.issue_id = a.issue_id
WHERE (c.field = 'Link')
GROUP BY a.issue_id,
           a.issue_key,
         c.prev_value,
         DATE_FORMAT(c.started_at, 'yyyy-MM-dd HH:mm:ss'),
         c.prev_value_string,
         c.value,
         c.value_string,
           link_type,
           linked_issue,
           change_type
ORDER BY a.issue_id ASC,
           a.issue_key ASC,
         c.prev_value ASC,
         DATE_FORMAT(c.started_at, 'yyyy-MM-dd HH:mm:ss') ASC,
         c.prev_value_string ASC,
         c.value ASC,
         c.value_string ASC,
           link_type ASC,
           linked_issue ASC,
           change_type ASC
),

tbl2 as (
  SELECT count(a.issue_key) over (partition by a.issue_key, a.link_type, a.linked_issue order by a.second_of_started_at DESC) as row_num,
  a.issue_id,
  a.issue_key,
  a.link_type,
  a.linked_issue,
  a.change_type
  FROM tbl1 as a
),

tbl3 as (
  SELECT a.issue_id,
  a.issue_key,
  a.link_type,
  a.linked_issue
  FROM tbl2 a
  WHERE a.row_num = 1
  AND a.change_type = "Added"
)

SELECT * from tbl3

Hope this helps.

[João Pedro Gomes Ramalho]

Thank you so much for the feedback :) @Kishan Sharma
Best Regards
-João 

[Kishan Sharma]

Happy to help :)