Sql Query for Bamboo Deployments and related artifacts?

[Tim Finch]

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?

[Daniel Santos · Accepted solution]

Hi @Tim Finch

Yes, after some deeper investigation. 

Can you please check if this will address your needs:

SELECT DP.NAME,
       DE.NAME,
       DR.STARTED_DATE,
       DR.QUEUED_DATE,
       DR.EXECUTED_DATE,
       DR.FINISHED_DATE,
       DV.NAME,
       DV.PLAN_BRANCH_NAME,
       DV.CREATOR_USERNAME,
       DVIB.*

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.

[Tim Finch]

Thanks @Daniel Santos 

From my perspective this looks fantastic and works successfully.

I have sent the data to the relevant delivery teams to ensure it also meets their criteria.

Thank you for your help.

Tim

[Daniel Santos]

You are welcome @Tim Finch
If you don't mind, when you confirm this, please accept the answer as a solution to help other Community users on finding it.