How to get a list of plan and job results from the Bamboo database
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
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
This document will show you how to get a list of plan and job results from the Bamboo database with details such as the job's duration, build status, and many others.
Environment
All supported versions of Bamboo.
Solution
The table that stores information about plan results (including job results) inside Bamboo is called BUILDRESULTSUMMARY. You will find most of the information displayed in the build and job result summary pages in Bamboo inside this database table. The following select query will produce a list with the results of all jobs inside your Bamboo instance:
The select queries below have been tested on PostgreSQL. You may need to make changes to the queries to ensure they work in a different DBMS e.g. Oracle, MySQL, and etc.
Job results
select * from buildresultsummary where build_type = 'BUILD';You will find many details about every job including the job duration, job status, the ID of the agent that built it, number of failed tests, and many others. If you wish to include the plan results alongside the results of its jobs you can use the following select instead:
Job results (incl. plan results)
select * from buildresultsummary brs1 join buildresultsummary brs2 on brs1.chain_result = brs2.buildresultsummary_id;Below we will highlight the differences between two of the columns that you will see in the results named duration and processing_duration:
Duration
- For plan results duration is the total time jobs within all stages of the plan took to complete.
- For job results duration is the total time that a particular job took to complete.
Processing duration
- For plans processing_duration is the total time jobs within a certain stage took to complete + the time they spent in the queue.
- For job results processing_duration does not include the time it spent in the queue.
You can separate plan results and job results using the build_type column inside the BUILDRESULTSUMMARY table. Job results have a build_type BUILD while plan results have a build_type CHAIN.
- The reason why we need this distinction is because builds don't always start building the moment they are triggered. They first need to enter the queue and wait to get assigned to an available agent that's capable of building them and this can take time.
- Values are displayed in milliseconds in the database but seconds in the build and job result summary pages.
- The Duration field in the build result summary corresponds to the duration column inside the
BUILDRESULTSUMMARYtable and not processing_duration. This means duration in the build result summary page does not take into account the time jobs spent in the queue waiting for an agent.