How to get the list of Bamboo agents along with it's ip address and the OS on which it is running
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
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
SQL query to get agent details along with it's IP address
1
2
3
4
5
6
SELECT DISTINCT AA.IP_ADDRESS,
Q.*
FROM QUEUE Q,
AGENT_AUTHENTICATION AA
WHERE Q.AGENT_TYPE = 'REMOTE'
AND Q.UUID = AA.UUID List of agent hosted on Windows OS
1
2
3
4
5
6
7
8
9
10
11
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.
Was this helpful?