How to query Issue Link Types?

Is there a way to query the link type (e.g., "causes") of linked issues?

Screenshot 2024-10-07 144116.png

I am able to return linked issues using the method in this post:

https://confluence.atlassian.com/analyticskb/query-linked-jira-issues-1387597007.html

I just don't see a way to return only the linked issues that were linked as "causes" or "caused by".

2 answers

1 accepted

0 votes

Answer accepted

Hi Eric,

Here is an example SQL code you can use for querying Atlassian Data Lake to capture the link type.

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

Let me know if this works for you!