Overview

One of the biggest advantages of Grafana is that due to multiple data sources, it’s really easy to display and correlate data from multiple sources in one diagram. Octopus Deploy is really popular Continuous Integration platform. Whenever a new release is deployed, the history of that event is being kept in the underlying SQL Server database.

In this blog post, we will use Grafana SQL Server data source, in order to display information about our deployments. Although this post will focus on Octopus Deploy data structure, this approach should be easily transferrable into any other platform, which uses a SQL database.

Octopus data source

Deployment history data

Octopus has plenty of tables, but for our purpose, we just need to focus on dbo.DeploymentHistory.

SELECT ProjectName, EnvironmentName, ReleaseVersion, TaskState, CompletedTime, DeployedBy FROM [dbo].[DeploymentHistory]

Which could produce result as:

Application1, Test, 1.0.1, Success, 2018-06-20 16:19:16.8920094 +00:00, admin@company.com
Application1, Production, 1.0.1, Success, 2018-06-21 11:29:36.1920094 +00:00, admin@company.com
Application2, Test, 1.0.2, Failed, 2018-06-21 11:29:36.1920094 +00:00, admin@company.com

Column names are quite self-explanatory, by querying the table we can get full detailed information about:

  • Project name and environment
  • A specific version which was released
  • Release status
  • Release completion date and time
  • A user which triggered that release

Configuration

For the sake of this post, let’s assume that we already have:

Please note that you should create a separate user with very limited permissions. SELECT on a table is all that you need. The very quick script for the reference:

USE master
CREATE LOGIN grafanareader WITH PASSWORD='somePassword'

USE OctopusDB
CREATE USER grafanareader FROM LOGIN grafanareader
GRANT SELECT ON dbo.[DeploymentHistory] TO grafanareader

At this point, please go to Grafana, add new Microsoft SQL Server data source with those credentials and make sure that Grafana can connect.

Grafana dashboard

Now that we have everything ready, we can start building the dashboard.

Deployment annotations

We can use our deployment history and display it on various panels using annotations. Annotations seem to be a perfect match since we will usually want to analyze data and be able to correlate it with deployments.

Here’s the SQL code which we will use:

SELECT
    CompletedTime  as time,
    ProjectName as text,
    CONCAT(EnvironmentName, ',' , ReleaseVersion, ',' , DeployedBy) as tags
  FROM
    DeploymentHistory
  WHERE
    $__timeFilter(CompletedTime) AND TaskState = 'Success' 
  ORDER BY
    CompletedTime  ASC

We’re only interested in successful deployment here, as failed or cancelled one should not affect non-staging panels and measurements.

Annotations window

If you hover over each annotation, you will see additional information.

Here’s how it looks:

Annotations example

Usually, you might want to focus on one project and environment. Let’s introduce two variables, just to filter those annotations a little bit per:

  • environment
SELECT Distinct(EnvironmentName) FROM [DeploymentHistory]

Variables- environment

  • project name
SELECT Distinct(ProjectName) FROM [DeploymentHistory] WHERE EnvironmentName = '$Environment'

Variables- project

The really nice thing is that we can filter projects per environment, this way for a given environment you will be only able to select projects, which were deployed at least once.

We can use those two variables now to modify our annotation a little bit:

SELECT
    CompletedTime  as time,
    ProjectName as text,
    CONCAT(EnvironmentName, ',' , ReleaseVersion, ',' , DeployedBy) as tags
  FROM
    DeploymentHistory
  WHERE
    $__timeFilter(CompletedTime) AND TaskState = 'Success' AND ProjectName = '$Project' AND EnvironmentName = '$Environment'
  ORDER BY
    CompletedTime  ASC

Now we can focus more: Annotations for one project

Deployment history panels

We already have annotations so we can correlate our metrics with deployments. But we might want to visualize deployment history data too. Let’s build the panel, where we will be able to track the number of deployments grouped by state.

 SELECT
  $__timeGroup(CompletedTime, '15m',0) as time,
  COUNT(DeploymentId) as Deployment,
   TaskState as metric
FROM
  DeploymentHistory
WHERE
  $__timeFilter(CompletedTime)
GROUP BY
  $__timeGroup(CompletedTime, '15m'),
  TaskState
ORDER BY 1

15 minutes time bucket is, of course, arbitrary, you might want to adjust it or even introduce time grain variable so you will be able to dynamically change the view.

Panel with deployment history

Conclusion

Being able to display and annotate your deployments on Grafana dashboards is extremely helpful. I’m sure those few examples are only the beginning and deployment history data can be presented in many ways. There’s one limitation which I couldn’t handle yet when you try to select more than one project or the environment from variables - this is currently breaking the SQL query.