How to query the database for jobs that are no longer active
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.
What is the query to check the last update / executed bamboo job/plan in the last 12 months?
How to get the bamboo author or owner or user who runs that build/job/plan.
Solution
The queries below were done using PostgreSQL, some changes might be needed for other DBMS's.
This first query will retrieve all the plans that ran after 2019-09-30 16:16:00 and was triggered manually or using the rerun option, so these are the ones we have the information of which user triggered the build:
SELECT b.full_key,
b.title,
MAX(brs.build_date) AS last_build_date,
cd.custom_info_value AS triggered_by
FROM build b
JOIN public.buildresultsummary brs ON b.full_key = brs.build_key
JOIN public.buildresultsummary_customdata cd ON brs.buildresultsummary_id = cd.buildresultsummary_id
WHERE brs.build_date > '2019-09-30 16:16:00'
AND b.build_type like '%CHAIN%'
AND cd.custom_info_key in ('ManualBuildTriggerReason.userName',
'trigger.created.by.user')
GROUP BY b.full_key,
b.title,
cd.custom_info_value;This second query will retrieve all the plans that ran after 2019-09-30 16:16:00, and was triggered by other reason then manual build or rerun, so we cannot link a person to the trigger:
SELECT R.*,
brs1.trigger_reason
FROM
(SELECT b.full_key,
b.title,
MAX(BRS.build_date) AS build_date
FROM build b
JOIN buildresultsummary brs ON b.full_key = brs.build_key
WHERE brs.build_date > '2019-09-30 16:16:00'
AND b.build_type like '%CHAIN%'
GROUP BY 1,
2) R
INNER JOIN buildresultsummary brs1 ON brs1.build_date = r.build_date
WHERE brs1.trigger_reason not in ('com.atlassian.bamboo.plugin.system.triggerReason:ManualBuildTriggerReason',
'com.atlassian.bamboo.plugin.system.triggerReason:RerunBuildTriggerReason');