Extract Repository details from the Bamboo 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
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. 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.
Solution
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
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;Updated on April 8, 2025
Was this helpful?
Still need help?
The Atlassian Community is here for you.