How to view the Jira issues linked to a Bamboo Data Center test results via Database queries
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
The purpose of this article is to provide details of the Database tables which stores the Jira issues linked to a test case and provide a SQL query to fetch that.
Currently this information can only be seen via Bamboo GUI ( refer below ) under each test cases for which the Jira issue is created and there is a feature request raised to fetch this information via Rest API, details at BAM-25530 - Getting issue details... STATUS
Environment
The solution has been tested on Bamboo 9.6.0 and Postgres SQL DB, but it is applicable for other supported version as well and the query can be run on other DB types as well.
Solution
Below is the structure of the test_case table and the Jira issue information is stored under column linked_jira_issue.
The below SQL can be used to fetch all the Jira issues created for a main plan and can be modified accordingly based on requirement.
SELECT DISTINCT B.FULL_KEY AS plan_key,
B.TITLE AS plan_name,
TCA.LINKED_JIRA_ISSUE AS jira_issue
FROM BUILD B,
TEST_CLASS TC,
TEST_CASE TCA
WHERE B.BUILD_ID = TC.PLAN_ID
AND TC.TEST_CLASS_ID = TCA.TEST_CLASS_ID
AND B.BUILD_TYPE = 'CHAIN'
AND TCA.LINKED_JIRA_ISSUE IS NOT NULL