要約

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.
• Which plans have an Artifactory Plugin task configured. 

環境

Tested on Bamboo 8.1.2 with PostgreSQL.

ソリューション

• SQL to list all the dedicated agents in the 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 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
  ORDER  BY q.title; 

• SQL to get details of which plans have an Artifactory Plugin task configured.

  SELECT b.build_type,
         b.full_key,
         b.buildkey  AS plankey,
         b.title     AS plan_name,
         b1.build_type,
         b1.full_key,
         b1.buildkey AS jobkey,
         b1.title    AS job_name
  FROM   build b,
         build b1,
         build_definition bd,
         chain_stage cs
  WHERE  b1.build_id = bd.build_id
         AND bd.xml_definition_data LIKE '%bamboo-artifactory-plugin%'
         AND b1.stage_id = cs.stage_id
         AND cs.build_id = b.build_id
  ORDER  BY b.full_key;