Query Database

Legacy Uncategorised
1

I would like to do some analysis on how long it takes changes to go from first checkin through prod. Is there a way I can query the database to get this information?

2

Hi Jeremy,

Thanks for reaching out. As Octopus only takes care of the deployment part, the only data you’ll be able to get from it is the overall deployment duration. The check in and build time are info you should be getting from your build server.

Rather than querying the database, i recommend you to get this information from the API. If you’d like i can provide a code snippet in Powershell to get the duration of a deployment.

Thanks,

Dalmiro

3

Yes, that would be great. I think just using an increment in the dev build number through it going to prod would be enough.

4

Hi Jeremy,

I’ve started a Powershell module that includes a cmdlet called Get-OctopusDeployment that gets the following info about deployments:

ProjectName         : Powershell3
EnvironmentName     : Development
DeploymentstartTime : 6/24/2015 7:28:23 PM
DeploymentEndTime   : 6/24/2015 7:28:47 PM
DeploymentStartedBy : dalmiro
ID                  : deployments-1606
Duration            : 0.39
Status              : Success
ReleaseVersion      : 0.0.1
ReleaseCreationDate : 6/24/2015 7:28:16 PM
ReleaseNotes        : 
ReleaseCreatedBy    : dalmiro
Package             : {}
Resource            : Octopus.Client.Model.DeploymentResource

The Duration property is in minutes.

Check it out and if you cant make it work, let me know and i’ll give you a hand.

Hope that helps!

Dalmiro

5

I don’t think this is exactly what I am looking for. I am looking to know when a release was deployed to dev and how long it was before it was deployed to Prod.

6

Hi Jeremy,

What if I add a parameter to that cmdlet that lets you filter by release (along with project and environment). That way you could run something like

Get-OctopusDeployment -Project MyProject -Release 1.0.0

And you’ll get all the deployments made for that release that’ll look something like this

ProjectName         : MyProject
EnvironmentName     : Dev
DeploymentstartTime : 6/24/2015 7:28:23 PM
DeploymentEndTime   : 6/24/2015 7:28:47 PM
DeploymentStartedBy : dalmiro
ID                  : deployments-1606
Duration            : 0.39
Status              : Success
ReleaseVersion      : 1.0.0
ReleaseCreationDate : 6/24/2015 7:28:16 PM
ReleaseNotes        : 
ReleaseCreatedBy    : dalmiro
Package             : {}
Resource            : Octopus.Client.Model.DeploymentResource

ProjectName         : MyProject
EnvironmentName     : Prod
DeploymentstartTime : 6/25/2015 7:28:23 PM
DeploymentEndTime   : 6/25/2015 7:28:47 PM
DeploymentStartedBy : dalmiro
ID                  : deployments-1607
Duration            : 0.39
Status              : Success
ReleaseVersion      : 1.0.0
ReleaseCreationDate : 6/24/2015 7:28:16 PM
ReleaseNotes        : 
ReleaseCreatedBy    : dalmiro
Package             : {}
Resource            : Octopus.Client.Model.DeploymentResource

You’ll be able to calculate how long it passed since the deployment to Dev and the one to Prod by using the properties DeploymentstartTime of each entry.

Let me know if that makes sense to you and i’ll add that to the script.

Thanks!

Dalmiro

7

That is a great start. I need to know how to take it from there.

Jeremy Hillin

8

Hi Jeremy,

I’ve added the parameter -ReleaseVersion to Get-OctopusDeployment on the version 0.2.77 of the module. It should work like this:

  1. Get all the deployments of a specific release and store them into a variable
$deployments = Get-OctopusDeployment -ProjectName Powershell3 -ReleaseVersion 0.0.4

Lets say I only deployed this release once on Development and Staging, so the $Deployments variable will hold these 2 values:

ProjectName         : Powershell3
EnvironmentName     : Staging
DeploymentstartTime : 6/26/2015 7:52:28 PM
DeploymentEndTime   : 6/26/2015 7:52:31 PM
DeploymentStartedBy : dalmiro
ID                  : deployments-1730
Duration            : 0.05
Status              : Success
ReleaseVersion      : 0.0.4
ReleaseCreationDate : 6/26/2015 7:52:03 PM
ReleaseNotes        :
ReleaseCreatedBy    : dalmiro
Package             : {}
Resource            : Octopus.Client.Model.DeploymentResource

ProjectName         : Powershell3
EnvironmentName     : Development
DeploymentstartTime : 6/26/2015 7:52:14 PM
DeploymentEndTime   : 6/26/2015 7:52:17 PM
DeploymentStartedBy : dalmiro
ID                  : deployments-1729
Duration            : 0.06
Status              : Success
ReleaseVersion      : 0.0.4
ReleaseCreationDate : 6/26/2015 7:52:03 PM
ReleaseNotes        :
ReleaseCreatedBy    : dalmiro
Package             : {}
Resource            : Octopus.Client.Model.DeploymentResource
  1. Using the cmdlet new-timespan you can calculate the time that passed between 2 values. With this line of code we’ll be calculating the time between the Development deployment and the one in staging 
New-TimeSpan $deployments[0].DeploymentstartTime $deployments[1].DeploymentstartTime

which will return something like this

Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 14
Milliseconds      : 327
Ticks             : 143270000
TotalDays         : 0.000165821759259259
TotalHours        : 0.00397972222222222
TotalMinutes      : 0.238783333333333
TotalSeconds      : 14.327
TotalMilliseconds : 14327

Instead of using new-timespan you could send those objects to a csv file, a database or even turn them into JSON to consume them from a website.

Regards,

Dalmiro