How to query the database for build plans that have been run within X days
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
Powered by Confluence and Scroll Viewport.