How to query Plan Branch configuration parameters from the Bamboo Database
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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
Purpose
You may want to fetch details about your Plan Branches configuration parameters in bulk instead of going through them one by one through the UI.
Environment
All supported versions of Bamboo and all supported versions of Postgresql.
Tested the query in Bamboo 9.2.14 and Postgresql 10.6
Solution
Please run the following queries in your Bamboo database, depending on the database engine that you use.
warning
This query is tested in a Postgresql-type DB. Please make changes if required according to your DB instance type.PostgreSQL
SELECT
P.TITLE AS PROJECT,
B.FULL_KEY,
B.BUILD_ID,
B.BUILDKEY AS BUILDKEY,
B.DESCRIPTION AS BUILD_DESCRIPTION,
CAST(UNNEST(XPATH('/configuration/branches/workflow/text()', XML_DEFINITION_DATA::xml)) AS VARCHAR) AS BRANCH_WORKFLOW,
CAST(UNNEST(XPATH('/configuration/branches/branchRemovalCleanUpEnabled/text()', XML_DEFINITION_DATA::xml)) AS VARCHAR) AS BRANCH_REMOVAL_CLEANUP_ENABLED,
CAST(UNNEST(XPATH('/configuration/branches/inactiveBranchCleanupEnabled/text()', XML_DEFINITION_DATA::xml)) AS VARCHAR) AS INACTIVE_BRANCH_CLEANUP_ENABLED,
CAST(UNNEST(XPATH('/configuration/branches/removalCleanupPeriodInDays/text()', XML_DEFINITION_DATA::xml)) AS VARCHAR) AS REMOVAL_CLEANUP_PERIOD_IN_DAYS,
CAST(UNNEST(XPATH('/configuration/branches/inactivityInDays/text()', XML_DEFINITION_DATA::xml)) AS VARCHAR) AS INACTIVITY_IN_DAYS,
CAST(UNNEST(XPATH('/configuration/branches/defaultNotificationStrategy/text()', XML_DEFINITION_DATA::xml)) AS VARCHAR) AS DEFAULT_NOTIFICATION_STRATEGY,
CAST(UNNEST(XPATH('/configuration/branches/issueLinking/text()', XML_DEFINITION_DATA::xml)) AS VARCHAR) AS ISSUE_LINKING,
CAST(UNNEST(XPATH('/configuration/branches/branchTriggering/text()', XML_DEFINITION_DATA::xml)) AS VARCHAR) AS BRANCH_TRIGGERING,
CAST(UNNEST(XPATH('/configuration/branches/defaultBranchIntegration/enabled/text()', XML_DEFINITION_DATA::xml)) AS VARCHAR) AS DEFAULT_BRANCH_INTEGRATION_ENABLED,
CAST(UNNEST(XPATH('/configuration/branches/default/trigger/pluginKey/text()', XML_DEFINITION_DATA::xml)) AS VARCHAR) AS DEFAULT_TRIGGER_PLUGIN_KEY,
CAST(UNNEST(XPATH('/configuration/branches/default/trigger/triggeringRepositories/text()', XML_DEFINITION_DATA::xml)) AS VARCHAR) AS DEFAULT_TRIGGER_TRIGGERING_REPOSITORIES
FROM
BUILD_DEFINITION BD
JOIN BUILD B ON (BD.BUILD_ID = B.BUILD_ID)
JOIN PROJECT P ON (P.PROJECT_ID = B.PROJECT_ID)
ORDER BY 1, 2;