How to identify and remove Bamboo agent dedication from the database
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
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.
Agent dedication is usually done through the Bamboo UI ("Dedicating an agent"). Some use cases might require Bamboo admins to remove agent dedication in bulk, which is not possible from the UI. The purpose of this article is to help you identify and remove agent dedication from the database.
- The queries have been tested on PostgreSQL and might need to be changed to work on other database types.
- The queries have been tested against Bamboo 8 but will likely work with other versions of Bamboo.
Listing agent dedication
Use the following query to get a list of agents that have been dedicated in your Bamboo instance:
SELECT DISTINCT AA.ASSIGNMENT_ID,
FROM AGENT_ASSIGNMENT AA,
WHERE AA.EXECUTOR_ID = Q.QUEUE_ID;
The output of the query should look like this:
In the example above you can see the following details:
- We have two agents that have been dedicated to different objects named Default Agent and 192.168.10.116.
- Each row represents an agent dedication to a certain object. There are only five types of objects: PROJECT, PLAN, JOB, DEPLOYMENT_PROJECT, and ENVIRONMENT.
- A local agent named Default Agent has been dedicated to a project, plan, and job.
- A remote agent named 192.168.10.116 has been dedicated to a deployment project and an environment.
The query will not give you the name of the objects but it will give you their IDs. The IDs can be used to identify the objects that the agents have been dedicated to using the following queries:Finding PROJECT details
SELECT PROJECT_ID, PROJECT_KEY, TITLE FROM PROJECT WHERE PROJECT_ID = <replaceWithObjectID>;Finding PLAN & JOB details
SELECT BUILD_ID, BUILD_TYPE, FULL_KEY, TITLE FROM BUILD WHERE BUILD_ID = <replaceWithObjectID>;Finding DEPLOYMENT_PROJECT details
SELECT DEPLOYMENT_PROJECT_ID, NAME DEPLOYMENT_PROJECT_NAME, PLAN_KEY SOURCE_BUILD_PLAN FROM DEPLOYMENT_PROJECT WHERE DEPLOYMENT_PROJECT_ID = <replaceWithObjectID>;Finding ENVIRONMENT details
SELECT DE.ENVIRONMENT_ID, DE.NAME ENVIRONMENT_NAME, DP.NAME DEPLOYMENT_PROJECT_NAME FROM DEPLOYMENT_ENVIRONMENT DE, DEPLOYMENT_PROJECT DP WHERE DE.PACKAGE_DEFINITION_ID = DP.DEPLOYMENT_PROJECT_ID AND ENVIRONMENT_ID = <replaceWithObjectID>;
Following the example above, if we want to know what are the names of the plan and job that the Default Agent has been dedicated to we can use the following select query:
WHERE BUILD_ID IN (720897,720898);
Removing agent dedication
Alwaysyour data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
In order to remove the agent dedication you need to identify the row that holds the dedication you want to delete and take note of its ASSIGNMENT_ID. You'll find the ASSIGNMENT_ID of the row that needs to be removed in the result from the first select query mentioned under the "Listing agent dedication" section. Once you have the ID you can run the following delete query:
DELETE FROM AGENT_ASSIGNMENT
WHERE ASSIGNMENT_ID = <replaceWithAssignmentID>;
Taking the example we've been following, if we want to remove dedication from the Default Agent on the plan and job mentioned previously we have to run:
DELETE FROM AGENT_ASSIGNMENT
WHERE ASSIGNMENT_ID in (950274,950275);