SQL Query to extract Bamboo deployment duration details
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
This article contains SQL query to extract the time taken for Deployments along with the Environment and the Deployment project details.
Solution
Below SQL query can be used to extract the details. The query has been tested in PostgreSQL DB but will work fine for other DB types.
SELECT DP.NAME AS DEPLOYMENT_PROJECT,
DE.NAME AS DEPLOYMENT_ENVIRONMENT,
DR.STARTED_DATE,
DR.QUEUED_DATE,
DR.EXECUTED_DATE,
DR.FINISHED_DATE,
DR.FINISHED_DATE - DR.EXECUTED_DATE as DEPLOYMENT_DURATION
FROM DEPLOYMENT_PROJECT DP,
DEPLOYMENT_ENVIRONMENT DE,
DEPLOYMENT_RESULT DR
WHERE DP.DEPLOYMENT_PROJECT_ID = DE.PACKAGE_DEFINITION_ID
AND DE.ENVIRONMENT_ID = DR.ENVIRONMENT_ID
ORDER BY DP.NAME,
DE.NAME,
DR.STARTED_DATE