Extract Repository details from the Bamboo database

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

Summary

This article will provide you with database queries that show Global Linked repository, Project-level and Plan-level repository details in Bamboo.

Building SQL statements for reporting purposes is not part of the Atlassian Support scope and this work is provided "as-is", on a best-effort basis. Queries may work better or worse than expected, your mileage may vary. It is up to each customer to analyse each query individually and understand if that is enough for their specific needs.

The SQL statements below may not work due to a bug related to XML processing in Bamboo: BAM-22073 - Getting issue details... STATUS . If you encounter XML parsing errors while executing the queries, please work on replacing the XPATH/EXTRACTVALUE with simple functions such as LIKE. You may also use stored procedures to accomplish the same thing.

Environment

All Bamboo releases

Database queries

We have provided queries for PostgreSQL and MySQL. You may have to adapt the queries to adhere to other Database products.

The resulting data will contain:

  • Repository ID
  • Repository usage (build/deploy)
  • Plan key
  • Plan description
  • Deployment project
  • Deployment environment
  • Repository name
  • Repository branch
  • Repository level (Linked/Global, Plan, Project)
  • Repository URL
  • Specs enabled (true/false)
  • Repository Polling (seconds)
  • Repository Schedule (cron format)
  • Suspended from building (true/false)
  • Last run date
  • Plan/Environment author

The query will return information for Git (including Bitbucket Server, Bitbucket Cloud, GitHub and plain Git), SVN, CVS, and Helix (p4) repository types.

List Global Linked RepositoriesProject-level repositories and Plan repositories:

The SQL statements below assume the SQL client has AUTOCOMMIT enabled. Add BEGIN/START/END TRANSACTION statements if required.

PostgreSQL

CREATE OR REPLACE FUNCTION GET_REPO_URL(k1 TEXT, k2 TEXT)
RETURNS TEXT
LANGUAGE PLPGSQL
AS $$
DECLARE URL TEXT;
BEGIN
  SELECT
    CASE
      WHEN VCS.PLUGIN_KEY LIKE '%gitv2' THEN CAST((XPATH('//serverConfiguration/entry/string[text()="repository.git.repositoryUrl"]/../string[2]/text()', CAST(k1 AS XML)))[1] AS TEXT)
      WHEN VCS.PLUGIN_KEY LIKE '%bbserver' THEN CAST((XPATH('//serverConfiguration/entry/string[text()="repository.stash.repositoryUrl"]/../string[2]/text()', CAST(k1 AS XML)))[1] AS TEXT)
      WHEN VCS.PLUGIN_KEY LIKE '%bbCloud' THEN CONCAT(
                                                 'https://bitbucket.org/',
                                                 (XPATH('//serverConfiguration/entry/string[text()="repository.bitbucket.repository"]/../string[2]/text()', CAST(k1 AS XML)))[1])
      WHEN VCS.PLUGIN_KEY LIKE '%gh2' THEN CONCAT(
                                                  CAST((XPATH('//serverConfiguration/entry/string[text()="repository.github.baseUrl"]/../string[2]/text()', CAST(k1 AS XML)))[1] AS TEXT),
                                                  CAST((XPATH('//serverConfiguration/entry/string[text()="repository.github.repository"]/../string[2]/text()', CAST(k1 AS XML)))[1] AS TEXT))
      WHEN VCS.PLUGIN_KEY LIKE '%svnv2' THEN CAST((XPATH('//serverConfiguration/entry/string[text()="repository.svn.repositoryRoot"]/../string[2]/text()', CAST(k1 AS XML)))[1] AS TEXT)
      WHEN VCS.PLUGIN_KEY LIKE '%cvs' THEN CAST(
                                               (XPATH('//repository/cvs/cvsRoot/text()',
                                                      CAST(REGEXP_REPLACE(
                                                           REPLACE(REPLACE(CAST((XPATH('//legacyXml', CAST(k1 AS XML)))[1] AS TEXT),'&lt;', '<'),'&gt;', '>'),'((^<)|(<\/))legacyXml>','','g')
                                                AS XML)))[1] AS TEXT)
      WHEN VCS.PLUGIN_KEY LIKE '%p4' THEN CAST(
                                               (XPATH('//repository/p4/depot/text()',
                                                      CAST(REGEXP_REPLACE(
                                                           REPLACE(REPLACE(CAST((XPATH('//legacyXml', CAST(k1 AS XML)))[1] AS TEXT),'&lt;', '<'),'&gt;', '>'),'((^<)|(<\/))legacyXml>','','g')
                                                AS XML)))[1] AS TEXT)
	END AS "Repository url"
  INTO URL
  FROM TMP_VCS_LOCATION VCS
  WHERE VCS.PLUGIN_KEY = k2;
  RETURN URL;
END;
$$;

-- Add a TEMP table with extra 'PROJECT_ID' column to make it work with Bamboo 7
DROP TABLE IF EXISTS TMP_VCS_LOCATION;
CREATE TEMPORARY TABLE TMP_VCS_LOCATION ON COMMIT DROP AS SELECT * FROM VCS_LOCATION;
ALTER TABLE TMP_VCS_LOCATION ADD COLUMN IF NOT EXISTS PROJECT_ID BIGINT;

