Generating Bamboo Agents counts from the database

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

Bamboo provides a web interface to manage Agents. Though most Bamboo users are happy with that interface, some Enterprise customers have specific requirements that may exceed the usability of the Web UI.
This document describes an SQL query that would assist in extracting Agent counts from the Bamboo database:

  • Agent details and dedication relationships
  • Elastic Agent dependencies
  • Last known build and agent status
  • Uptime (last known information)

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 analyse each query individually and understand if that is enough for their specific needs.

Environment

Bamboo 7, 8

Solution

Use the following SQL to gather statistics about Bamboo Agents.

The SQL below will capture:

  • Information from LOCAL, REMOTE and ELASTIC agents - you can customise it on the WHERE clause
  • Known Uptime: This is the last known time that Bamboo has about that Agent. It is not an indicator of the current uptime of the Agent (but it may be if the agent is up and connected)

Make sure to update any "AT TIME ZONE 'AEST'" occurrences to reflect your local timezone

This SQL works on PostgreSQL and may require changes to work with additional databases

SELECT Q.QUEUE_ID "Agent Id",
       Q.UUID "UUID",
       EI.NAME "Elastic Image Name",
       Q.TITLE "Name",
       Q.AGENT_TYPE "Type",
       Q.AGENT_DESCRIPTION "Description",
       Q.CREATED_DATE "Creation Date",
       Q.LAST_STOP_TIME "Last Stop Time",
       Q.LAST_START_TIME "Last Start Date",
       BRSK.BUILD_KEY "Last Build",
       BRS.BUILD_COMPLETED_DATE "Last Build Date",
       CASE
         WHEN Q.AGENT_TYPE = 'LOCAL' THEN Q.AGENT_TYPE
         WHEN Q.LAST_STOP_TIME IS NULL AND Q.LAST_START_TIME IS NOT NULL THEN CAST(
           (SELECT DATE_TRUNC('SECOND', CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - Q.LAST_START_TIME AT TIME ZONE 'AEST')) AS TEXT)
         WHEN Q.LAST_STOP_TIME IS NOT NULL AND Q.LAST_START_TIME IS NOT NULL THEN CAST(
           (SELECT DATE_TRUNC('SECOND', Q.LAST_STOP_TIME - Q.LAST_START_TIME)) AS TEXT)  
       END AS "Known Uptime",
       CASE
         WHEN ( Q.LAST_START_TIME > Q.LAST_STOP_TIME OR Q.LAST_STOP_TIME IS NULL ) THEN TRUE
         ELSE FALSE
       END "Online",
       CASE
         WHEN Q.ENABLED IS TRUE AND Q.AGENT_TYPE = 'ELASTIC' AND Q.LAST_STOP_TIME IS NOT NULL THEN FALSE
         WHEN Q.ENABLED IS TRUE THEN TRUE
         ELSE FALSE
       END "Enabled",
       AA.EXECUTABLE_TYPE "Dedication Type",
       CASE
         WHEN AA.EXECUTABLE_TYPE = 'PROJECT' THEN CAST(
           (SELECT PROJECT_KEY FROM PROJECT WHERE PROJECT_ID = AA.EXECUTABLE_ID) AS TEXT)
         WHEN AA.EXECUTABLE_TYPE IN ('PLAN', 'JOB') THEN CAST(
           (SELECT FULL_KEY FROM BUILD WHERE BUILD_ID = AA.EXECUTABLE_ID) AS TEXT)
         WHEN AA.EXECUTABLE_TYPE = 'ENVIRONMENT' THEN CAST(
           (SELECT NAME FROM DEPLOYMENT_ENVIRONMENT WHERE ENVIRONMENT_ID = AA.EXECUTABLE_ID) AS TEXT)
         WHEN AA.EXECUTABLE_TYPE = 'DEPLOYMENT_PROJECT' THEN CAST(
             (SELECT NAME FROM DEPLOYMENT_PROJECT WHERE DEPLOYMENT_PROJECT_ID = AA.EXECUTABLE_ID) AS TEXT)
       END "Dedicated object"
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
       LEFT JOIN AGENT_ASSIGNMENT AA
          ON AA.EXECUTOR_ID = Q.QUEUE_ID
       LEFT JOIN ELASTIC_IMAGE EI
          ON Q.ELASTIC_IMAGE = EI.ELASTIC_IMAGE_ID
       LEFT JOIN (SELECT MAX(BUILD_KEY) AS BUILD_KEY,
                         BUILD_AGENT_ID
                  FROM   BUILDRESULTSUMMARY
                  GROUP  BY BUILD_AGENT_ID) BRSK
              ON Q.QUEUE_ID = BRSK.BUILD_AGENT_ID
WHERE  Q.AGENT_TYPE IN ( 'REMOTE', 'LOCAL', 'ELASTIC' )
ORDER  BY Q.CREATED_DATE,
          BRS.BUILD_COMPLETED_DATE;

Last modified on Sep 16, 2022

Was this helpful?

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