How to obtain a list of linked repositories in Bamboo that are not connected to any plans or deployment projects from the database

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

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

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 
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 
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 
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 
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 
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 
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:

=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.




Last modified on Jul 23, 2024

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.