Find repository type and build strategy used by each plan

Still need help?

The Atlassian Community is here for you.

Ask the community

Scenario

To know which repository type and build strategy is being used in each plan, run this SQL query:

SELECT p.title AS project, b.title AS plan,
(CASE WHEN LOWER(vl.plugin_key) LIKE '%:svn%' THEN 'Subversion' ELSE 
 CASE WHEN LOWER(vl.plugin_key) LIKE '%:git%' THEN 'Git' ELSE
 CASE WHEN LOWER(vl.plugin_key) LIKE '%:p4%' THEN 'Perforce' ELSE
 CASE WHEN LOWER(vl.plugin_key) LIKE '%:hg%' THEN 'Mercurial' ELSE
 CASE WHEN LOWER(vl.plugin_key) LIKE '%:bb%' THEN 'BitBucket' ELSE
 CASE WHEN LOWER(vl.plugin_key) LIKE '%:gh%' THEN 'GitHub' ELSE
 vl.plugin_key
 END END END END END END) AS repository_type, 
(CASE WHEN bd.xml_definition_data LIKE '%<daily>%' THEN 'Single Daily Build' ELSE
 CASE WHEN bd.xml_definition_data LIKE '%<schedule>%' THEN 'Cron Based Scheduling' ELSE
 CASE WHEN bd.xml_definition_data LIKE '%<poll>%' AND bd.xml_definition_data LIKE '%CRON%' THEN 'Polling the Repository for changes - Scheduled' ELSE
 CASE WHEN bd.xml_definition_data LIKE '%<poll>%' AND bd.xml_definition_data LIKE '%PERIOD%' THEN 'Polling the Repository for changes - Periodically'
 END END END END) AS build_strategy,
(SUBSTRING(bd.xml_definition_data, 
        (LOCATE('<cronExpression>', bd.xml_definition_data)) + 16, 
        (LOCATE('</cronExpression>', bd.xml_definition_data) - 16 - LOCATE('<cronExpression>', bd.XML_DEFINITION_DATA)))) AS cron_expression,
(SUBSTRING(bd.xml_definition_data, 
        (LOCATE('<pollingPeriod>', bd.xml_definition_data)) + 15, 
        (LOCATE('</pollingPeriod>', bd.xml_definition_data) - 15 - LOCATE('<pollingPeriod>', bd.XML_DEFINITION_DATA)))) AS polling_period,
(SUBSTRING(bd.xml_definition_data, 
        (LOCATE('<buildTime>', bd.xml_definition_data)) + 11, 
        (LOCATE('</buildTime>', bd.xml_definition_data) - 11 - LOCATE('<buildTime>', bd.XML_DEFINITION_DATA)))) AS build_time
  FROM plan_vcs_location AS pvl 
  JOIN vcs_location      AS vl ON pvl.vcs_location_id = vl.vcs_location_id 
  JOIN build             AS b  ON b.build_id = pvl.plan_id
  JOIN build_definition  AS bd ON b.build_id = bd.build_id
  JOIN project           AS p  ON b.project_id = p.project_id
ORDER BY project, plan;

Sample Result

PROJECTPLANREPOSITORY_TYPEBUILD_STRATEGYCRON_EXPRESSIONPOLLING_PERIODBUILD_TIME
Project ABCPlan ABCBitBucketPolling the Repository for changes - Periodically0 0 0 ? * *180 
Project ABCPlan DEFMercurialCron Based Scheduling0 10 0 ? * 1\,7  
Project XYZPlan XYZSubversionSingle Daily Build  18:00
Project XYZPlan 123PerforcePolling the Repository for changes - Scheduled0 0 1-23/3 ? * *300 

Notes:

1- Some functions in this query may work only with HSQL database, so you may need to modify it in order to run it in other database types.

2- This query was built and tested with Bamboo 4.4.5 database schema, so you may need to modify it in order to run it in older/newer database schemas.

Last modified on Aug 14, 2015

Was this helpful?

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