Bamboo SQL Query to fetch Agent capabilities,Job requirements and Dedicated agent details
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
本記事で説明している手順は、現時点でのものとなります。そのため、一部のお客様で特定の状況下で動作したという報告がありますが、正式にサポートされているわけではなく、お客様の特定のシナリオで動作することを保証するものではありません。
本番環境での実施の前に一通り非本番環境で検証し、成功しなかった場合にはサポートされている代替案にフォール バックしてください。
要約
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.
環境
Tested on Bamboo 9.2.1 with PostgreSQL DB.
ソリューション
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 Elastic Images and their capabilities set.
SELECT EI.ELASTIC_IMAGE_ID, EI.NAME, EI.AMI_IMAGE_ID, EI.DESCRIPTION, C.KEY_IDENTIFIER, C.VALUE FROM ELASTIC_IMAGE EI JOIN CAPABILITY_SET CS ON EI.CAPABILITY_SET = CS.CAPABILITY_SET_ID JOIN CAPABILITY C ON CS.CAPABILITY_SET_ID = C.CAPABILITY_SET ORDER BY EI.ELASTIC_IMAGE_ID
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