How to query Plan Branch 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
You may want to use details about your Plan Branches that are not available from the Bamboo UI, including for example, which Plan and Project the branch belongs to.
Solution
Please run the following queries in your Bamboo database, depending on the database engine that you use.
MySQL
Click here for query...
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
SELECT COALESCE(VL.NAME, VLX.NAME, PARENT.NAME) AS REPO_NAME,
COALESCE(B.FULL_KEY, VLX.PLAN_KEY, PARENT.PLAN_KEY) AS PLAN_KEY,
COALESCE(B.BUILD_TYPE, VLX.BUILD_TYPE, PARENT.BUILD_TYPE) BUILD_TYPE,
COALESCE(B.TITLE, VLX.PLAN_TITLE, PARENT.PLAN_TITLE) TITLE,
COALESCE(VLX.REPOSITORY_TYPE, PARENT.REPOSITORY_TYPE) AS REPO_TYPE,
COALESCE(VLX.XML, PARENT.XML) AS BRANCH
FROM VCS_LOCATION AS VL
LEFT JOIN PLAN_VCS_LOCATION AS PVL ON PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
LEFT JOIN BUILD AS B ON PVL.PLAN_ID = B.BUILD_ID
LEFT JOIN
(SELECT VL.PARENT_ID,
B.FULL_KEY AS PLAN_KEY,
BUILD_TYPE,
B.TITLE AS PLAN_TITLE,
VL.PLUGIN_KEY AS REPOSITORY_TYPE,
VL.VCS_LOCATION_ID,
ExtractValue(VL.XML_DEFINITION_DATA, '//repository/vcsBranch/name') AS XML
FROM VCS_LOCATION as VL
LEFT JOIN PLAN_VCS_LOCATION AS PVL ON PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
LEFT JOIN BUILD AS B ON PVL.PLAN_ID = B.BUILD_ID
LEFT JOIN BUILD_DEFINITION AS BD ON B.BUILD_ID = BD.BUILD_ID) VLX
ON (VLX.VCS_LOCATION_ID = VL.VCS_LOCATION_ID)
LEFT JOIN
(SELECT B.FULL_KEY AS PLAN_KEY,
BUILD_TYPE,
B.TITLE AS PLAN_TITLE,
VL.PLUGIN_KEY AS REPOSITORY_TYPE,
VL.VCS_LOCATION_ID,
ExtractValue(VL.XML_DEFINITION_DATA, '//repository/vcsBranch/name') AS XML
FROM VCS_LOCATION as VL
LEFT JOIN PLAN_VCS_LOCATION AS PVL ON PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
LEFT JOIN BUILD AS B ON PVL.PLAN_ID = B.BUILD_ID) AS PARENT ON (VL.PARENT_ID = PARENT.VCS_LOCATION_ID)
WHERE COALESCE(B.FULL_KEY, VLX.PLAN_KEY, PARENT.PLAN_KEY) is not NULL ;PostgreSQL
Click here for query...
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
SELECT COALESCE(VL.NAME, VLX.NAME, PARENT.NAME) AS REPO_NAME,
COALESCE(B.FULL_KEY, VLX.PLAN_KEY, PARENT.PLAN_KEY) AS PLAN_KEY,
COALESCE(B.BUILD_TYPE, VLX.BUILD_TYPE, PARENT.BUILD_TYPE) BUILD_TYPE,
COALESCE(B.TITLE, VLX.PLAN_TITLE, PARENT.PLAN_TITLE) TITLE,
TRIM(LEADING ':' FROM SUBSTRING(COALESCE(VL.PLUGIN_KEY, VLX.REPOSITORY_TYPE, PARENT.REPOSITORY_TYPE) from ':.*$')) REPOSITORY_TYPE,
COALESCE(VLX.XML, PARENT.XML) AS BRANCH
FROM VCS_LOCATION AS VL
LEFT JOIN PLAN_VCS_LOCATION AS PVL ON PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
LEFT JOIN BUILD AS B ON PVL.PLAN_ID = B.BUILD_ID
LEFT JOIN
(SELECT B.FULL_KEY AS PLAN_KEY,
BUILD_TYPE,
B.TITLE AS PLAN_TITLE,
VL.PLUGIN_KEY AS REPOSITORY_TYPE,
VL.VCS_LOCATION_ID,
unnest(xpath('//vcsBranch[@class="com.atlassian.bamboo.plan.branch.VcsBranchImpl"]/name/text()',CAST(VL.XML_DEFINITION_DATA as XML)))::text AS XML, VL.NAME
FROM VCS_LOCATION VL
LEFT JOIN PLAN_VCS_LOCATION AS PVL ON PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
LEFT JOIN BUILD AS B ON PVL.PLAN_ID = B.BUILD_ID) VLX
ON (VLX.VCS_LOCATION_ID = VL.VCS_LOCATION_ID)
LEFT JOIN
(SELECT B.FULL_KEY AS PLAN_KEY,
BUILD_TYPE,
B.TITLE AS PLAN_TITLE,
VL.PLUGIN_KEY AS REPOSITORY_TYPE,
VL.VCS_LOCATION_ID,
unnest(xpath('//vcsBranch[@class="com.atlassian.bamboo.plan.branch.VcsBranchImpl"]/name/text()',CAST(VL.XML_DEFINITION_DATA as XML)))::text AS XML, VL.NAME
FROM VCS_LOCATION VL
LEFT JOIN PLAN_VCS_LOCATION AS PVL ON PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
LEFT JOIN BUILD AS B ON PVL.PLAN_ID = B.BUILD_ID) AS
PARENT ON (VL.PARENT_ID = PARENT.VCS_LOCATION_ID)
WHERE COALESCE(B.FULL_KEY, VLX.PLAN_KEY, PARENT.PLAN_KEY) is not NULL ;MS SQL Server
Click here for query...
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
SET QUOTED_IDENTIFIER ON
SELECT COALESCE(VL.NAME, VLX.NAME, PARENT.NAME) AS REPO_NAME,
COALESCE(B.FULL_KEY, VLX.PLAN_KEY, PARENT.PLAN_KEY) AS PLAN_KEY,
COALESCE(B.BUILD_TYPE, VLX.BUILD_TYPE, PARENT.BUILD_TYPE) BUILD_TYPE,
COALESCE(B.TITLE, VLX.PLAN_TITLE, PARENT.PLAN_TITLE) TITLE,
COALESCE(VLX.REPOSITORY_TYPE, PARENT.REPOSITORY_TYPE) AS REPO_TYPE,
COALESCE(VLX.XML, PARENT.XML) AS BRANCH
FROM VCS_LOCATION AS VL
LEFT JOIN PLAN_VCS_LOCATION AS PVL ON PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
LEFT JOIN BUILD AS B ON PVL.PLAN_ID = B.BUILD_ID
LEFT JOIN
(select B.FULL_KEY as PLAN_KEY,
BUILD_TYPE,
B.TITLE as PLAN_TITLE,
VL.PLUGIN_KEY as REPOSITORY_TYPE,
VL.VCS_LOCATION_ID,
CAST(
REPLACE(CAST(XML_DEFINITION_DATA AS VARCHAR(MAX)), 'encoding="UTF-8"', 'encoding="utf-8"')
AS XML).value('(//vcsBranch[@class="com.atlassian.bamboo.plan.branch.VcsBranchImpl"]/name)[1]', 'NVARCHAR(max)') as XML,
VL.NAME
from VCS_LOCATION VL
left join PLAN_VCS_LOCATION as PVL on PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
left join BUILD as B on PVL.PLAN_ID = B.BUILD_ID) VLX
ON (VLX.VCS_LOCATION_ID = VL.VCS_LOCATION_ID)
LEFT JOIN
(select B.FULL_KEY as PLAN_KEY,
BUILD_TYPE,
B.TITLE as PLAN_TITLE,
VL.PLUGIN_KEY as REPOSITORY_TYPE,
VL.VCS_LOCATION_ID,
CAST(
REPLACE(CAST(XML_DEFINITION_DATA AS VARCHAR(MAX)), 'encoding="UTF-8"', 'encoding="utf-8"')
AS XML).value('(//vcsBranch[@class="com.atlassian.bamboo.plan.branch.VcsBranchImpl"]/name)[1]', 'NVARCHAR(max)') as XML,
VL.NAME
from VCS_LOCATION VL
left join PLAN_VCS_LOCATION as PVL on PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
left join BUILD as B on PVL.PLAN_ID = B.BUILD_ID)
PARENT ON (VL.PARENT_ID = PARENT.VCS_LOCATION_ID)
WHERE COALESCE(B.FULL_KEY, VLX.PLAN_KEY, PARENT.PLAN_KEY) is not NULL ;Oracle
Click here for query...
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
SELECT COALESCE(VL.NAME, VLX.NAME, PARENT.NAME) AS REPO_NAME,
COALESCE(B.FULL_KEY, VLX.PLAN_KEY, PARENT.PLAN_KEY) AS PLAN_KEY,
COALESCE(B.BUILD_TYPE, VLX.BUILD_TYPE, PARENT.BUILD_TYPE) BUILD_TYPE,
COALESCE(B.TITLE, VLX.PLAN_TITLE, PARENT.PLAN_TITLE) TITLE,
COALESCE(VLX.REPOSITORY_TYPE, PARENT.REPOSITORY_TYPE) AS REPO_TYPE,
COALESCE(VLX.XML, PARENT.XML) AS BRANCH
FROM VCS_LOCATION AS VL
LEFT JOIN PLAN_VCS_LOCATION AS PVL ON PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
LEFT JOIN BUILD AS B ON PVL.PLAN_ID = B.BUILD_ID
LEFT JOIN
(select B.FULL_KEY PLAN_KEY,
B.TITLE TITLE,
VL.PLUGIN_KEY REPOSITORY_TYPE,
XMLTYPE(XML_DEFINITION_DATA).EXTRACT('//vcsBranch[@class="com.atlassian.bamboo.plan.branch.VcsBranchImpl"]/name/text()').getStringVal() BRANCH,
from VCS_LOCATION
left join PLAN_VCS_LOCATION PVL on PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
left join BUILD B on PVL.PLAN_ID = B.BUILD_ID) VLX
ON (VLX.VCS_LOCATION_ID = VL.VCS_LOCATION_ID)
LEFT JOIN
(select B.FULL_KEY PLAN_KEY,
B.TITLE TITLE,
VL.PLUGIN_KEY REPOSITORY_TYPE,
XMLTYPE(XML_DEFINITION_DATA).EXTRACT('//vcsBranch[@class="com.atlassian.bamboo.plan.branch.VcsBranchImpl"]/name/text()').getStringVal() BRANCH,
from VCS_LOCATION
left join PLAN_VCS_LOCATION PVL on PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
left join BUILD B on PVL.PLAN_ID = B.BUILD_ID) PARENT
ON (VL.PARENT_ID = PARENT.VCS_LOCATION_ID)
WHERE COALESCE(B.FULL_KEY, VLX.PLAN_KEY, PARENT.PLAN_KEY) is not NULL ;Updated on April 8, 2025
Was this helpful?
Still need help?
The Atlassian Community is here for you.