Generating Bamboo Agents counts from the database
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
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 Server and Data Center
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. EPHEMERAL agents will only show up in the DB during their existence so not required to be "cleaned-up"
- 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;