How to get the list of Bamboo agents along with it's ip address and the OS on which it is running
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
The purpose of this page is to provide a DB query that will help pull out the details of the below from Bamboo DB.
- Agent and it's ip address
- List of agent running on either Windows or Linux OS
The content on this page relates to platforms which are supported; however, the content is out of scope of our Atlassian Support Offerings. Consequently, Atlassian cannot guarantee support. Please be aware that this material is provided for your information only and you may use it at your own risk.
Building SQL statements for reporting purposes is not part of the Atlassian Support scope and this work is provided "as-is", on a best-effort basis. Queries may work better or worse than expected, your mileage may vary. It is up to each customer to analyze each query individually and understand if that is enough for their specific needs.
Environment
The queries have been tested on Bamboo 8.2.6 and PostgreSQL and may work for other supported version and DB as well.
Solution
SELECT DISTINCT AA.IP_ADDRESS,
Q.*
FROM QUEUE Q,
AGENT_AUTHENTICATION AA
WHERE Q.AGENT_TYPE = 'REMOTE'
AND Q.UUID = AA.UUID SELECT DISTINCT AA.IP_ADDRESS,
Q.*
FROM QUEUE Q,
AGENT_AUTHENTICATION AA,
CAPABILITY_SET CS,
CAPABILITY C
WHERE Q.AGENT_TYPE = 'REMOTE'
AND Q.UUID = AA.UUID
AND Q.CAPABILITY_SET = CS.CAPABILITY_SET_ID
AND CS.CAPABILITY_SET_ID = C.CAPABILITY_SET
AND C.VALUE LIKE '%Windows%' In Bamboo DB we don't store OS details of the agent anywhere, the above query works with the assumption that in each Windows agent there is a capability defined whose path contains "Windows" text, you may need to recheck this to find something common across all the agent and then replace that text in the query above.
Similarly the above query can be modified to get list of linux agents by replacing C.VALUE with a capability text which is common across all the linux agents.