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.
Support for Server* products will end after 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
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 Queries to retrieve details about completed builds.
Environment
Tested on Atlassian Bamboo 9.3.2 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 (VCS_UPDATE_TIME - 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. |
The below DB query would display the processing time of all the jobs in Bamboo and the agent which executed it
SELECT BRS.BUILD_KEY AS "Job_Key",
BRS.PLAN_NAME AS "Job_name",
BRS.BUILD_DATE,
BRS.BUILD_NUMBER,
BRS.PROCESSING_DURATION / 1000 :: REAL "Build_duration_seconds",
Q.TITLE AS agent_name
FROM BUILDRESULTSUMMARY BRS,
QUEUE Q
WHERE BRS.BUILD_AGENT_ID = Q.QUEUE_ID
GROUP BY BRS.BUILD_KEY,
BRS.PLAN_NAME,
BRS.BUILD_DATE,
BRS.BUILD_NUMBER,
BRS.PROCESSING_DURATION,
Q.TITLE
ORDER BY Q.TITLE, BRS.BUILD_KEY;This query will return all plans that have not been built in 12 months:
SELECT *
FROM (SELECT B.FULL_KEY, LOWER(L.USER_NAME) AS created_by_username, COALESCE(MAX(BRS.build_completed_date), '1000-01-01 00:00:00') AS LAST_BUILD_DATE
FROM BUILD B JOIN AUDIT_LOG L ON B.FULL_KEY = L.ENTITY_ID
LEFT JOIN buildresultsummary brs ON full_key = build_key AND brs.build_key IS NOT NULL AND b.build_type IN ('CHAIN', 'CHAIN_BRANCH')
GROUP BY B.FULL_KEY, L.USER_NAME) BUILD_DATA
WHERE BUILD_DATA.LAST_BUILD_DATE < Now() - interval '12 month';And this query will return all enabled plans that have not been built in over 12 months:
SELECT *
FROM (SELECT B.FULL_KEY, LOWER(L.USER_NAME) AS created_by_username, COALESCE(MAX(BRS.build_completed_date), '1000-01-01 00:00:00') AS LAST_BUILD_DATE
FROM BUILD B JOIN AUDIT_LOG L ON B.FULL_KEY = L.ENTITY_ID and B.suspended_from_building = 'false'
LEFT JOIN buildresultsummary brs ON full_key = build_key AND brs.build_key IS NOT NULL AND b.build_type IN ('CHAIN', 'CHAIN_BRANCH')
GROUP BY B.FULL_KEY, L.USER_NAME) BUILD_DATA
WHERE BUILD_DATA.LAST_BUILD_DATE < Now() - interval '12 month';