How to get a list of deployments that contain a specific task or a variable in their environments from the Bamboo database
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 you how to get a list of deployments that contain a specific task or a variable in their environments from the Bamboo database.
Environment
- Bamboo 7.2
- Postgres database
Solution
Use the following SQL to gather all deployments which have a specific task or a variable in their environments from the Bamboo database:
WITH environment AS
(
SELECT name,
package_definition_id,
description,
xml_definition_data::xml AS query_column
FROM deployment_environment
)
SELECT
proj.name AS "Project Name",
env.name AS "Environment Name",
env.description AS "Environment Description",
envdata.taskId AS "Task ID",
envdata.userDescription AS "Task Description",
envdata.isEnabled AS "Task Enabled",
envdata.pluginKey AS "Task Plugin Key"
FROM environment env JOIN deployment_project proj ON env.package_definition_id = proj.deployment_project_id,
XMLTABLE ('/configuration/taskDefinition'
PASSING query_column COLUMNS
taskId text PATH 'id',
userDescription text PATH 'userDescription',
isEnabled text PATH 'isEnabled',
pluginKey text PATH 'pluginKey',
config xml PATH 'config'
) envdata
WHERE envdata.pluginKey LIKE '%com.atlassian.bamboo.plugins.scripttask:task.builder.script%'
OR CAST(envdata.config AS text) LIKE '%${bamboo.%';
Feel free to change the text below in the query according to your needs:
// The text below is the task type:
com.atlassian.bamboo.plugins.scripttask:task.builder.script
// The text below is the variable name:
${bamboo.}