Extract Repository details from the Bamboo database
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 Repositories, Project-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),'<', '<'),'>', '>'),'((^<)|(<\/))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),'<', '<'),'>', '>'),'((^<)|(<\/))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'),'<', '<'),'>', '>'),'<legacyXml>',''),'<\legacyXml>','')
,'//repository/cvs/cvsRoot/text()')
WHEN VCS.PLUGIN_KEY LIKE '%p4' THEN EXTRACTVALUE(
REPLACE(REPLACE(REPLACE(REPLACE(EXTRACTVALUE(k1,'//legacyXml'),'<', '<'),'>', '>'),'<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
Powered by Confluence and Scroll Viewport.