How to get a list of deployments that contain a specific task or a variable in their environments from the Bamboo database
Platform Notice: Data Center Only - This article only applies to Atlassian apps 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
The steps outlined in this article are provided AS-IS. This means we've had reports of them working for some customers, under certain circumstances, yet they 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 contact our Community for matters that are outside Atlassian's scope of support!
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:
PostgreSQL query
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.}Was this helpful?