How to find Changeset IDs included in Deployment Versions

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

This article will show how to find the last Repository Changeset – by commit ID or hash – included in a given deployment version using a SQL query.

Solution

To list Changeset IDs for all repository types

SELECT dv.NAME AS "Version",
       dp.NAME AS "Deployment Project",
       de.NAME AS "Deployment Environment",
       dvc.CHANGESET_ID AS "Commit Hash"
FROM DEPLOYMENT_PROJECT dp
JOIN DEPLOYMENT_VERSION dv ON (dp.DEPLOYMENT_PROJECT_ID = dv.PROJECT_ID)
JOIN DEPLOYMENT_VERSION_CHANGESET dvc ON (dv.DEPLOYMENT_VERSION_ID=dvc.DEPLOYMENT_VERSION_ID)
JOIN DEPLOYMENT_ENVIRONMENT de ON (de.PACKAGE_DEFINITION_ID = dp.DEPLOYMENT_PROJECT_ID)
WHERE dv.NAME = '<version name>';


To list only changesets/hashes for one Repository Type

Repository Types
Repository TypePlugin Key
Gitcom.atlassian.bamboo.plugins.atlassian-bamboo-plugin-git:gitv2
SVNcom.atlassian.bamboo.plugin.system.repository:svnv2
Bitbucket Server 4.0+com.atlassian.bamboo.plugins.stash.atlassian-bamboo-plugin-stash:bbserver
Stash

com.atlassian.bamboo.plugins.stash.atlassian-bamboo-plugin-stash:stash-rep

GitHubcom.atlassian.bamboo.plugins.atlassian-bamboo-plugin-git:gh
Bitbucket Cloudcom.atlassian.bamboo.plugins.atlassian-bamboo-plugin-bitbucket:bbCloud
Perforcecom.atlassian.bamboo.plugin.system.repository:p4
CVS

com.atlassian.bamboo.plugin.system.repository:cvs

Mercurial

com.atlassian.bamboo.plugins.atlassian-bamboo-plugin-mercurial:hg

SELECT dv.NAME AS "Version",
       dp.NAME AS "Deployment Project",
       de.NAME AS "Deployment Environment",
       dvc.CHANGESET_ID AS "Commit Hash"
FROM DEPLOYMENT_PROJECT dp
JOIN DEPLOYMENT_VERSION dv ON (dp.DEPLOYMENT_PROJECT_ID = dv.PROJECT_ID)
JOIN DEPLOYMENT_VERSION_CHANGESET dvc ON (dv.DEPLOYMENT_VERSION_ID=dvc.DEPLOYMENT_VERSION_ID)
JOIN DEPLOYMENT_ENVIRONMENT de ON (de.PACKAGE_DEFINITION_ID = dp.DEPLOYMENT_PROJECT_ID)
JOIN VCS_LOCATION vl ON (dvc.VCS_LOCATION_ID = vl.VCS_LOCATION_ID)
WHERE dv.NAME = '<version name>'
  AND vl.PLUGIN_KEY = '<plugin key>';

Sample Output

Version

Deployment Project

Deployment Environment

Commit Hash

release-1

Test Deploy 1

TestEnv 1

115f848562edd456ec110429468272a7dfe92eb4

release-1

Test Deploy 2

stg

ecbbc9ffe9d9a329bfe28e1bd414102868cf3215

release-1

Test Deploy 3

Deployment

ecbbc9ffe9d9a329bfe28e1bd414102868cf3215

Last modified on May 25, 2017

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.