How to query the Bamboo Data Center database for build plans that have not been run
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. 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
For auditing purposes, we may want to find builds that have not run for a given period of time. The solution below will present some queries to retrieve this data from the database.
Environment
The solution has been validated in Bamboo Data Center 8.0 but may be applicable to other versions.
Solution
Builds which have not been run at all
select b.FULL_KEY
from BUILD b left join BUILDRESULTSUMMARY brs
on (b.FULL_KEY = brs.BUILD_KEY)
where brs.BUILD_KEY is null
and b.BUILD_TYPE in ('CHAIN', 'CHAIN_BRANCH') ;This query should work for all database types.
Builds which have not been run for a given period
In the queries that follow, please replace <period> with values of the form number unit, for example: 6 months.
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 brs.build_completed_date < Now() - interval '1day'
GROUP BY full_key;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;
Please note the very slight difference between the queries above.
For the following queries, please replace:
<interval>with "day", "week", "month", "year"<num>with the number of intervals<number of days>number of days to check (example:90will search for builds that last ran 90 days ago)
SELECT FULL_KEY, LAST_BUILD
FROM (
SELECT b.FULL_KEY, MAX(brs.BUILD_DATE) AS LAST_BUILD
FROM BUILD b
LEFT JOIN BUILDRESULTSUMMARY brs ON b.FULL_KEY = brs.BUILD_KEY
WHERE b.BUILD_TYPE IN ('CHAIN', 'CHAIN_BRANCH') AND brs.BUILD_KEY IS NOT NULL
GROUP BY b.FULL_KEY) a
WHERE a.LAST_BUILD < DATEADD(<interval>, -<num>, GETDATE());select b.FULL_KEY
, b.BUILD_TYPE
, brs.BUILD_COMPLETED_DATE
from BUILD b
join BUILDRESULTSUMMARY brs on b.FULL_KEY = brs.BUILD_KEY
and b.BUILD_ID in (select b.BUILD_ID
from BUILDRESULTSUMMARY brs2
join BUILD b on brs2.BUILD_KEY = b.FULL_KEY
where b.BUILD_TYPE in ('CHAIN','CHAIN_BRANCH')
and brs2.BUILD_COMPLETED_DATE <= TRUNC(SYSDATE) - <number of days>)
order by b.FULL_KEY;Please note that the Oracle and SQL Server queries have not been tested.