Setting Up InfluxDb, Chronograf, and Grafana for the SqlServer Dev

There are some beautiful ways to visualize time series data with the tools I’m going to go over. This post is purely focused on the initial setup and saving you some time there. In a future post, I’ll show how some of these tools can help you visualize your server performance in a powerful way, including taking metrics from multiple types of servers that be working with SQL Server, and combining the metrics when appropriate to give a full picture of performance.

A beautiful way to visualize performance across a variety of machines

It’s pretty epic to combine information across a variety of sources and be able to relate the metrics to the “big picture” that individual machine monitoring might fail to shed light on.

Downloading

I started by running this quick powershell script to download the stable toolkit.

Download InfluxDB. A quick parsing of the html of the webpage to get you the latest URL version of the stable builds and download and unzip to local directory. This includes a download of whatever Chronograf version is available since at the time of this it was in beta and not a stable build

GitHub


clear-host

[datetime]$StepTimer = [datetime]::Now
$url = 'https://portal.influxdata.com/downloads'
$DownloadDirectory = 'C:\Influx'

if(!([io.directory]::Exists($DownloadDirectory)))
{New-Item -Path 'C:\Influx' -ItemType directory -Force -ErrorAction Ignore -Verbose}

$PageHtml = Invoke-WebRequest -Uri $url 

$PageHtml | select-string -Pattern '(https\:\/\/.*?\.\d{1,2}_windows_amd64.zip)|(https:\/\/dl\.influxdata\.com\/chronograf.*?_windows_amd64.zip)' -AllMatches | select -expand Matches|  % { 
        $NewFileName = $_.Groups[0] | select-string -Pattern '(?<=\/)\w*?(?=\-)'-AllMatches |  select -ExpandProperty Matches -first 1 
        $DownloadUrl = $_.Groups[0].Value
        $DownloadedFile = ([io.path]::Combine($DownloadDirectory,"$NewFileName.zip"))
        $UnzipLocation = ([io.path]::Combine($DownloadDirectory,$NewFileName)) 
        write-debug "--- Downloading ---`nParsed Name: $($NewFIleName)`nDownload URL: $($DownloadURL)`nUnzipLocation: $($UnzipLocation)"

        Invoke-WebRequest -Uri $DownloadUrl -OutFile ([io.path]::Combine($DownloadDirectory,"$NewFileName.zip")) -Verbose
       
        if([io.directory]::Exists($DownloadDirectory))
        {Remove-Item -Path $UnzipLocation -Force -ErrorAction Ignore -Verbose -recurse}

        Expand-Archive -Path $DownloadedFile -DestinationPath ([io.path]::Combine($DownloadDirectory))  -Force
        Remove-Item -Path $DownloadedFile -Force -Verbose
      
}

Invoke-WebRequest -Uri 'https://github.com/influxdata/telegraf/archive/master.zip' -OutFile ([io.path]::Combine($DownloadDirectory,"Telegraf.zip")) -Verbose
Expand-Archive -Path ([io.path]::Combine($DownloadDirectory,"Telegraf.zip")) -DestinationPath ([io.path]::Combine($DownloadDirectory))  -Force
Remove-Item -Path ([io.path]::Combine($DownloadDirectory,"Telegraf.zip")) -Force -Verbose
      

write-debug( "{0:hh\:mm\:ss\.fff} {1}: finished" -f [timespan]::FromMilliseconds(((Get-Date)-$StepTimer).TotalMilliseconds),'Finished download and unzip')
ii $DownloadDirectory

Once extracted, I moved the influx extracted subfolder into the InfluxDB folder to keep it clean. Now all the binaries rested in C:\Influx\InfluxDB folder with no nesting folders. I referenced the documentation for getting started with InfluxDB.

Setup Local InfluxDb

Started up the local influxdb binary.

open the influx command line and startup the console server if not currently running

GitHub

