Is there any way of querying the Bamboo database to bring back details of each time a deployment was run, to which environment and what artifacts were involved?
I have (thanks to another question on here) got the first part:-
SELECT dp.name AS deploy_name, de.name AS env_name, dr.started_date, dr.queued_date, dr.executed_date, dr.finished_date 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 AND de.name = '<Your_Environment_Name_Here>'
However, I'd like to tie into the results returned the ability to see what artifacts are linked to each deployment.
Is this possible?
Hi @Tim Finch
Yes, after some deeper investigation.
Can you please check if this will address your needs:
FROM DEPLOYMENT_PROJECT DP
JOIN DEPLOYMENT_ENVIRONMENT DE ON DP.DEPLOYMENT_PROJECT_ID = DE.PACKAGE_DEFINITION_ID
JOIN DEPLOYMENT_RESULT DR ON DE.ENVIRONMENT_ID = DR.ENVIRONMENT_ID
JOIN DEPLOYMENT_VERSION DV ON DV.DEPLOYMENT_VERSION_ID=DR.VERSION_ID
JOIN DEPLOYMENT_VERSION_ITEM DVI ON DVI.DEPLOYMENT_VERSION_ID=DV.DEPLOYMENT_VERSION_ID
JOIN DEPLOYMENT_VERSION_ITEM_BA DVIB ON DVIB.VERSION_BAM_ARTIFACT_ITEM_ID=DVI.DEPLOYMENT_VERSION_ITEM_ID
WHERE DE.NAME = '<ENVIRONMENT_NAME>'
I'll wait for your findings.