How to get the list of Bamboo agents along with it's ip address and the OS on which it is running

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform notice: Server and Data Center only. This article only applies to Atlassian products on the server and data center platforms.

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.

  1. Agent and it's ip address
  2. 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
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
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.  




Last modified on Nov 29, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.