Summary

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.

Environment

• 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.

Solution

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,
                Q.TITLE AGENT_NAME,
				Q.AGENT_TYPE,
                AA.EXECUTABLE_TYPE OBJECT_TYPE,
                AA.EXECUTABLE_ID OBJECT_ID
		   FROM AGENT_ASSIGNMENT AA,
                QUEUE Q
	      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:

SELECT BUILD_ID,
       BUILD_TYPE,
	   FULL_KEY,
       TITLE
  FROM BUILD
 WHERE BUILD_ID IN (720897,720898);

Removing agent dedication

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);