How to generate report in Bamboo to get the builds and deployments where a specific global variable was used.
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
Summary
The purpose of this page is to generate a report using DB queries that would display a list of builds and deployment jobs where a specific global variable is being used.
Environment
All supported versions of Bamboo
Solution
Query #1 to get where was defined for a Plan.
Please replace the TestVariable with the name of your variable.
SELECT P.PROJECT_KEY,
P.TITLE,
B.FULL_KEY,
B.BUILD_TYPE,
B.TITLE,
B.CREATED_DATE
FROM BUILD_DEFINITION BD
INNER JOIN BUILD B
ON B.BUILD_ID = BD.BUILD_ID
INNER JOIN PROJECT P
ON P.PROJECT_ID = B.PROJECT_ID
WHERE BD.XML_DEFINITION_DATA LIKE '%TestVariable%'; This is going to list the ProjectKey, ProjectTitle, FullKey, BuildType, BuildTitle, and CreationDate of where the variable was used.
Query #2 to get where was defined for a Deployment.
Please replace the TestVariable with the name of your variable.
SELECT NAME,
DESCRIPTION
FROM DEPLOYMENT_ENVIRONMENT DE
WHERE DE.XML_DEFINITION_DATA LIKE '%TestVariable%'; 
Please note this query has been tested in PostgreSQL DB and might require changes for other DB types.