How to obtain a list of linked repositories in Bamboo that are not connected to any plans or deployment projects from the database
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!
The following SQL queries will help you obtain a list of linked repositories (global ones) in Bamboo that are not connected to any plans or deployment projects from the database (no plans or deployment projects listed on the linked repository configuration > usages tab).
Environment
The solution has been validated in Bamboo 9.6 but may be applicable to other versions.
Solution
1. Finding linked repositories that are not associated with any plans and are not referenced by any deployment projects
PostgreSQL
PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT VL.NAME, VL.VCS_LOCATION_ID, CONCAT('<BAMBOO_BASE_URL>/admin/editLinkedRepository.action?repositoryId=', VL.VCS_LOCATION_ID) AS EDIT_URL
FROM VCS_LOCATION VL
WHERE VL.VCS_LOCATION_ID NOT IN
(
SELECT VL.PARENT_ID
FROM VCS_LOCATION VL
JOIN PLAN_VCS_LOCATION PVL
ON VL.VCS_LOCATION_ID = PVL.VCS_LOCATION_ID
WHERE VL.PARENT_ID IS NOT NULL
)
AND VL.VCS_LOCATION_ID NOT IN
(
SELECT DEVL.VCS_LOCATION_ID
FROM DEPLOYMENT_ENV_VCS_LOCATION DEVL
)
AND VL.IS_GLOBAL = true
AND VL.MARKED_FOR_DELETION = false
AND VL.PARENT_ID IS NULL;Microsoft SQL Server
Microsoft SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT VL.NAME,
VL.VCS_LOCATION_ID,
CONCAT('<BAMBOO_BASE_URL>/admin/editLinkedRepository.action?repositoryId=', VL.VCS_LOCATION_ID) AS EDIT_URL
FROM VCS_LOCATION VL
WHERE VL.VCS_LOCATION_ID NOT IN
(
SELECT VL.PARENT_ID
FROM VCS_LOCATION VL
JOIN PLAN_VCS_LOCATION PVL
ON VL.VCS_LOCATION_ID = PVL.VCS_LOCATION_ID
WHERE VL.PARENT_ID IS NOT NULL
)
AND VL.VCS_LOCATION_ID NOT IN
(
SELECT DEVL.VCS_LOCATION_ID
FROM DEPLOYMENT_ENV_VCS_LOCATION DEVL
)
AND VL.IS_GLOBAL = 1
AND VL.MARKED_FOR_DELETION = 0
AND VL.PARENT_ID IS NULL;We have broken down the SQL query above to provide a list of linked repositories that are not connected to any plans or deployment projects individually.
1.1 Finding linked repositories that are not associated with any plans
PostgreSQL
PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT VL.NAME, VL.VCS_LOCATION_ID, CONCAT('<BAMBOO_BASE_URL>/admin/editLinkedRepository.action?repositoryId=', VL.VCS_LOCATION_ID) AS EDIT_URL
FROM VCS_LOCATION VL
WHERE VL.VCS_LOCATION_ID NOT IN
(
SELECT VL.PARENT_ID
FROM VCS_LOCATION VL
JOIN PLAN_VCS_LOCATION PVL
ON VL.VCS_LOCATION_ID = PVL.VCS_LOCATION_ID
WHERE VL.PARENT_ID IS NOT NULL
)
AND VL.IS_GLOBAL = true
AND VL.MARKED_FOR_DELETION = false
AND VL.PARENT_ID IS NULL;Microsoft SQL Server
Microsoft SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT VL.NAME,
VL.VCS_LOCATION_ID,
CONCAT('<BAMBOO_BASE_URL>/admin/editLinkedRepository.action?repositoryId=', VL.VCS_LOCATION_ID) AS EDIT_URL
FROM VCS_LOCATION VL
WHERE VL.VCS_LOCATION_ID NOT IN
(
SELECT VL.PARENT_ID
FROM VCS_LOCATION VL
JOIN PLAN_VCS_LOCATION PVL
ON VL.VCS_LOCATION_ID = PVL.VCS_LOCATION_ID
WHERE VL.PARENT_ID IS NOT NULL
)
AND VL.IS_GLOBAL = 1
AND VL.MARKED_FOR_DELETION = 0
AND VL.PARENT_ID IS NULL;1.2 Finding linked repositories that are not referenced by any deployment projects
PostgreSQL
PostgreSQL
1
2
3
4
5
6
7
8
9
10
SELECT VL.NAME, VL.VCS_LOCATION_ID, CONCAT('<BAMBOO_BASE_URL>/admin/editLinkedRepository.action?repositoryId=', VL.VCS_LOCATION_ID) AS EDIT_URL
FROM VCS_LOCATION VL
WHERE VL.VCS_LOCATION_ID NOT IN
(
SELECT DEVL.VCS_LOCATION_ID
FROM DEPLOYMENT_ENV_VCS_LOCATION DEVL
)
AND VL.IS_GLOBAL = true
AND VL.MARKED_FOR_DELETION = false
AND VL.PARENT_ID IS NULL;Microsoft SQL Server
Microsoft SQL Server
1
2
3
4
5
6
7
8
9
10
11
12
SELECT VL.NAME,
VL.VCS_LOCATION_ID,
CONCAT('<BAMBOO_BASE_URL>/admin/editLinkedRepository.action?repositoryId=', VL.VCS_LOCATION_ID) AS EDIT_URL
FROM VCS_LOCATION VL
WHERE VL.VCS_LOCATION_ID NOT IN
(
SELECT DEVL.VCS_LOCATION_ID
FROM DEPLOYMENT_ENV_VCS_LOCATION DEVL
)
AND VL.IS_GLOBAL = 1
AND VL.MARKED_FOR_DELETION = 0
AND VL.PARENT_ID IS NULL;By substituting <BAMBOO_BASE_URL> with your Bamboo's URL, the third column of the outcome (EDIT_URL) will provide a direct link to the webpage for editing the linked repository. When exporting it to Excel, you can utilize the formula below to transform the URL into a hyperlink. For example:
1
=HYPERLINK(C2;C2)If you're using a different Database Management System (DBMS), you will have to convert the query mentioned above into the syntax specific to your DBMS.
Was this helpful?