How to extract list of jobs in Bamboo that have an specific task by using an SQL query
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
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
This article will show how to extract a list of jobs that have a specific task using SQL query.
Environment
Valid for any Bamboo version.
Solution
The following SQL queries were tested on PostgreSQL and may need to be adjusted to work on other database engines (e.g. Oracle, MySQL and etc).
In order to fetch a list of jobs that contain a specific task you must identify the pluginKey of the task first. The pluginKey information is stored in the XML_DEFINITION_DATA column inside the BUILD_DEFINITION table. The XML_DEFINITION_DATA contains all the job configuration.
1. Identify pluginKey of the task
You can run the following query to fetch all jobs and find the pluginKey of the task that you want to search for:
select JOB.full_key, JOB.title, BD.*
from BUILD JOB
join BUILD_DEFINITION BD
on BD.BUILD_ID = JOB.BUILD_ID
where JOB.BUILD_TYPE = 'JOB';Sample Output:
| full_key | title | build_definition_id | build_definition_type | created_date | updated_date | xml_definition_data | build_id |
|---|---|---|---|---|---|---|---|
| TP-PLAN1-JOB1 | Default Job | 851,970 | BUILD | <?xml version="1.0" encoding="UTF-8" standalone="no"?> .... Output omitted ... <pluginKey>com.atlassian.bamboo.plugins.vcs:task.vcs.checkout</pluginKey> ... Output omitted ... <pluginKey>com.atlassian.bamboo.plugins.scripttask:task.builder.script</pluginKey> ... Output omitted ... </configuration> | 819,202 | ||
| TP-PLAN2-JOB1 | Default Job | 851,971 | BUILD | <?xml version="1.0" encoding="UTF-8" standalone="no"?> .... Output omitted ... <pluginKey>com.atlassian.bamboo.plugins.vcs:task.vcs.checkout</pluginKey> ... Output omitted ... <pluginKey>com.atlassian.bamboo.plugins.bamboo-docker-plugin:task.docker.cli</pluginKey> ... Output omitted ... </configuration> | 819,204 |
You can be more specific to fetch all tasks in a specific job:
select JOB.full_key, JOB.title, BD.*
from BUILD JOB
join BUILD_DEFINITION BD
on BD.BUILD_ID = JOB.BUILD_ID
where JOB.BUILD_TYPE = 'JOB'
and JOB.full_key = 'TP-PLAN1-JOB1';2. Fetch list of jobs that contain a certain task
After fetching all the pluginKeys you can filter for jobs that contain the task you want. Here's an example on how to search for jobs that have Docker tasks:
select JOB.full_key, JOB.title, BD.*
from BUILD JOB
join BUILD_DEFINITION BD
on BD.BUILD_ID = JOB.BUILD_ID
where JOB.BUILD_TYPE = 'JOB'
and BD.XML_DEFINITION_DATA like '%<pluginKey>com.atlassian.bamboo.plugins.bamboo-docker-plugin:task.docker.cli</pluginKey>%'Sample output:
| full_key | title | build_definition_id | build_definition_type | created_date | updated_date | xml_definition_data | build_id |
|---|---|---|---|---|---|---|---|
| TP-PLAN2-JOB1 | Default Job | 851,971 | BUILD | <?xml version="1.0" encoding="UTF-8" standalone="no"?> .... Output omitted ... <pluginKey>com.atlassian.bamboo.plugins.vcs:task.vcs.checkout</pluginKey> ... Output omitted ... <pluginKey>com.atlassian.bamboo.plugins.bamboo-docker-plugin:task.docker.cli</pluginKey> ... Output omitted ... </configuration> | 819,204 |