How to query the database for jobs that are no longer active

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.

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');



Last modified on Jan 18, 2021

Was this helpful?

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