Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,362,409
Community Members
 
Community Events
168
Community Groups

Sql Query for Bamboo Deployments and related artifacts?

Edited

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

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.

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

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

Atlassian Community Events