-- Create a temporary table for BUILD_DEFINITION to fix broken XML on XML_DEFINITION_DATA
DROP TABLE IF EXISTS TMP_BUILD_DEFINITION;
CREATE TEMPORARY TABLE TMP_BUILD_DEFINITION ON COMMIT DROP AS SELECT * FROM BUILD_DEFINITION;
UPDATE TMP_BUILD_DEFINITION SET XML_DEFINITION_DATA = REGEXP_REPLACE(XML_DEFINITION_DATA,
  ':(enabledForShared|enabledForNonShared|S3ArtifactHandler|ServerLocalArtifactHandler|BambooRemoteArtifactHandler|AgentLocalArtifactHandler|SftpArtifactHandler)', 
    '', 'g');
  
SELECT *
FROM   (  SELECT DISTINCT VCS.VCS_LOCATION_ID AS "Repository ID",
                 'build' AS "Repository usage",
                 PLN.FULL_KEY AS "Plan key",
                 PLN.DESCRIPTION AS "Plan description",
				 NULL AS "Deploy proj",
                 NULL AS "Deploy env",
                 NULL::bigint AS "Deploy ID",
                 VCS.NAME AS "Repository name",
                 CAST((XPATH('//vcsBranch/name/text()', CAST(VCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT) AS "Repository branch",
                 SPLIT_PART(VCS.PLUGIN_KEY, ':', 2) AS "Repository type",
                 'Plan' AS "Repository level",
                 GET_REPO_URL(VCS.XML_DEFINITION_DATA, VCS.PLUGIN_KEY) AS "Repository url",
                 CASE
                   WHEN CAST(CAST((XPATH('//bambooSpecsDetectionConfiguration/entry/string[text()="bamboo.specs.enabled"]/../string[2]/text()', CAST(VCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT) AS BOOLEAN)
                        IS TRUE THEN TRUE
                   ELSE FALSE
                 END AS "Specs enabled",
                 VCS.MARKED_FOR_DELETION AS "Marked for deletion",
                 CASE
                   WHEN POSITION(CAST(VCS.VCS_LOCATION_ID AS TEXT) IN CAST((XPATH('//triggerDefinition/triggeringRepositories/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT)) > 0 THEN
                     CASE
                       WHEN CAST((XPATH('//triggerDefinition/config/item[key="repository.change.poll.type"]/value/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT) = 'PERIOD' THEN
                         CAST((XPATH('//triggerDefinition/config/item[key="repository.change.poll.pollingPeriod"]/value/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT)
                       ELSE NULL
                     END
                   ELSE NULL
                 END AS "Polling period",
                 CASE
                   WHEN POSITION(CAST(VCS.VCS_LOCATION_ID AS TEXT) IN CAST((XPATH('//triggerDefinition/triggeringRepositories/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT)) > 0 THEN
                     CASE
                       WHEN CAST((XPATH('//triggerDefinition/config/item[key="repository.change.poll.type"]/value/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT) = 'CRON' THEN
                         CAST((XPATH('//triggerDefinition/config/item[key="repository.change.poll.cronExpression"]/value/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT)
                       ELSE NULL
                     END
                   ELSE NULL
                 END AS "Polling Schedule",
				 PLN.SUSPENDED_FROM_BUILDING AS "Suspended from building",
                 (SELECT MAX(BRS.BUILD_COMPLETED_DATE) 
				  FROM BUILDRESULTSUMMARY BRS 
				  WHERE BRS.BUILD_KEY = PLN.FULL_KEY) AS "Last run date",
				 LOWER(AUDL.USER_NAME) AS "Plan/Env created by"
          FROM   TMP_VCS_LOCATION VCS
     LEFT JOIN   BUILD PLN
     LEFT JOIN   PLAN_VCS_LOCATION PVL
          ON     PLN.BUILD_ID = PVL.PLAN_ID
          ON     PVL.VCS_LOCATION_ID = VCS.VCS_LOCATION_ID
     LEFT JOIN   TMP_BUILD_DEFINITION BD
          ON     BD.BUILD_ID = PLN.BUILD_ID
     LEFT JOIN   AUDIT_LOG AUDL
	      ON     AUDL.ENTITY_ID = PLN.FULL_KEY AND AUDL.MSG = 'Plan has been created.'
          WHERE  PLN.BUILD_TYPE IS NOT NULL
          AND    VCS.PARENT_ID IS NULL
        UNION ALL
          SELECT DISTINCT
                 CASE
                  WHEN VCS.PARENT_ID IS NULL THEN VCS.VCS_LOCATION_ID
                  ELSE VCS.PARENT_ID
                 END AS "Repository ID",
                 CASE
                  WHEN PLN.FULL_KEY IS NOT NULL THEN 'build'
                  ELSE 'unused'
                 END AS "Repository usage",
                 PLN.FULL_KEY AS "Plan key",
                 PLN.DESCRIPTION AS "Plan description",
				 NULL AS "Deploy proj",
                 NULL AS "Deploy env",
                 NULL::bigint AS "Deploy ID",
                 VCS.NAME AS "Repository name",
                 CASE
                   WHEN VCS.PLUGIN_KEY LIKE '%svnv2' THEN
                     COALESCE (
                       NULLIF(CAST((XPATH('//vcsBranch/displayName/text()', CAST(CVCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT),''),
                       NULLIF(CAST((XPATH('//vcsBranch/displayName/text()', CAST(BVCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT),''),
                       NULLIF(CAST((XPATH('//vcsBranch/displayName/text()', CAST(VCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT),'')
                     )   
                   ELSE
                     COALESCE (
                       NULLIF(CAST((XPATH('//vcsBranch/name/text()', CAST(CVCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT),''),
                       NULLIF(CAST((XPATH('//vcsBranch/name/text()', CAST(BVCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT),''),
                       NULLIF(CAST((XPATH('//vcsBranch/name/text()', CAST(VCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT),'')
                 ) END AS "Repository branch",
                 SPLIT_PART(VCS.PLUGIN_KEY, ':', 2) AS "Repository type",
                 CASE
                   WHEN VCS.IS_GLOBAL IS TRUE THEN 'Linked/Global'
                   WHEN VCS.IS_GLOBAL IS FALSE AND VCS.PARENT_ID IS NULL AND PLN.BUILD_TYPE = 'CHAIN_BRANCH' THEN 'Plan'
                   WHEN VCS.IS_GLOBAL IS FALSE AND BVCS.PROJECT_ID IS NOT NULL THEN 'Project'
                   WHEN VCS.IS_GLOBAL IS FALSE AND VCS.PROJECT_ID IS NOT NULL THEN 'Project'
                   WHEN VCS.IS_GLOBAL IS FALSE AND PLN.MASTER_ID IS NOT NULL AND BVCS.PROJECT_ID IS NULL THEN 'Linked/Global'
                 END AS "Repository level",
                 CASE
                   WHEN VCS.PARENT_ID IS NULL THEN
                   GET_REPO_URL(VCS.XML_DEFINITION_DATA, VCS.PLUGIN_KEY)
                 ELSE
                   GET_REPO_URL(BVCS.XML_DEFINITION_DATA, VCS.PLUGIN_KEY)                  
                 END AS "Repository url",
                 CASE
                   WHEN CAST(COALESCE (
                          CAST((XPATH('//bambooSpecsDetectionConfiguration/entry/string[text()="bamboo.specs.enabled"]/../string[2]/text()', CAST(BVCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT),
                          CAST((XPATH('//bambooSpecsDetectionConfiguration/entry/string[text()="bamboo.specs.enabled"]/../string[2]/text()', CAST(VCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT)
                        ) AS BOOLEAN) IS TRUE THEN TRUE
                   ELSE FALSE
                 END AS "Specs enabled",
                 VCS.MARKED_FOR_DELETION AS "Marked for deletion",
                 CASE
                   WHEN POSITION(CAST(CVCS.VCS_LOCATION_ID AS TEXT) IN CAST((XPATH('//triggerDefinition/triggeringRepositories/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT)) > 0 THEN
                     CASE
                       WHEN CAST((XPATH('//triggerDefinition/config/item[key="repository.change.poll.type"]/value/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT) = 'PERIOD' THEN
                         CAST((XPATH('//triggerDefinition/config/item[key="repository.change.poll.pollingPeriod"]/value/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT)
                       ELSE NULL
                     END
                   ELSE NULL
                 END AS "Polling period",
                 CASE
                   WHEN POSITION(CAST(CVCS.VCS_LOCATION_ID AS TEXT) IN CAST((XPATH('//triggerDefinition/triggeringRepositories/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT)) > 0 THEN
                     CASE
                       WHEN CAST((XPATH('//triggerDefinition/config/item[key="repository.change.poll.type"]/value/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT) = 'CRON' THEN
                         CAST((XPATH('//triggerDefinition/config/item[key="repository.change.poll.cronExpression"]/value/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT)
                       ELSE NULL
                     END
                   ELSE NULL
                 END AS "Polling schedule",
				 PLN.SUSPENDED_FROM_BUILDING AS "Suspended from building",
                 (SELECT MAX(BRS.BUILD_COMPLETED_DATE) 
				  FROM BUILDRESULTSUMMARY BRS 
				  WHERE BRS.BUILD_KEY = PLN.FULL_KEY) AS "Last run date",
				 LOWER(AUDL.USER_NAME) AS "Plan/Env created by"
          FROM        TMP_VCS_LOCATION VCS
          LEFT JOIN   BUILD PLN
          LEFT JOIN   PLAN_VCS_LOCATION PVL
          ON          PLN.BUILD_ID = PVL.PLAN_ID
          LEFT JOIN   TMP_VCS_LOCATION CVCS
          ON          PVL.VCS_LOCATION_ID = CVCS.VCS_LOCATION_ID
          ON          CVCS.PARENT_ID = VCS.VCS_LOCATION_ID
          LEFT JOIN   TMP_VCS_LOCATION BVCS
          ON          VCS.PARENT_ID = BVCS.VCS_LOCATION_ID
          LEFT JOIN   DEPLOYMENT_ENV_VCS_LOCATION DEVL
          ON          VCS.VCS_LOCATION_ID = DEVL.VCS_LOCATION_ID
          LEFT JOIN   TMP_BUILD_DEFINITION BD
          ON          BD.BUILD_ID = PLN.BUILD_ID
		  LEFT JOIN   AUDIT_LOG AUDL
	      ON          AUDL.ENTITY_ID = PLN.FULL_KEY AND AUDL.MSG LIKE 'Plan%has been created.'
          WHERE       ( DEVL.VCS_LOCATION_ID IS NULL OR DEVL.VCS_LOCATION_ID = VCS.VCS_LOCATION_ID )
          AND         ( VCS.IS_GLOBAL IS TRUE
					    OR ( VCS.IS_GLOBAL IS FALSE AND PLN.FULL_KEY IS NOT NULL )
                        OR ( VCS.IS_GLOBAL IS FALSE AND PLN.FULL_KEY IS NULL AND VCS.PROJECT_ID IS NOT NULL )
                      )
        UNION ALL
          SELECT DISTINCT VCS.VCS_LOCATION_ID AS "Repository ID",
                 'deploy' AS "Repository usage",
                 NULL AS "Plan key",
                 NULL AS "Plan description",
				 DP.NAME AS "Deploy proj",
                 DE.NAME AS "Deploy env",
                 DE.ENVIRONMENT_ID AS "Deploy ID",
                 VCS.NAME AS "Repository name",
                 CASE
                   WHEN VCS.PLUGIN_KEY LIKE '%svnv2' THEN
                     COALESCE (
                       NULLIF(CAST((XPATH('//vcsBranch/displayName/text()', CAST(CVCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT),''),
                       NULLIF(CAST((XPATH('//vcsBranch/displayName/text()', CAST(VCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT),'')
                     )   
                   ELSE
                     COALESCE (
                       NULLIF(CAST((XPATH('//vcsBranch/name/text()', CAST(CVCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT),''),
                       NULLIF(CAST((XPATH('//vcsBranch/name/text()', CAST(VCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT),'')
                 ) END AS "Repository branch",
                 SPLIT_PART(VCS.PLUGIN_KEY, ':', 2) AS "Repository type",
                 CASE
                   WHEN VCS.IS_GLOBAL IS TRUE THEN 'Linked/Global'
                   WHEN VCS.IS_GLOBAL IS FALSE AND PLN.FULL_KEY IS NOT NULL THEN 'Project'
                 END AS "Repository level",
                 GET_REPO_URL(VCS.XML_DEFINITION_DATA, VCS.PLUGIN_KEY) AS "Repository url",
                 CASE
                   WHEN CAST(CAST((XPATH('//bambooSpecsDetectionConfiguration/entry/string[text()="bamboo.specs.enabled"]/../string[2]/text()', CAST(VCS.XML_DEFINITION_DATA AS XML)))[1] AS TEXT) AS BOOLEAN )
                        IS TRUE THEN TRUE
                   ELSE FALSE
                 END AS "Specs Enabled",
                 VCS.MARKED_FOR_DELETION "Marked for deletion",
				 NULL AS "Polling period",
				 NULL AS "Polling schedule",
				 FALSE AS "Suspended from building",
				 DR.EXECUTED_DATE AS "Last run date",
				 LOWER(AUDL.USER_NAME) AS "Plan/Env created by"
          FROM        TMP_VCS_LOCATION VCS
          LEFT JOIN   BUILD PLN
          LEFT JOIN   PLAN_VCS_LOCATION PVL
          ON          PLN.BUILD_ID = PVL.PLAN_ID
          LEFT JOIN   TMP_VCS_LOCATION CVCS
          ON          PVL.VCS_LOCATION_ID = CVCS.VCS_LOCATION_ID
          ON          CVCS.PARENT_ID = VCS.VCS_LOCATION_ID
          LEFT JOIN   DEPLOYMENT_ENV_VCS_LOCATION DEVL
          ON          VCS.VCS_LOCATION_ID = DEVL.VCS_LOCATION_ID
          LEFT JOIN   DEPLOYMENT_ENVIRONMENT DE
          ON          DEVL.ENVIRONMENT_ID = DE.ENVIRONMENT_ID
		  LEFT JOIN   DEPLOYMENT_RESULT DR 
		  ON          DR.ENVIRONMENT_ID = DE.ENVIRONMENT_ID
		  LEFT JOIN   DEPLOYMENT_PROJECT DP
		  ON          DP.DEPLOYMENT_PROJECT_ID = DE.PACKAGE_DEFINITION_ID
		  LEFT JOIN   AUDIT_LOG AUDL
	      ON          AUDL.CHILD_ID = CAST(DE.ENVIRONMENT_ID AS VARCHAR) AND AUDL.MSG LIKE 'Environment created'
          WHERE       VCS.PARENT_ID IS NULL
          AND         ( VCS.IS_GLOBAL IS TRUE
                        OR ( VCS.IS_GLOBAL IS FALSE AND PLN.FULL_KEY IS NOT NULL )
                      )
          AND         DE.ENVIRONMENT_ID IS NOT NULL
          AND         VCS.VCS_LOCATION_ID = ANY(CAST((XPATH('//taskDefinition/pluginKey[text()="com.atlassian.bamboo.plugins.vcs:task.vcs.checkout"]/../config/item[key[contains(text(),"selectedRepository")]]/value/text()', CAST(DE.XML_DEFINITION_DATA AS XML))) AS TEXT)::INT[])
	      ) AS RESULTS
ORDER BY "Repository url",
         "Plan key",
         CASE
           WHEN "Repository level" = 'Linked/Global' THEN 0
           WHEN "Repository level" = 'Project' THEN 1
           WHEN "Repository level" = 'Plan' THEN 2
           ELSE 3
         END
ASC;

MySQL

The provided queries may need further optimization. Modern functionalities present on MySQL 8 and newer releases are not in use to allow the SQL to work on older releases of the product. On MySQL 8 you may also have to relax the checks for non-deterministic functions. Read the comments for more information.

DROP FUNCTION IF EXISTS GET_REPO_URL;
DROP FUNCTION IF EXISTS CUSTOM_REGEXP_REPLACE;
DROP PROCEDURE IF EXISTS TMP_VCS_LOCATION;
DROP PROCEDURE IF EXISTS TMP_BUILD_DEFINITION;

-- On MySQL 8 you may need to run 'SET GLOBAL log_bin_trust_function_creators = 1;'
-- To relax the checking for non-deterministic functions

DELIMITER $$
CREATE FUNCTION GET_REPO_URL(k1 TEXT, k2 TEXT)
    RETURNS TEXT
    NOT DETERMINISTIC
BEGIN
    DECLARE URL TEXT;
    SELECT DISTINCTROW
        CASE
            WHEN VCS.PLUGIN_KEY LIKE '%gitv2' THEN EXTRACTVALUE(k1,'//serverConfiguration/entry/string[text()="repository.git.repositoryUrl"]/../string[2]/text()')
            WHEN VCS.PLUGIN_KEY LIKE '%bbserver' THEN EXTRACTVALUE(k1,'//serverConfiguration/entry/string[text()="repository.stash.repositoryUrl"]/../string[2]/text()')
            WHEN VCS.PLUGIN_KEY LIKE '%bbCloud' THEN CONCAT(
                    'https://bitbucket.org/',
                    EXTRACTVALUE(k1,'//serverConfiguration/entry/string[text()="repository.bitbucket.repository"]/../string[2]/text()'))
            WHEN VCS.PLUGIN_KEY LIKE '%gh2' THEN CONCAT(
                    EXTRACTVALUE(k1,'//serverConfiguration/entry/string[text()="repository.github.baseUrl"]/../string[2]/text()'),
                    EXTRACTVALUE(k1,'//serverConfiguration/entry/string[text()="repository.github.repository"]/../string[2]/text()'))
            WHEN VCS.PLUGIN_KEY LIKE '%svnv2' THEN EXTRACTVALUE(k1,'//serverConfiguration/entry/string[text()="repository.svn.repositoryRoot"]/../string[2]/text()')
            WHEN VCS.PLUGIN_KEY LIKE '%cvs' THEN EXTRACTVALUE(
                    REPLACE(REPLACE(REPLACE(REPLACE(EXTRACTVALUE(k1,'//legacyXml'),'&lt;', '<'),'&gt;', '>'),'<legacyXml>',''),'<\legacyXml>','')
                ,'//repository/cvs/cvsRoot/text()')
            WHEN VCS.PLUGIN_KEY LIKE '%p4' THEN EXTRACTVALUE(
                    REPLACE(REPLACE(REPLACE(REPLACE(EXTRACTVALUE(k1,'//legacyXml'),'&lt;', '<'),'&gt;', '>'),'<legacyXml>',''),'<\legacyXml>','')
                ,'//repository/p4/depot/text()')
            END
      INTO URL
    FROM VCS_LOCATION VCS
    WHERE VCS.PLUGIN_KEY = k2;
    RETURN URL;
END$$

-- Add a TABLE with extra 'PROJECT_ID' column to make it work with Bamboo 7
-- MySQL 5 fails to use Temporary tables with "Can't reopen table XX"
-- Creating regular table (will be deleted at the end)
-- MySQL lacks some very useful DDLs
DELIMITER $$
CREATE PROCEDURE TMP_VCS_LOCATION()
    DETERMINISTIC
BEGIN
    DROP TABLE IF EXISTS TMP_VCS_LOCATION;

    IF NOT EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                   WHERE TABLE_NAME = 'VCS_LOCATION'
                     AND COLUMN_NAME = 'PROJECT_ID') THEN
        CREATE TABLE TMP_VCS_LOCATION SELECT *, NULL AS PROJECT_ID FROM VCS_LOCATION;
    ELSE
        CREATE TABLE TMP_VCS_LOCATION SELECT * FROM VCS_LOCATION;
    END IF;
END$$
CALL TMP_VCS_LOCATION;

DELIMITER $$
CREATE FUNCTION CUSTOM_REGEXP_REPLACE(original TEXT, pattern TEXT, replacement TEXT)
RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE temp TEXT;
    DECLARE ch TEXT;
    DECLARE i INT;

    SET temp = '';
    SET i = 1;

    IF original REGEXP pattern THEN
        loop_label: LOOP
            IF i>CHAR_LENGTH(original) THEN
                LEAVE loop_label;
            END IF;
            SET ch = SUBSTRING(original, i, 1);
            IF ch REGEXP pattern THEN
                SET temp = CONCAT(temp, replacement);
            ELSE
                SET temp = CONCAT(temp, ch);
            END IF;
            SET i = i + 1;
        END LOOP;
    ELSE
        SET temp = original;
    END IF;

    RETURN temp;
END$$
DELIMITER ;

-- Temporarily disable safe updates to update TMP_BUILD_DEFINITION
SET SESSION SQL_SAFE_UPDATES = 0;

DELIMITER $$
CREATE PROCEDURE TMP_BUILD_DEFINITION()
    DETERMINISTIC
BEGIN
    DROP TABLE IF EXISTS TMP_BUILD_DEFINITION;

    -- Create a new table with the same structure as BUILD_DEFINITION
    CREATE TABLE TMP_BUILD_DEFINITION SELECT * FROM BUILD_DEFINITION;

    -- Update XML_DEFINITION_DATA column
    UPDATE TMP_BUILD_DEFINITION SET XML_DEFINITION_DATA = CUSTOM_REGEXP_REPLACE(XML_DEFINITION_DATA,
      ':(enabledForShared|enabledForNonShared|S3ArtifactHandler|ServerLocalArtifactHandler|BambooRemoteArtifactHandler|AgentLocalArtifactHandler|SftpArtifactHandler)', 
        '');
END$$
DELIMITER ;
CALL TMP_BUILD_DEFINITION;

-- Enables safe updates again
SET SESSION SQL_SAFE_UPDATES = 1;

SELECT *
FROM   ( SELECT DISTINCT VCS.VCS_LOCATION_ID AS "Repository ID",
                         'build' AS "Repository Usage",
                         PLN.FULL_KEY AS "Plan key",
                         PLN.DESCRIPTION AS "Plan description",
                         NULL AS "Deploy proj",
                         NULL AS "Deploy env",
                         NULL AS "Deploy ID",
                         VCS.NAME AS "Repository name",
                         EXTRACTVALUE(VCS.XML_DEFINITION_DATA,'//vcsBranch/name/text()') AS "Repository branch",
                         SUBSTRING_INDEX(VCS.PLUGIN_KEY,':', -1) AS "Repository type",
                         'Plan' AS "Repository level",
                         GET_REPO_URL(VCS.XML_DEFINITION_DATA, VCS.PLUGIN_KEY) AS "Repository url",
                         EXTRACTVALUE(VCS.XML_DEFINITION_DATA,'//bambooSpecsDetectionConfiguration/entry/string[text()="bamboo.specs.enabled"]/../string[2]/text()') AS "Specs Enabled",
                         VCS.MARKED_FOR_DELETION AS "Marked for deletion",
                         CASE
							WHEN LOCATE(CAST(VCS.VCS_LOCATION_ID AS CHAR), EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/triggeringRepositories')) > 0 THEN
								CASE
									WHEN EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/config/item[key="repository.change.poll.type"]/value') = 'PERIOD' THEN
										EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/config/item[key="repository.change.poll.pollingPeriod"]/value')
									ELSE NULL
								END
							ELSE NULL
						END AS "Polling period",
						CASE
							WHEN LOCATE(CAST(VCS.VCS_LOCATION_ID AS CHAR), EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/triggeringRepositories')) > 0 THEN
								CASE
									WHEN EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/config/item[key="repository.change.poll.type"]/value') = 'CRON' THEN
										EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/config/item[key="repository.change.poll.cronExpression"]/value')
									ELSE NULL
								END
							ELSE NULL
						END AS "Polling Schedule",
                        IFNULL(NOT NOT (PLN.SUSPENDED_FROM_BUILDING), FALSE) AS "Suspended from building",
						(SELECT MAX(BRS.BUILD_COMPLETED_DATE) 
						  FROM BUILDRESULTSUMMARY BRS 
						  WHERE BRS.BUILD_KEY = PLN.FULL_KEY) AS "Last run date",
						LOWER(AUDL.USER_NAME) AS "Plan/Env created by"
              FROM   TMP_VCS_LOCATION VCS
         LEFT JOIN   BUILD PLN
		 LEFT JOIN   PLAN_VCS_LOCATION PVL
   		        ON   PLN.BUILD_ID = PVL.PLAN_ID
				ON   PVL.VCS_LOCATION_ID = VCS.VCS_LOCATION_ID
		 LEFT JOIN   TMP_BUILD_DEFINITION BD
				ON   BD.BUILD_ID = PLN.BUILD_ID
		 LEFT JOIN   AUDIT_LOG AUDL
				ON   AUDL.ENTITY_ID = PLN.FULL_KEY AND AUDL.MSG = 'Plan has been created.'
             WHERE   PLN.BUILD_TYPE IS NOT NULL
               AND   VCS.PARENT_ID IS NULL
         UNION ALL
         SELECT DISTINCT
             CASE
                 WHEN VCS.PARENT_ID IS NULL THEN VCS.VCS_LOCATION_ID
                 ELSE VCS.PARENT_ID
                 END AS "Repository ID",
             CASE
                 WHEN PLN.FULL_KEY IS NOT NULL THEN 'build'
                 ELSE 'unused'
                 END AS "Repository Usage",
             PLN.FULL_KEY AS "Plan key",
             PLN.DESCRIPTION AS "Plan description",
             NULL AS "Deploy proj",
             NULL AS "Deploy env",
             NULL AS "Deploy ID",
             VCS.NAME AS "Repository name",
             CASE
                 WHEN VCS.PLUGIN_KEY LIKE '%svnv2' THEN
                     COALESCE (
                             NULLIF(EXTRACTVALUE(BVCS.XML_DEFINITION_DATA,'//vcsBranch/displayName/text()'),''),
                             NULLIF(EXTRACTVALUE(CVCS.XML_DEFINITION_DATA,'//vcsBranch/displayName/text()'),''),
                             NULLIF(EXTRACTVALUE(VCS.XML_DEFINITION_DATA,'//vcsBranch/displayName/text()'),'')
                         )
                 ELSE
                     COALESCE (
                             NULLIF(EXTRACTVALUE(BVCS.XML_DEFINITION_DATA,'//vcsBranch/name/text()'),''),
                             NULLIF(EXTRACTVALUE(CVCS.XML_DEFINITION_DATA,'//vcsBranch/name/text()'),''),
                             NULLIF(EXTRACTVALUE(VCS.XML_DEFINITION_DATA,'//vcsBranch/name/text()'),'')
                         ) END AS "Repository branch",
             SUBSTRING_INDEX(VCS.PLUGIN_KEY,':', -1) AS "Repository type",
             CASE
                 WHEN VCS.IS_GLOBAL IS TRUE THEN 'Linked/Global'
                 WHEN VCS.IS_GLOBAL IS FALSE AND VCS.PARENT_ID IS NULL AND PLN.BUILD_TYPE = 'CHAIN_BRANCH' THEN 'Plan'
                 WHEN VCS.IS_GLOBAL IS FALSE AND BVCS.PROJECT_ID IS NOT NULL THEN 'Project'
                 WHEN VCS.IS_GLOBAL IS FALSE AND VCS.PROJECT_ID IS NOT NULL THEN 'Project'
                 WHEN VCS.IS_GLOBAL IS FALSE AND PLN.MASTER_ID IS NOT NULL AND BVCS.PROJECT_ID IS NULL THEN 'Linked/Global'
                 END AS "Repository level",
             CASE
                 WHEN VCS.PARENT_ID IS NULL THEN
                     GET_REPO_URL(VCS.XML_DEFINITION_DATA, VCS.PLUGIN_KEY)
                 ELSE
                     GET_REPO_URL(BVCS.XML_DEFINITION_DATA, VCS.PLUGIN_KEY)
                 END AS "Repository url",
             COALESCE (
                     EXTRACTVALUE(BVCS.XML_DEFINITION_DATA,'//bambooSpecsDetectionConfiguration/entry/string[text()="bamboo.specs.enabled"]/../string[2]/text()'),
                     EXTRACTVALUE(VCS.XML_DEFINITION_DATA,'//bambooSpecsDetectionConfiguration/entry/string[text()="bamboo.specs.enabled"]/../string[2]/text()')
                 ) AS "Specs Enabled",
             VCS.MARKED_FOR_DELETION AS "Marked for deletion",
			 CASE
				WHEN LOCATE(CAST(CVCS.VCS_LOCATION_ID AS CHAR), EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/triggeringRepositories')) > 0 THEN
					CASE
						WHEN EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/config/item[key="repository.change.poll.type"]/value') = 'PERIOD' THEN
							EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/config/item[key="repository.change.poll.pollingPeriod"]/value')
						ELSE NULL
					END
				ELSE NULL
			 END AS "Polling period",
			 CASE
				WHEN LOCATE(CAST(CVCS.VCS_LOCATION_ID AS CHAR), EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/triggeringRepositories')) > 0 THEN
					CASE
						WHEN EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/config/item[key="repository.change.poll.type"]/value') = 'CRON' THEN
							EXTRACTVALUE(BD.XML_DEFINITION_DATA, '//triggerDefinition/config/item[key="repository.change.poll.cronExpression"]/value')
						ELSE NULL
					END
				ELSE NULL
			 END AS "Polling Schedule",
			 IFNULL(NOT NOT (PLN.SUSPENDED_FROM_BUILDING), FALSE) AS "Suspended from building",
             (SELECT MAX(BRS.BUILD_COMPLETED_DATE) 
						  FROM BUILDRESULTSUMMARY BRS 
						  WHERE BRS.BUILD_KEY = PLN.FULL_KEY) AS "Last run date",
			 LOWER(AUDL.USER_NAME) AS "Plan/Env created by"
              FROM   TMP_VCS_LOCATION VCS
		 LEFT JOIN   BUILD PLN
		 LEFT JOIN   PLAN_VCS_LOCATION PVL
			    ON   PLN.BUILD_ID = PVL.PLAN_ID
		 LEFT JOIN   TMP_VCS_LOCATION CVCS
				ON   PVL.VCS_LOCATION_ID = CVCS.VCS_LOCATION_ID
				ON   CVCS.PARENT_ID = VCS.VCS_LOCATION_ID
		 LEFT JOIN   TMP_VCS_LOCATION BVCS
				ON   VCS.PARENT_ID = BVCS.VCS_LOCATION_ID
		 LEFT JOIN   DEPLOYMENT_ENV_VCS_LOCATION DEVL
				ON   VCS.VCS_LOCATION_ID = DEVL.VCS_LOCATION_ID
         LEFT JOIN   TMP_BUILD_DEFINITION BD
                ON   BD.BUILD_ID = PLN.BUILD_ID
		 LEFT JOIN   AUDIT_LOG AUDL
                ON   AUDL.ENTITY_ID = PLN.FULL_KEY AND AUDL.MSG LIKE 'Plan%has been created.'
             WHERE   ( DEVL.VCS_LOCATION_ID IS NULL OR DEVL.VCS_LOCATION_ID = VCS.VCS_LOCATION_ID )
               AND   ( VCS.IS_GLOBAL IS TRUE
                       OR ( VCS.IS_GLOBAL IS FALSE AND PLN.FULL_KEY IS NOT NULL )
                       OR ( VCS.IS_GLOBAL IS FALSE AND PLN.FULL_KEY IS NULL AND VCS.PROJECT_ID IS NOT NULL )
                     )         
         UNION ALL
         SELECT DISTINCT VCS.VCS_LOCATION_ID AS "Repository ID",
                         'deploy' AS "Repository Usage",
                         NULL AS "Plan key",
                         NULL AS "Plan description",
                         DP.NAME AS "Deploy proj",
                         DE.NAME AS "Deploy env",
                         DE.ENVIRONMENT_ID AS "Deploy ID",
                         VCS.NAME AS "Repository name",
                         CASE
                             WHEN VCS.PLUGIN_KEY LIKE '%svnv2' THEN
                                 COALESCE (
                                         NULLIF(EXTRACTVALUE(CVCS.XML_DEFINITION_DATA,'//vcsBranch/displayName/text()'),''),
                                         NULLIF(EXTRACTVALUE(VCS.XML_DEFINITION_DATA,'//vcsBranch/displayName/text()'),'')
                                     )
                             ELSE
                                 COALESCE (
                                         NULLIF(EXTRACTVALUE(CVCS.XML_DEFINITION_DATA,'//vcsBranch/name/text()'),''),
                                         NULLIF(EXTRACTVALUE(VCS.XML_DEFINITION_DATA,'//vcsBranch/name/text()'),'')
                                     ) END AS "Repository branch",
                         SUBSTRING_INDEX(VCS.PLUGIN_KEY,':', -1) AS "Repository type",
                         CASE
                             WHEN VCS.IS_GLOBAL IS TRUE THEN 'Linked/Global'
                             WHEN VCS.IS_GLOBAL IS FALSE AND PLN.FULL_KEY IS NOT NULL THEN 'Project'
                             END AS "Repository level",
                         GET_REPO_URL(VCS.XML_DEFINITION_DATA, VCS.PLUGIN_KEY) AS "Repository url",
                         EXTRACTVALUE(VCS.XML_DEFINITION_DATA,'//bambooSpecsDetectionConfiguration/entry/string[text()="bamboo.specs.enabled"]/../string[2]/text()') AS "Specs Enabled",
                         VCS.MARKED_FOR_DELETION AS "Marked for deletion",
                         NULL AS "Polling period",
                         NULL AS "Polling schedule",
                         0 AS "Suspended from building",
                         DR.EXECUTED_DATE AS "Last run date",
                         LOWER(AUDL.USER_NAME) AS "Plan/Env created by"
              FROM   TMP_VCS_LOCATION VCS
		 LEFT JOIN   BUILD PLN
		 LEFT JOIN   PLAN_VCS_LOCATION PVL
				ON   PLN.BUILD_ID = PVL.PLAN_ID
		 LEFT JOIN   TMP_VCS_LOCATION CVCS
			    ON   PVL.VCS_LOCATION_ID = CVCS.VCS_LOCATION_ID
				ON   CVCS.PARENT_ID = VCS.VCS_LOCATION_ID
		 LEFT JOIN   DEPLOYMENT_ENV_VCS_LOCATION DEVL
				ON   VCS.VCS_LOCATION_ID = DEVL.VCS_LOCATION_ID
		 LEFT JOIN   DEPLOYMENT_ENVIRONMENT DE
				ON   DEVL.ENVIRONMENT_ID = DE.ENVIRONMENT_ID
		 LEFT JOIN   DEPLOYMENT_RESULT DR 
				ON   DR.ENVIRONMENT_ID = DE.ENVIRONMENT_ID
		 LEFT JOIN   DEPLOYMENT_PROJECT DP
			    ON   DP.DEPLOYMENT_PROJECT_ID = DE.PACKAGE_DEFINITION_ID
		 LEFT JOIN   AUDIT_LOG AUDL
				ON   AUDL.CHILD_ID = CAST(DE.ENVIRONMENT_ID AS CHAR) AND AUDL.MSG LIKE 'Environment created'	 
             WHERE   VCS.PARENT_ID IS NULL
               AND   ( VCS.IS_GLOBAL IS TRUE
                       OR ( VCS.IS_GLOBAL IS FALSE AND PLN.FULL_KEY IS NOT NULL )
					 )
               AND   DE.ENVIRONMENT_ID IS NOT NULL
               AND   FIND_IN_SET(VCS.VCS_LOCATION_ID,REPLACE(EXTRACTVALUE(DE.XML_DEFINITION_DATA,'//taskDefinition/pluginKey[text()="com.atlassian.bamboo.plugins.vcs:task.vcs.checkout"]/../config/item[key[contains(text(),"selectedRepository")]]/value/text()'),' ',',')) IS TRUE
       ) AS RESULTS
ORDER BY "Repository url",
         "Plan key",
         CASE
             WHEN "Repository level" = 'Linked/Global' THEN 0
             WHEN "Repository level" = 'Project' THEN 1
             WHEN "Repository level" = 'Plan' THEN 2
             ELSE 3
         END
ASC;
Last modified on Jan 31, 2024

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.