How to get a list of remote agents that haven't performed any builds
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
It is possible to extract the following details from the Bamboo database for agents that:
Haven't been dedicated
Haven't performed a build for more than 1 month
Haven't performed a build at all
Environment
Bamboo 7.1.2
Solution
This solution will require you to have the appropriate access to run the below SQL queries on your Bamboo database.
1
2
3
4
5
6
7
SELECT QUEUE_ID, TITLE, AGENT_TYPE, AGENT_DESCRIPTION, CREATED_DATE, LAST_START_TIME, LAST_STOP_TIME, BUILD_COMPLETED_DATE AS LAST_BUILD_DATE
FROM QUEUE Q LEFT JOIN
(SELECT MAX(BUILD_COMPLETED_DATE) AS BUILD_COMPLETED_DATE, BUILD_AGENT_ID FROM BUILDRESULTSUMMARY GROUP BY BUILD_AGENT_ID) BRS ON Q.QUEUE_ID = BRS.BUILD_AGENT_ID
WHERE (BRS.BUILD_COMPLETED_DATE < current_timestamp - interval '1' month OR BRS.BUILD_COMPLETED_DATE IS NULL)
AND AGENT_TYPE IN ('LOCAL','REMOTE')
AND QUEUE_ID NOT IN (SELECT EXECUTOR_ID FROM AGENT_ASSIGNMENT)
ORDER BY LAST_BUILD_DATE;Please note, build result expiry will affect this and there's no way to account for it. The agent may have performed a build yesterday, but if that build result was expired – it will seem as if it never built anything or show the most recent unexpired result. You may want to base it off LAST_START_TIME / LAST_STOP_TIME to get an idea of agent age and when it was last known to Bamboo.
In case you want to include the agent's UUID in the results, you can add one more column to the above select query. However please note that the UUID for elastic and local agents are not stored inside the database. This is mostly used to uniquely identify remote agents that need approval. Local agents and elastic agents don't need approval. They don't go through the regular authentication mechanism like remote agents do.
Was this helpful?