Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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 Galina Galkina likes this

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Bamboo

Bamboo 7.1 is here and is packed with value!

I'm happy to announce that Bamboo 7.1 has been released and it’s overflowing with awesome new features. Top-voted issues First and foremost, a bunch of JAC top voted issues has been delivered - y...

595 views 1 6
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you