Query the database to find details about builds

Still need help?

The Atlassian Community is here for you.

Ask the community

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:

FieldValue
Project NameThis 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.

Last modified on Nov 30, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.