How to fetch complete plan details in the particular Bamboo project using database queries
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
This article explains how to generate plan details for a particular Bamboo project from the database.
Building SQL statements for reporting purposes is not part of the Atlassian Support scope and this work is provided "as-is", on a best-effort basis. Queries may work better or worse than expected, your mileage may vary. It is up to each customer to analyze each query individually and understand if that is enough for their specific needs.
Environment
The SQL queries listed on this page have been tested on Bamboo 8.1.3 but may work with other versions of Bamboo as well.
Solution
The SQL queries listed in this article cover the following reports:
To fetch the project_ID for a particular project
The following SQL query will fetch the project_ID for a particular project:
SELECT PROJECT_ID
FROM PROJECT P
WHERE PROJECT_KEY='NEW';
Where 'NEW' is the project KEY.List of plans,plan branches,jobs for a particular project_ID
The following SQL query will generate a list of all plans, plan branches and jobs for a particular project_ID:
SELECT B.*
FROM PROJECT P
JOIN
BUILD B
ON P.PROJECT_ID=B.PROJECT_ID
WHERE
P.PROJECT_ID='1048577' ORDER BY FULL_KEY
Where '1048577' is the project ID.