Summary

This article provides SQL queries to retrieve the following items:

• All dedicated agents and which build_type they are dedicated to.
• Details of executable types ( Plans and Jobs ) and which dedicated agent these are linked to.
• All jobs and their requirements set.
• All agents and their capabilities set.

Environment

Tested on Bamboo 9.2.1 with PostgreSQL DB.

Solution

• SQL to list all the dedicated agents in Bamboo and for which build type they are dedicated.

  SELECT AA.EXECUTABLE_ID,
         AA.EXECUTABLE_TYPE,
         Q.AGENT_TYPE,
         Q.TITLE AS dedicated_agentname
  FROM   AGENT_ASSIGNMENT AA
         JOIN QUEUE Q
           ON Q.QUEUE_ID = AA.EXECUTOR_ID; 

• SQL to list details of the executable_type(Plan & Job) and which agent it is dedicated to.

  SELECT AA.EXECUTABLE_TYPE AS build_type,
         B.FULL_KEY         AS full_key,
         B.TITLE,
         Q.AGENT_TYPE,
         Q.TITLE            AS dedicated_agentname
  FROM   AGENT_ASSIGNMENT AA
         JOIN BUILD B
           ON AA.EXECUTABLE_ID = B.BUILD_ID
         JOIN QUEUE Q
           ON Q.QUEUE_ID = AA.EXECUTOR_ID
  ORDER  BY B.TITLE; 

• SQL to list all jobs and their requirements set.

  SELECT B.BUILD_TYPE,
         B.FULL_KEY,
         B.TITLE,
         R.KEY_IDENTIFIER AS requirement_type
  FROM   BUILD B
         JOIN REQUIREMENT_SET RS
           ON B.REQUIREMENT_SET = RS.REQUIREMENT_SET_ID
         JOIN REQUIREMENT R
           ON R.REQUIREMENT_SET = RS.REQUIREMENT_SET_ID
  ORDER  BY B.FULL_KEY; 

• SQL to list all Remote agents and their capabilities set.

  SELECT Q.AGENT_TYPE,
         Q.TITLE,
         C.KEY_IDENTIFIER,
         C.VALUE
  FROM   QUEUE Q
         JOIN CAPABILITY_SET CS
           ON Q.CAPABILITY_SET = CS.CAPABILITY_SET_ID
         JOIN CAPABILITY C
           ON CS.CAPABILITY_SET_ID = C.CAPABILITY_SET
           WHERE Q.AGENT_TYPE = 'REMOTE' 
  ORDER  BY Q.TITLE;  

• SQL to list all capabilities that are specific to particular local agents.

  SELECT Q.TITLE          AS AGENT,
         C.KEY_IDENTIFIER AS CAPABILITY,
         C.VALUE
  FROM   CAPABILITY C
         JOIN CAPABILITY_SET CS
           ON C.CAPABILITY_SET = CS.CAPABILITY_SET_ID
         JOIN QUEUE Q
           ON Q.CAPABILITY_SET = CS.CAPABILITY_SET_ID
  WHERE  CAPABILITY_TYPE = 'LOCAL'
         AND CAPABILITY_SCOPE = 'AGENT'
         AND Q.AGENT_TYPE = 'LOCAL' 

• SQL to list all capabilities that are shared among all the local agents.

  SELECT C.KEY_IDENTIFIER AS CAPABILITY,
         C.VALUE
  FROM   CAPABILITY C
         JOIN CAPABILITY_SET CS
           ON C.CAPABILITY_SET = CS.CAPABILITY_SET_ID
  WHERE  CAPABILITY_TYPE = 'LOCAL'
         AND CAPABILITY_SCOPE = 'SHARED'
  ORDER  BY C.KEY_IDENTIFIER