How to pull the list of Bamboo plans along with list of Bitbucket repositories linked
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the server and data center platforms.
Summary
The purpose of this page is to provide a DB query which can be used to pull the list of all Bamboo plans with the list of linked repositories
The content on this page relates to platforms which are supported; however, the content is out of scope of our Atlassian Support Offerings. Consequently, Atlassian cannot guarantee support. Please be aware that this material is provided for your information only and you may use it at your own risk.
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.
Environment
The query has been tested for PostgreSQL and MySQL DB's.
Solution
Query for PostgreSQL
select distinct b.build_type,b.full_key,b.title, CAST((XPATH('//serverConfiguration/entry/string[text()="repository.stash.repositoryUrl"]/../string[2]/text()', CAST(vcl2.xml_definition_data AS XML)))[1] AS TEXT) from build b, plan_vcs_location pvl, vcs_location vcl1 , vcs_location vcl2 where b.build_id=pvl.plan_id and pvl.vcs_location_id=vcl1.vcs_location_id and vcl1.parent_id=vcl2.vcs_location_id and vcl2.plugin_key='com.atlassian.bamboo.plugins.stash.atlassian-bamboo-plugin-stash:bbserver' and vcl2.xml_definition_data like '%repository.stash.repositoryUrl%'Query for MySQL
select distinct b.BUILD_TYPE,b.FULL_KEY,b.TITLE, EXTRACTVALUE(vcl2.XML_DEFINITION_DATA,'//serverConfiguration/entry/string[text()="repository.stash.repositoryUrl"]/../string[2]/text()') as URL from BUILD b, PLAN_VCS_LOCATION pvl, VCS_LOCATION vcl1 , VCS_LOCATION vcl2 where b.BUILD_ID=pvl.PLAN_ID and pvl.VCS_LOCATION_ID=vcl1.VCS_LOCATION_ID and vcl1.PARENT_ID=vcl2.VCS_LOCATION_ID and vcl2.PLUGIN_KEY='com.atlassian.bamboo.plugins.stash.atlassian-bamboo-plugin-stash:bbserver' and vcl2.XML_DEFINITION_DATA like '%repository.stash.repositoryUrl%'