How to pull the list of Bamboo plans along with list of Bitbucket repositories linked

Still need help?

The Atlassian Community is here for you.

Ask the community


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%'






Last modified on Sep 21, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.