How to query the database for jobs that are no longer active
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
Summary
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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');Was this helpful?