How to filter Bamboo plans using a particular task type or having a particular text via DB queries
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
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
PostgreSQL DB query for tasks using Maven 3
1
2
3
4
5
6
7
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
PostgreSQL DB query for tasks using Ant
1
2
3
4
5
6
7
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_KEYPostgreSQL DB query for tasks using Node JS
1
2
3
4
5
6
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.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%'SQL query to filter a plan for a particular text
1
2
3
4
5
6
7
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.
PostgreSQL
1
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 VERSIONOracle
1
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() VERSIONOracle DB query for Ant task
1
2
3
4
5
6
7
8
SELECT 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_KEYWas this helpful?