Sql Query for Bamboo Deployments and related artifacts?

Tim Finch March 12, 2019

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?

1 answer

1 accepted

0 votes
Answer accepted
Daniel Santos
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
March 13, 2019

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 March 18, 2019

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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
March 18, 2019

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.

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events