Set-Location $PSScriptRoot
$GetExe = get-childitem -Path $PSScriptRoot -Filter *.exe -Recurse | where {$_.Name -match '(influxd.exe)|(influx\.exe)'}
[Environment]::SetEnvironmentVariable('HOME','C:\Influx\')

foreach($i in $GetExe)
{
        if(@(Get-process -name $i.BaseName -ErrorAction SilentlyContinue).count -eq 0) 
        { start-process -filepath $i.FullName -Verbose }

}

Initializing the new database was simple as documented: create database statty

Also, if you get an error with access to the file, try running as admin.

More Enviromental Variable Fun

A simple fix to errors related to paths and the HOME variable these tools often need, per a Github issue, was to ensure the current path was available as a variable. I did this quickly with a simple batch file to launch the consoles as well as one option, as well as updated the Start-Process script to include a statement to set the env variable for the processes being started. This eliminated the issue. For more details see github issues

SET HOME=%~dp0
start influxd.exe
start influx.exe

An additional snippet for launching the console version via a bat file:

set HOME=C:\influx
cmd /k influx.exe -host "MyInfluxDbHost" -database "statty" -precision "s" -format column

Quick Start for Telegraf

Once you have this running you can take the telegraf binaries and run them on any other server to start capturing some default preset metrics. I launched with the following script and placed this in C:\Influx directory to make it easy to access for future runs.

StartTelegraf

GitHub

Set-Location $PSScriptRoot
$GetExe = get-childitem -Path $PSScriptRoot -Filter *.exe -Recurse | where {$_.Name -match '(telegraf.exe)'} | select -first 1
$Config =  (  get-childitem -Path $PSScriptRoot -Filter *.conf -Recurse | where {$_.Name -match 'telegraf.conf'} | select -first 1).FullName
write-debug "Configuration File Path: $($Config)"
write-debug "Matching exes: `n$($GetExe | format-table -AutoSize | out-string)"
Get-Process -Name telegraf -ErrorAction SilentlyContinue | Stop-Process -Verbose 
foreach($i in $GetExe)
{
        if(@(Get-process -name $i.BaseName -ErrorAction SilentlyContinue).count -eq 0) 
        { 
                set-variable -name WorkingDir -value (split-path -Path $i.FullName -Parent) -Verbose
                [Environment]::SetEnvironmentVariable('TELEGRAF_CONFIG_PATH',$Config)
                start-process -filepath $i.FullName -Verbose 
        }

}

Edit the conf file to add some tags, change default sampling interval and more. I’ll post another article about setting up telegraf to run as a service in the future so search for more info

You can also apply the same bat file in the startup directory such as:

@REM alternative is using variable
@REM set TELEGRAF_CONFIG_PATH=C:\telegraf\telegraf.conf

start %~dp0telegraf.exe -config %~dp0telegraf.conf

Run Chronograf

One these metrics began to run, I ran Chronograf. This is Influx’s alternative to Grafana, another more mature product.

Start Chronograf

GitHub

Set-Location $PSScriptRoot
$GetExe = get-childitem -Path $PSScriptRoot -Filter *.exe -Recurse | where {$_.Name -match '(chronograf.exe)'} | select -first 1

write-debug "Matching exes1`n$($GetExe | format-table -AutoSize | out-string)"

foreach($i in $GetExe)
{
        if(@(Get-process -name $i.BaseName -ErrorAction SilentlyContinue).count -eq 0) 
        { 

                start-process -filepath $i.FullName -Verbose 
        }

}

Upon loading and opening up the instance monitor, I found immediately that I was able to get some metrics from the defaults.

Get Grafana

My preferred visualization tool, this was far more robust and well documented than Chronograf which has promise, but is a relatively new project.

When starting Grafana, you can run the following script. It creates a copy of the default ini to copy for the user to edit if not already there.

StartGrafana

GitHub

Set-Location $PSScriptRoot
$GetExe = get-childitem -Path $PSScriptRoot -Filter *.exe -Recurse | where {$_.Name -match '(grafana\-server.exe)'} | select -first 1
$GetOriginalConfig = (get-childitem -Path $PSScriptRoot -Filter *.ini -Recurse | where {$_.Name -match '(defaults.ini)'} | select -first 1).FullName

$Getcustom = (get-childitem -Path $PSScriptRoot -Filter *.ini -Recurse | where {$_.Name -match '(custom.ini)'} | select -first 1)
write-debug "Matching exes1`n$($GetExe | format-table -AutoSize | out-string)"
if(@($Getcustom).Count -eq 0)
{ 

        (Get-Content -path $GetOriginalConfig -Raw) |  Out-File -FilePath ([io.path]::Combine([io.path]::GetDirectoryName($GetOriginalConfig),'custom.ini')) -NoClobber -Encoding utf8
}
foreach($i in $GetExe)
{
        if(@(Get-process -name $i.BaseName -ErrorAction SilentlyContinue).count -eq 0) 
        { 
                $RootDir = (split-path -path $i.fullname -Parent | Split-Path -Parent)
                [Environment]::SetEnvironmentVariable('HOME', $RootDir )
                start-process -filepath $i.FullName -Verbose -WorkingDirectory  $RootDir 
        }



}

Start-process  http://localhost:3000

Once you open the localhost page, if you don’t see datasources in the left hand drop down, create an organization and ensure you are an admin, you’ll then see the option to add datasources. I simple pointed the page to InfluxDB console running on the server I had setup previously.

summary

This is just a quick guide on getting started as I found a lot of little bumps in the road since the projects are written in GO and not an easily run .NET project. Getting through this will hopefully give you a way to get started. I’ll blog a bit more soon on visualization of the metrics captured, some custom annotations to help make metrics come alive with real-time event notifications (like “load test started” and “build ended” etc). It’s a really promising solution for those who want some really nice flexibility in using perfmon and related metrics to visualize Windows and SQL Server performance.