How to filter Bamboo plans using a particular task type or having a particular text via DB queries
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
The purpose of this page is to provide a DB query that will help pull out the details of the plans using below task types in Bamboo.
- Maven and it's version
- Node JS and it's version
- Ant and it's version
This page also contains a SQL query using which you can use to filter a plan for a particular text.
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.
Environment
Queries have been tested on Bamboo 8.1.3 on PostgreSQL and Oracle DB
Solution
SELECT B.FULL_KEY AS JOB_KEY,B.TITLE AS JOB_NAME, B1.FULL_KEY AS PLAN_KEY, B1.TITLE AS PLAN_NAME , XPATH('//configuration/buildTasks/taskDefinition[pluginKey/text()="com.atlassian.bamboo.plugins.maven:task.builder.mvn3"]/config/item[key/text()="label"]/value/text()', cast(BD.xml_definition_data as xml)) AS VERSION
FROM BUILD B, BUILD_DEFINITION BD , CHAIN_STAGE CS , BUILD B1
WHERE B.BUILD_ID=BD.BUILD_ID
AND B.STAGE_ID=CS.STAGE_ID
AND B1.BUILD_ID=CS.BUILD_ID
AND BD.XML_DEFINITION_DATA LIKE '%com.atlassian.bamboo.plugins.maven:task.builder.mvn3%'
ORDER BY PLAN_KEYThe above query will give results for all the Jobs which are configured with Maven 3 if you want to get results for Maven 2 and Maven 1, you will need to replace com.atlassian.bamboo.plugins.maven:task.builder.mvn3 in the above query at 2 places with the below values
- For Maven 2 : com.atlassian.bamboo.plugins.maven:task.builder.mvn2
- For Maven 1 : com.atlassian.bamboo.plugins.maven:task.builder.maven
SELECT B.FULL_KEY AS JOB_KEY,B.TITLE AS JOB_NAME, B1.FULL_KEY AS PLAN_KEY, B1.TITLE AS PLAN_NAME , XPATH('//configuration/buildTasks/taskDefinition[pluginKey/text()="com.atlassian.bamboo.plugins.ant:task.builder.ant"]/config/item[key/text()="label"]/value/text()', cast(BD.xml_definition_data as xml)) AS VERSION
FROM BUILD B, BUILD_DEFINITION BD , CHAIN_STAGE CS , BUILD B1
WHERE B.BUILD_ID=BD.BUILD_ID
AND B.STAGE_ID=CS.STAGE_ID
AND B1.BUILD_ID=CS.BUILD_ID
AND BD.XML_DEFINITION_DATA LIKE '%com.atlassian.bamboo.plugins.ant:task.builder.ant%'
ORDER BY PLAN_KEYSELECT B.FULL_KEY AS JOB_KEY,B.TITLE AS JOB_NAME, B1.FULL_KEY AS PLAN_KEY, B1.TITLE AS PLAN_NAME , XPATH('//configuration/buildTasks/taskDefinition[pluginKey/text()="com.atlassian.bamboo.plugins.bamboo-nodejs-plugin:task.builder.node"]/config/item[key/text()="runtime"]/value/text()', cast(BD.xml_definition_data as xml)) AS VERSION
FROM BUILD B, BUILD_DEFINITION BD , CHAIN_STAGE CS , BUILD B1
WHERE B.BUILD_ID=BD.BUILD_ID
AND B.STAGE_ID=CS.STAGE_ID
AND B1.BUILD_ID=CS.BUILD_ID
AND BD.XML_DEFINITION_DATA LIKE '%com.atlassian.bamboo.plugins.bamboo-nodejs-plugin:task.builder.node%'SELECT B.FULL_KEY AS JOB_KEY,B.TITLE AS JOB_NAME, B1.FULL_KEY AS PLAN_KEY, B1.TITLE AS PLAN_NAME
FROM BUILD B, BUILD_DEFINITION BD , CHAIN_STAGE CS , BUILD B1
WHERE B.BUILD_ID=BD.BUILD_ID
AND B.STAGE_ID=CS.STAGE_ID
AND B1.BUILD_ID=CS.BUILD_ID
AND BD.XML_DEFINITION_DATA LIKE '%sample_text%'
ORDER BY PLAN_KEY
Please replace sample_text with the text which you are looking to filter
How to convert above PostgreSQL queries to Oracle
There will be change to only one function which is being used to query XML_DEFINITION_DATA column from BUILD_DEFINTION table, below are the change in functions.
XPATH('//configuration/buildTasks/taskDefinition[pluginKey/text()="com.atlassian.bamboo.plugins.ant:task.builder.ant"]/config/item[key/text()="label"]/value/text()', cast(BD.xml_definition_data as xml)) AS VERSIONXMLTYPE(BD.XML_DEFINITION_DATA).EXTRACT('//configuration/buildTasks/taskDefinition[pluginKey/text()="com.atlassian.bamboo.plugins.ant:task.builder.ant"]/config/item[key/text()="label"]/value/text()').getStringVal() VERSIONSELECT B.FULL_KEY AS JOB_KEY,B.TITLE AS JOB_NAME, B1.FULL_KEY AS PLAN_KEY, B1.TITLE AS PLAN_NAME ,
XMLTYPE(BD.XML_DEFINITION_DATA).EXTRACT('//configuration/buildTasks/taskDefinition[pluginKey/text()="com.atlassian.bamboo.plugins.ant:task.builder.ant"]/config/item[key/text()="label"]/value/text()').getStringVal() VERSION
FROM BUILD B, BUILD_DEFINITION BD , CHAIN_STAGE CS , BUILD B1
WHERE B.BUILD_ID=BD.BUILD_ID
AND B.STAGE_ID=CS.STAGE_ID
AND B1.BUILD_ID=CS.BUILD_ID
AND BD.XML_DEFINITION_DATA LIKE '%com.atlassian.bamboo.plugins.ant:task.builder.ant%'
ORDER BY PLAN_KEY