How to list all Bamboo plans using repository from particular server
Platform Notice: Data Center Only - This article only applies to Atlassian products 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 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!
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:
POSTGRESQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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%'
)
) MYSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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%'
)
) MSSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT BUILD.FULL_KEY
FROM dbo.BUILD
WHERE BUILD.BUILD_ID IN (
SELECT PLAN_VCS_LOCATION.PLAN_ID
FROM dbo.PLAN_VCS_LOCATION
WHERE PLAN_VCS_LOCATION.VCS_LOCATION_ID IN
(
SELECT VCS_LOCATION.VCS_LOCATION_ID
FROM dbo.VCS_LOCATION
WHERE IS_GLOBAL = 0
AND VCS_LOCATION.XML_DEFINITION_DATA LIKE '%repo-server-url%'
)
)In the above query please substitute repo-server-urlwith the URL of your repository server (example: '%http://bitbucket.acme.com/%' )
Or if you are want to be more precise use the repo URI so for https://github.com/atlassian/github_test_repo1.git you would use '%atlassian/github_test_repo1%'
Was this helpful?