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
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, firstname.lastname@example.org Application1, Production, 1.0.1, Success, 2018-06-21 11:29:36.1920094 +00:00, email@example.com Application2, Test, 1.0.2, Failed, 2018-06-21 11:29:36.1920094 +00:00, firstname.lastname@example.org
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
For the sake of this post, let’s assume that we already have:
- SQL Server running Octopus database with some deployment history
- Grafana with a version at least v5.1+ which supports Microsoft SQL Server data source
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.
Now that we have everything ready, we can start building the dashboard.
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.
If you hover over each annotation, you will see additional information.
Here’s how it looks:
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:
SELECT Distinct(EnvironmentName) FROM [DeploymentHistory]
- project name
SELECT Distinct(ProjectName) FROM [DeploymentHistory] WHERE EnvironmentName = '$Environment'
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:
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.
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.
Author Tomasz Gawlik