Query linked Jira issues
In Jira, it is common to link issues together. For example, you may have an epic that work is blocked until a bug is fixed. In that scenario, you can link the bug to the epic and let other users know that the bug ticket blocks the epic’s progress.
You can use Atlassian Analytics to query issues that are linked together. Let’s walk through how to create a chart that finds all the issues that are linked together.
Querying linked issues in a chart
In this example, we’re creating a chart that is listing the issues that are linked together. We can query linked issues by using the following steps:
Open the chart editor by creating a new chart or editing an existing chart.
In your visual mode query, add the Value column from the Issue history table under “Jira family of products”. Choose the aggregation Group for the column.
Then add the Issue key column from the Issue table under “Jira family of products”. Change the aggregation of the column to Group.
Add a filter to the query, where the Field column from the Issue history table equals Link.
Optional: Rename the Value column to “Issue linked to” and the Issue key column to “Linked issues”.
Run the query!
The chart will then display all issues that have been linked together within your site.
Querying linked issues as a custom table
If you would like to display your linked issues in multiple charts, then you might want to consider creating a custom table. To query your linked issues through a custom table you can use the following steps to create a custom table:
1. In Atlassian Analytics, select Data from the top navigation bar.
2. Select the Atlassian Data Lake connection that you would like to customize the schema.
3. In your Atlassian Data Lake connection, navigate to the Schema tab.
4. Select Add Custom Table at the top of your Atlassian Data Lake connection’s schema.
5. A modal will open where you can name the custom table and provide the SQL used to create the table. To create a custom table that has the linked issues, you can paste the following SQL into the modal.
SELECT jira_issue.issue_key AS `Linked issue`,
jira_issue_history.value AS `Issue linked to`,
jira_issue_history.field
FROM jira_issue
INNER JOIN jira_issue_history ON jira_issue.issue_id = jira_issue_history.issue_id
WHERE jira_issue_history.field = 'Link'
GROUP BY ALL6. Select Add Table to save the custom table to your Atlassian Data Lake connection’s schema.
If you want to be able to query this custom table with other tables in the schema, you will need to set up foreign keys.
An example foreign key you could set up would be using the “Issue linked to” column to connect this custom table to the Issue table. The screenshot below is what the example foreign key would look like once it was configured.
