How to list the details of Deployments which use a particular plugin in a task from the Bamboo Data Center database
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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
The steps outlined on this article are provided AS-IS. This means we've had reports of them working for some customers — under certain circumstances — yet are not officially supported, nor can we guarantee they'll work for your specific scenario.
You may follow through and validate them on your own non-prod environments prior to production or fall back to supported alternatives if they don't work out.
We also invite you to reach out to our Community for matters that fall beyond Atlassian's scope of support!
Summary
The purpose of this article is to fetch the details of Deployments like Deployment project id, Deployment env name,Deployment State, Finished date, Component (Task), Appname(Task) and Environment (Task) for all the deployments which uses a particular plugin in a task from the Bamboo database.The queries below were tested against a MySQL and Postgres database. If you use a different database management system, you’ll need to translate the query above into your DBMS’s syntax.
Note
The queries below were tested against a Oracle database. If you use a different database management system, you’ll need to translate the query above into your DBMS’s syntax.
Environment
- The solution was tested on Bamboo 9.6.5, but it will be applicable to other supported versions as well.
- Tested on Oracle 12c Database.
Solution
The below SQL can be used to fetch details of Deployment projects which use a particular plugin in a task,
SELECT DP.NAME, DE.NAME, DR.FINISHED_DATE, DR.DEPLOYMENT_STATE,
XMLTYPE(DE.XML_DEFINITION_DATA).EXTRACT('//configuration/taskDefinition[pluginKey/text()="com.go2group.ibm-websphere-plugin:task.udeploy.deployComponent"]/config/item[key/text()="component"]/value/text()').getStringVal() component,
XMLTYPE(DE.XML_DEFINITION_DATA).EXTRACT('//configuration/taskDefinition[pluginKey/text()="com.go2group.ibm-websphere-plugin:task.udeploy.deployComponent"]/config/item[key/text()="appName"]/value/text()').getStringVal() appName,
XMLTYPE(DE.XML_DEFINITION_DATA).EXTRACT('//configuration/taskDefinition[pluginKey/text()="com.go2group.ibm-websphere-plugin:task.udeploy.deployComponent"]/config/item[key/text()="envName"]/value/text()').getStringVal() Environment
FROM DEPLOYMENT_ENVIRONMENT DE, DEPLOYMENT_PROJECT DP,DEPLOYMENT_RESULT DR
WHERE DE.package_definition_id = DP.deployment_project_id
AND DR.ENVIRONMENT_ID = DE.ENVIRONMENT_ID
AND DE.XML_DEFINITION_DATA LIKE '%<plugin-class>%'The below SQL can be used to fetch details of Deployment projecs which use a particluar plugin in a task, in this case we have taken plugin for U deploy task - "com.go2group.ibm-websphere-plugin:task.udeploy.deployComponent" which were run in the last 1 year.
SELECT DP.NAME, DE.NAME, DR.FINISHED_DATE, DR.DEPLOYMENT_STATE,
XMLTYPE(DE.XML_DEFINITION_DATA).EXTRACT('//configuration/taskDefinition[pluginKey/text()="com.go2group.ibm-websphere-plugin:task.udeploy.deployComponent"]/config/item[key/text()="component"]/value/text()').getStringVal() component,
XMLTYPE(DE.XML_DEFINITION_DATA).EXTRACT('//configuration/taskDefinition[pluginKey/text()="com.go2group.ibm-websphere-plugin:task.udeploy.deployComponent"]/config/item[key/text()="appName"]/value/text()').getStringVal() appName,
XMLTYPE(DE.XML_DEFINITION_DATA).EXTRACT('//configuration/taskDefinition[pluginKey/text()="com.go2group.ibm-websphere-plugin:task.udeploy.deployComponent"]/config/item[key/text()="envName"]/value/text()').getStringVal() Environment
FROM DEPLOYMENT_ENVIRONMENT DE, DEPLOYMENT_PROJECT DP,DEPLOYMENT_RESULT DR
WHERE DE.package_definition_id = DP.deployment_project_id
AND DR.ENVIRONMENT_ID = DE.ENVIRONMENT_ID
AND DE.XML_DEFINITION_DATA LIKE '%<plugin-class>%'
AND DR.FINISHED_DATE between sysdate-365 and sysdate;Note
Replace the "plugin-class" with the class of the plugin. For example for udeploy its - com.go2group.ibm-websphere-plugin:task.udeploy.deployComponent. You can get this information by navigating to Manage apps→Click on installed plugin→Check the class name.