How to list all Bamboo plans using repository from particular server
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
Listing all build plans that are consuming repositories hosted on a specific repository server might be necessary in case of migration of that repository server to a different hostname or just for reporting purposes.
Environment
Valid for any Bamboo version.
Solution
The below SQL query will generate a list of all plans that have specific patterns in their configuration. If the repository server hostname is used as a pattern the query will list all plans that have repositories containing that hostname:
SELECT BUILD.FULL_KEY
FROM BUILD
WHERE BUILD.BUILD_ID IN (
SELECT PLAN_VCS_LOCATION.PLAN_ID
FROM PLAN_VCS_LOCATION
WHERE PLAN_VCS_LOCATION.VCS_LOCATION_ID IN
(
SELECT VCS_LOCATION.VCS_LOCATION_ID
FROM VCS_LOCATION
WHERE IS_GLOBAL = FALSE
AND VCS_LOCATION.XML_DEFINITION_DATA LIKE '%repo-server-url%'
)
) SELECT BUILD.FULL_KEY
FROM BUILD
WHERE BUILD.BUILD_ID IN (
SELECT PLAN_VCS_LOCATION.PLAN_ID
FROM PLAN_VCS_LOCATION
WHERE PLAN_VCS_LOCATION.VCS_LOCATION_ID IN
(
SELECT VCS_LOCATION.VCS_LOCATION_ID
FROM VCS_LOCATION
WHERE IS_GLOBAL = 0
AND VCS_LOCATION.XML_DEFINITION_DATA LIKE '%repo-server-url%'
)
) In the above query please substitute repo-server-url with the URL of your repository server (example: '%http://bitbucket.acme.com/%' )