How to query the database for build plans that have been run within X days

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.

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

    

Purpose

For auditing purposes, we may want to find builds that have run for a given period of time. The solution below will present some queries to retrieve this data from the database.

Environment

Bamboo 6.10 - 7.X

Solutions

Builds which have been run for a given period.  In the queries that follow, please replace <period> with values of the form number unit, for example: 1 day.

POSTGRES:

SELECT    b.full_key, 
          Max(brs.build_completed_date) AS last_build 
FROM      build b 
LEFT JOIN buildresultsummary brs 
ON        ( 
                    full_key = build_key) 
WHERE     brs.build_key IS NOT NULL 
AND       b.build_type IN ('CHAIN', 
                           'CHAIN_BRANCH') 
AND       brs.build_completed_date >= Now() - interval '<period>' 
GROUP BY  full_key;

MYSQL

SELECT    b.full_key, 
          Max(brs.build_date) AS last_build 
FROM      build b 
LEFT JOIN buildresultsummary brs 
ON        ( 
                    full_key = build_key) 
WHERE     brs.build_key IS NOT NULL 
AND       b.build_type IN ('CHAIN', 
                           'CHAIN_BRANCH') 
AND       build_date >= Now() - interval <period> 
GROUP BY  full_key;


ORACLE:

SELECT    b.full_key, 
          Max(brs.build_completed_date) AS last_build 
FROM      build b 
LEFT JOIN buildresultsummary brs 
ON        ( 
                    full_key = build_key) 
WHERE     brs.build_key IS NOT NULL 
AND       b.build_type IN ('CHAIN', 
                           'CHAIN_BRANCH') 
AND       brs.build_completed_date >= Trunc(sysdate) - 1 
GROUP BY  full_key;



Last modified on Dec 18, 2020

Was this helpful?

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