Query the database to find details about builds
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the server and data center platforms.
The steps outlined on this article are provided AS-IS. This means we've had reports of them working for some customers — under certain circumstances — yet are not officially supported, nor can we guarantee they'll work for your specific scenario.
You may follow through and validate them on your own non-prod environments prior to production or fall back to supported alternatives if they don't work out.
We also invite you to reach out to our Community for matters that fall beyond Atlassian's scope of support!
Summary
SQL Query to get details about the builds.
Environment
Tested on 6.10.4 with Postgres.
Solution
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Run the following SQL query against the Bamboo database to find details about the builds:
SELECT P.TITLE AS "Project_Name",
B.TITLE AS "Build_Plan_Name",
CONCAT('<your_bamboo_url>/browse/', B.FULL_KEY) AS "Build_Plan_Link",
(SELECT COUNT(*) FROM BUILDRESULTSUMMARY BRS WHERE BRS.BUILD_KEY = B.FULL_KEY AND BRS.BUILD_TYPE NOT IN ('BUILD') AND BRS.TRIGGER_REASON NOT LIKE '%Manual%' AND BRS.BUILD_DATE > current_date - interval '30' day) AS "Build_Activity",
CONCAT(ABS(FLOOR((SELECT COALESCE(AVG(EXTRACT(MINUTE FROM (BUILD_DATE - QUEUE_TIME))), 0) FROM BUILDRESULTSUMMARY BRS WHERE BRS.BUILD_KEY = B.FULL_KEY AND BRS.BUILD_TYPE NOT IN ('BUILD')))), ' mins') AS "Average_Queue_Duration",
(SELECT CONCAT(FLOOR(COALESCE(AVG(DURATION), 0)/60000), ' mins') FROM BUILDRESULTSUMMARY BRS WHERE BRS.BUILD_KEY = B.FULL_KEY AND BRS.BUILD_TYPE NOT IN ('BUILD') AND BRS.BUILD_DATE > current_date - interval '30' day) AS "Average_Build_Duration",
(SELECT COALESCE(FLOOR(AVG(BRS.TOTAL_TEST_COUNT)), 0) FROM BUILDRESULTSUMMARY BRS WHERE BRS.BUILD_KEY = B.FULL_KEY AND BRS.BUILD_TYPE NOT IN ('BUILD') AND BRS.BUILD_STATE = 'Successful') AS "Average_Tests",
CONCAT(ROUND((SELECT CAST(COUNT(*) AS FLOAT) FROM BUILDRESULTSUMMARY BRS WHERE BRS.BUILD_KEY = B.FULL_KEY AND BRS.BUILD_TYPE NOT IN ('BUILD') AND BRS.BUILD_STATE = 'Failed')/(SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE COUNT(*) END FROM BUILDRESULTSUMMARY BRS WHERE BRS.BUILD_KEY = B.FULL_KEY AND BRS.BUILD_TYPE NOT IN ('BUILD'))*100), ' %') AS "Percentage_of_Failure_Builds",
CONCAT(ROUND((SELECT CAST(COUNT(*) AS FLOAT) FROM BUILDRESULTSUMMARY BRS WHERE BRS.BUILD_KEY = B.FULL_KEY AND BRS.BUILD_TYPE NOT IN ('BUILD') AND BRS.BUILD_STATE = 'Successful')/(SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE COUNT(*) END FROM BUILDRESULTSUMMARY BRS WHERE BRS.BUILD_KEY = B.FULL_KEY AND BRS.BUILD_TYPE NOT IN ('BUILD'))*100), ' %') AS "Percentage_of_Successful_Builds"
FROM PROJECT P
INNER JOIN BUILD B ON (P.PROJECT_ID = B.PROJECT_ID)
WHERE B.BUILD_TYPE NOT IN ('JOB', 'CHAIN_BRANCH')
ORDER BY 1, 2;This query returns the following information:
| Field | Value |
|---|---|
| Project Name | This field returns the project name. |
Build Plan Name | This field returns the plan name. |
Build Plan Link | This field returns the build plan link. |
Build Activity | This field returns how many times the plan was triggered in the last 30 days. |
Average Queue Duration | This field returns the average of the queue duration of the build plan (in minutes). |
Average Build Duration | This field returns the average of the build duration for the last 30 days of the build plan (in minutes). |
Average Tests | This field returns the average of successful tests of the build plan. |
Percentage of Failure Builds | This field returns the count of the failed builds divided by the total of builds executed. |
Percentage of Successful Builds | This field returns the count of the successful builds divided by the total of builds executed. |