(Or any Telegraf config file you need to be honest... )
I like using telegraf to collect metrics, that's no secret. I've blogged the end to end installation of it on Windows for SQL server purposes here. Or if you'd like to see if I talk as much rubbish as I type, presented a session on it here.
As mentioned, both of those occurrences in relation to setting it up on Windows in conjuction with InfluxDB as a data repository, and Grafana as the presentation layer.
What i'm focusing on here is purely the Telegraf side of things, again on Windows as I mainly use this for monitoring SQL Server on Windows. Again, this isn't a huge blog post, just a lot of script excepts included for explainations.
Files can be found at: sqlrodbloke/telegrafinstall (github.com)
Telegraf
Telegraf is a single EXE file, which is the collector...and a configuration file dictating what is to be collected. The configuration file in turn states where to log the metrics (the outputs) that have been collected from the specified products that you wish to collect (the inputs).
There's nothing else to it, even the default installation is easy, simples, done. OK, goodnight then...
Large, non uniform Estates
However there are many of us who have large estates that we wish to monitor, and the makeup of this file may prove to be annoying if you have differing configuration requirements across that estate?
...and you want one install routine to rule them all right? #FreeSauron
Lets take SQL server as the use case here, as this is an issue i've tackled directly.
The Input section for SQL Server in the telegraf configuration file could be like this.
[[inputs.sqlserver]]
servers = [
"Server=Sqlserver1;Database=master;Integrated Security=SSPI;log=1;",
]
database_type = "SQLServer"
include_query = []
exclude_query = ["SQLServerRequests"]Great, told you this was easy...
But look again, it has the sqlserver name in there (Sqlserver1), what about Sqlserver2, 3, 4 up to as many SQLServers that you have. Yes, you could substitute the name with localhost , but what about all of those servers with named instances on? And multiple named instances on those servers, which follow maniacal naming conventions due to third party vendors?
So you can either... construct a telegraf.conf file manually for each server you wish to deploy to (nay) ... or have an automated routine that does the heavy lifting for you (yay).
The Yays have it, now Template it.
One way of tackling this is by having one or more template files, containing placeholders. The idea being we just:
Suck up this templated file.
Do some work to generate the strings we actually want.
Replace the placeholders.
Spit out a shiny new taylored config file.
Copy what we need to the target server, installing the service with our customised file.
Template files
Lets start with our template file, i'll call it sqlserver_telegraf_template.conf.
In here we'll set a handful of placeholders of things that we will be changing through our install routines.
Yes, the telegraf.conf file really can be this simple, 99% of the data in there by default is just explaining what the settings are.
[agent]
interval = "<_collectioninterval_>s"
round_interval = true
metric_batch_size = 25000
metric_buffer_limit = 100000
collection_jitter = "5s"
flush_interval = "10s"
flush_jitter = "5s"
precision = ""
hostname = ""
omit_hostname = false
[[inputs.sqlserver]]
servers = [
<_ServersPlaceHolders_>
]
database_type = "SQLServer"
include_query = []
exclude_query = ["SQLServerRequests"]Hold My beer... I mean place.
You can see i've added 2 placeholders.
<_collectioninterval_> - to allow an easy set of how often we want to pull data
<_ServersPlaceHolders_> - the segment where our connection strings for the Sqlserver inputs will be.
I'm just doing basic substitution here, what if you want selectible input sections? There is nothing stopping you building the separate input template config mini-files into your main config file. Power!
Guess what? Powershell time.
You knew it was coming didn't you? A wee bit of powershell and we can have this task dancing to our tune in no time.
Prerequisities
There are are couple of things you will need for this to work.
Administrative access to the target server (sorry, uses Admin shares)
Powershell remoting enabled.
Preparation
On your client / management machine....
First, amend sqlserver_telegraf_template.conf to include your target influxdb output URL token, organization and bucket. Mine supplied on this post won't work for you. Also, set /add any other telegraf configuration that you want.
Copy all 3 files to a folder of your choosing, i'm just using C:\temp\telegraf
install-telegraf.ps1
sqlserver_telegraf_template.conf
telegraf.exe
Telegraf.exe you will have already, if not, grab it from Influxdata, the other 2 files can be found at: sqlrodbloke/telegrafinstall (github.com) .
Installation Examples.
At its simplest form, to install on Sqlserver1 (this is the SERVER name) . On your client/management machine, open a PS command prompt (as administrator) to the folder above.... and run install-telegraf.ps1 with params as needed.
PS C:\temp\telegraf> .\install-telegraf.ps1 Sqlserver1To use a different collection interval, ie.. not 60s .
.\install-telegraf.ps1 -Server Sqlserver1 -interval 120To force a new copy of telegraf.exe to copy (ie.. to upgrade telegraf.exe on existing servers.
.\install-telegraf.ps1 Sqlserver1 -upgradetelegrafexeAnd we even get some pretty coloured output.
Done. Sweary Ramsey style.
This example is only dealing with a single instance, but point it at a server with multiple instances, and all will be discovered, collected, and added in the correct format to the config file. Honest, it really does.
Logging
A Log folder will automatically be created under where the PS file is running from, in this case C:\temp\telegraf\Log. This will contain a transcript log file of all output. See my previous post on transcripts if you'd like to learn more on those.
Target server installation
Looking on the target server, we now see the files... under C:\Program Files\Telegraf. Note, that the default name is now used sqlserver_telegraf.conf, as this is no longer a template.
...and the service. Hooray!
If you want to have look in the sqlserver_telegraf file on the server also, you will see the placeholders have been substituted with our required config values. Hooray again!
You can stop reading now if you're only into the doings, and not the howings.
Code, code, code!
The code is attached in its entirety, but as always, I like to step through the various stages, so you can see what it is doing, and more importantly...understand so you can improve it as you see fit for your own environments.
Function - Set-TelegrafConfig
I put the majority of the code into a function, just for ease of use and portability.
Basically, the main script passes in the computername, the path of the template config file that you want to use, and a serviceaccount to use, this is set to NT AUTHORITY\SYSTEM if not supplied.
Param ([string]$computername,
[String]$templateConfig,
[String]$SQLServAcc)
We then get the folder path of the supplied config, and substitute the name of the server into the file name.
$OutputConf=(Split-Path $templateConfig)+"\sqlserver_telegraf_$servername.conf"
Next, we remote connect to the targetted server, and read the registry to find out what instances are installed, saving this in ($SQLinstances).
$Confblock=$null
Write-Host "Obtaining list of SQL Instances from $computername" -ForegroundColor Cyan
$SQLInstances = Invoke-Command -ComputerName $ComputerName {
(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
}If instances are found, then we loop through these instances, removing the default instance place name if needed. We then connect to the SQL Server, and grant the pemissions required to the Telegraf service account we are going to use. Minimal here, if you start collecting SQL Agent Job info, then the additional permissions would need to be added here also. Finally, we build a dynamic connection string ($confline) , and add this to ($confblock). Meaning ($confblock) is built up as more instances are found on the server.
foreach ($sql in $SQLInstances) {
$InstanceName = "$computername\$sql".Replace('\MSSQLSERVER','')
Write-Host "Adding Telegraf SQL Permissions to $InstanceName" -ForegroundColor Cyan
Invoke-Sqlcmd -ServerInstance $InstanceName -Database master -Query "GRANT VIEW SERVER STATE TO $SQLServAcc; GRANT VIEW ANY DEFINITION TO $SQLServAcc;" -ConnectionTimeout 15 -QueryTimeout 15
$InstanceName = $InstanceName.Replace('\','\\')
$Confline='"'+"Server=$InstanceName;Database=master;Integrated Security=SSPI;log=1;"+'",'
$Confblock+="$Confline`n"
} Great, we're starting to build up our dynamic config.
We then:
Read the text contents of our template config file into a variable ($Change).
Replace <_ServersPlaceHolders_> with our dynamic string in ($Confblock).
Replace <_collectioninterval_> with our ($Interval) variable..
Set the ($Outputconf) file with our newly built config file text held in ($Change)
Write-Host "SQL Connection String Block:" -ForegroundColor Magenta
Write-Host $Confblock -ForegroundColor Magenta
$Change = Get-Content $templateConfig
$Change=$Change | ForEach-Object {$_ -Replace "<_ServersPlaceHolders_>","$ConfBlock" }
$Change=$Change | ForEach-Object {$_ -Replace "<_collectioninterval_>","$interval" }
$Change | Set-Content $OutputConf Test that the file exists, and then return this from the function. Boom, sorted.
So that's ultimately how we create the custom config. So what about the main part of the script, as we haven't actually installed anything yet.
So, the running of the install-telegraf ps file itself.
Set some options, only server is manditory, all others optional, see examples earlier in this post for usage.
param([String]$Server,[int]$interval,[String]$SourceFolder,[switch]$upgradetelegrafexe)The first block of code is just a basic usage response.
Then we set some defaults, namely:
($interval) for telegraf collections to 60 (s) if not specified.
location of the template files folder ($Sourcefolder) to be the same as the .ps1 file if not supplied.
If (-not $interval){
$interval=60
}
try {
$scriptpath = Split-Path $script:MyInvocation.MyCommand.Path
If (-not $SourceFolder){
$SourceFolder=$scriptpath}
Then create a Logs Folder under ($Sourcefolder). This is just used for transcript output.
$Logpath="$scriptpath\Logs"
$LogName="InstallTelegraf"
If(-not (test-path -PathType container $Logpath))
{ New-Item -ItemType Directory -Path $Logpath}We start a transcript that this point to capture all output to a file. Again, I love the transcript command, although I find many a hardered powershell veteran unaware of it.
We then supply the name of our template file to $SourceFile, and then create the ($TemplateConfig) full path from our source folder and file strings. We pass this to the function above.
Start-Transcript -Path "$Logpath\$LogName-$($Server.Replace('\','_'))-$(Get-Date -format dd-MM-yyyy-hhmm).log" -IncludeInvocationHeader
$SourceFile='sqlserver_telegraf_Template.conf'
$templateConfig=Join-Path $SourceFolder $SourceFileNow, we look for the Telegraf service on the target server.
No Service
If not found, we:
Run a remote command to create the C:\Program Files\Telegraf folder on the target server.
Call our Set-TelegrafConfig function above to generate our custom config file.
Move this newly created file to our target server folder, renaming it to sqlserver_telegraf.conf
Invoke another remote command to run the telegraf service creation command that we already know and love.
$service = Get-Service -Computername $server -Name telegraf -ErrorAction SilentlyContinue
if($null -eq $service)
{
Write-Host "Looking for Telegraf Service....NOT FOUND."
Write-Host "Creating folder $TargetFolder on $server"
Invoke-Command -ComputerName $server -ScriptBlock { New-Item -ItemType Directory -Force -Path "C:\Program Files\Telegraf"}
Write-Host "Copying required files to Server $server"
$OutputConffile=Set-TelegrafConfig -Computername $server -TemplateConfig $templateConfig
Write-Host $OutputConffile -ForegroundColor Magenta
Move-Item $OutputConffile -Destination "\\$server\C$\Program Files\telegraf\sqlserver_telegraf.conf" -Force
Copy-Item $SourceFolder\telegraf.exe -Destination "\\$server\c$\Program Files\telegraf\" -Force
Invoke-Command -ComputerName $server -ScriptBlock {& "C:\Program Files\Telegraf\telegraf.exe" --service install --config "C:\Program Files\Telegraf\sqlserver_telegraf.conf"}
}But the Service Exists already
So the above was if the telegraf service does not exist on the server already (ie.. fresh install).... if it does exist, we do different things. This is when you may want to send out a new config file as you've added/changed basic inputs, or replace the telegraf.exe with a new version etc.
Existing service detected means we:
Stop the Telegraf service
Generate and Move the config file
And if ($upgradetelegrafexe) is set, also copy the Telegraf.exe file in the source folder you're using.
else {
Write-Host "Telegraf Service already exists, replacing config file with new version."
Write-Host "Stopping Telegraf Service." -ForegroundColor Cyan
Invoke-Command -ComputerName $server -ScriptBlock {& "C:\Program Files\Telegraf\telegraf.exe" --service stop}
Write-Host "Copying Telegraf files to Target Server $server" -ForegroundColor Cyan
$OutputConffile=Set-TelegrafConfig -Computername $server -TemplateConfig $templateConfig -pluginconfig $pluginconfig
Move-Item $OutputConffile -Destination "\\$server\C$\Program Files\telegraf\sqlserver_telegraf.conf" -Force
If ($upgradetelegrafexe){
Write-Host "...also copying new Telegraf.exe file as requested" -ForegroundColor Yellow
Copy-Item "$SourceFolder\telegraf.exe" -Destination "\\$server\C$\Program Files\telegraf\telegraf.exe" -Force
}We're done, so start the Telegraf service, and stop the transcript so that the log file is closed correctly.
Write-Host "Starting Telegraf Service." -ForegroundColor Cyan
Invoke-Command -ComputerName $server -ScriptBlock {& "C:\Program Files\Telegraf\telegraf.exe" --service start}
Write-Host "Telegraf installation/config Complete." -ForegroundColor Green
}
catch {
$exception = $_.Exception.Message
write-host "Failed Telegraf Installation: $Server - $exception" -ForegroundColor Red
}
finally {Stop-Transcript
}And that's your lot... we now have Telegraf installed with a custom config file, all in a matter of a couple of seconds.
As always, thanks for reading and I hope this makes your monitoring journey that little bit easier.
Roddot-dash-dot.
Full Code.... sqlrodbloke/telegrafinstall (github.com)
param([String]$Server,[int]$interval,[String]$SourceFolder,[switch]$upgradetelegrafexe)
function Set-TelegrafConfig {
<#
.DESCRIPTION
Generates the required Telegraf config for a target server from a template file
Also grants required permissions for Telegraf service account used.
Will default to permission [NT AUTHORITY\SYSTEM] if not specified.
Returns the Path of the config file generated
.NOTES
Usage: Set-TelegrafConfig -Computername SQLServer1 -TemplateConfig='D:\GIT\DBA-Telegraf\Files\sqlserver_telegraf_Template.conf' -$SQLServAcc '[NT AUTHORITY\SYSTEM]'
#>
Param ([string]$computername,
[String]$templateConfig,
[String]$SQLServAcc
)
try {
If (-not $SQLServAcc){$SQLServAcc="[NT AUTHORITY\SYSTEM]"}
$OutputConf=(Split-Path $templateConfig)+"\sqlserver_telegraf_$servername.conf"
$Confblock=$null
Write-Host "Obtaining list of SQL Instances from $computername" -ForegroundColor Cyan
$SQLInstances = Invoke-Command -ComputerName $ComputerName {
(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
}
If ($SQLInstances){
foreach ($sql in $SQLInstances) {
$InstanceName = "$computername\$sql".Replace('\MSSQLSERVER','')
Write-Host "Adding Telegraf SQL Permissions to $InstanceName" -ForegroundColor Cyan
Invoke-Sqlcmd -ServerInstance $InstanceName -Database master -Query "GRANT VIEW SERVER STATE TO $SQLServAcc; GRANT VIEW ANY DEFINITION TO $SQLServAcc;" -ConnectionTimeout 15 -QueryTimeout 15
$InstanceName = $InstanceName.Replace('\','\\')
$Confline='"'+"Server=$InstanceName;Database=master;Integrated Security=SSPI;log=1;"+'",'
$Confblock+="$Confline`n"
}
Write-Host "SQL Connection String Block:" -ForegroundColor Magenta
Write-Host $Confblock -ForegroundColor Magenta
$Change = Get-Content $templateConfig
$Change=$Change | ForEach-Object {$_ -Replace "<_ServersPlaceHolders_>","$ConfBlock" }
$Change=$Change | ForEach-Object {$_ -Replace "<_collectioninterval_>","$interval" }
$Change | Set-Content $OutputConf
}
else {
Write-Host "SQL Instances Not Detected on $Computername " -ForegroundColor Red
}
If (Test-Path -Path $OutputConf -PathType Leaf){
Return $OutputConf
}
}
catch
{
Write-Host "----- Exception -----"
Write-Host $_.Exception
Write-Host $_.Exception.Response.StatusCode
Write-Host $_.Exception.Response.StatusDescription
}
}
## MAIN
If (-not $Server){
Write-Host "Server name must be passed as a parameter" -ForegroundColor Red
Write-Host "Usage:" -ForegroundColor Yellow
Write-Host ".\install-telegraf.ps1 -server {Servername} -Interval (Optional) {Collection interval in seconds -default 60} " -ForegroundColor Green
Write-host "-SourceFolder (optional) {Path to config templates} " -ForegroundColor Green
Write-host "-upgradetelegrafexe (optional switch to copy new telegraf.exe)" -ForegroundColor Green
Return
}
# Default to 60s interval collection if not supplied
If (-not $interval){
$interval=60
}
try {
$scriptpath = Split-Path $script:MyInvocation.MyCommand.Path
If (-not $SourceFolder){
$SourceFolder=$scriptpath
}
$Logpath="$scriptpath\Logs"
$LogName="InstallTelegraf"
If(-not (test-path -PathType container $Logpath))
{
New-Item -ItemType Directory -Path $Logpath
}
Start-Transcript -Path "$Logpath\$LogName-$($Server.Replace('\','_'))-$(Get-Date -format dd-MM-yyyy-hhmm).log" -IncludeInvocationHeader
$SourceFile='sqlserver_telegraf_Template.conf'
$templateConfig=Join-Path $SourceFolder $SourceFile
Clear-Host
Write-Host "Generating Telegraf Config files..." -ForegroundColor Cyan
# Installation / Update
# Check if service present
$service = Get-Service -Computername $server -Name telegraf -ErrorAction SilentlyContinue
if($null -eq $service)
{
Write-Host "Looking for Telegraf Service....NOT FOUND."
Write-Host "Creating folder $TargetFolder on $server"
Invoke-Command -ComputerName $server -ScriptBlock { New-Item -ItemType Directory -Force -Path "C:\Program Files\Telegraf"}
Write-Host "Copying required files to Server $server"
$OutputConffile=Set-TelegrafConfig -Computername $server -TemplateConfig $templateConfig
Write-Host $OutputConffile -ForegroundColor Magenta
Move-Item $OutputConffile -Destination "\\$server\C$\Program Files\telegraf\sqlserver_telegraf.conf" -Force
Copy-Item $SourceFolder\telegraf.exe -Destination "\\$server\c$\Program Files\telegraf\" -Force
Invoke-Command -ComputerName $server -ScriptBlock {& "C:\Program Files\Telegraf\telegraf.exe" --service install --config "C:\Program Files\Telegraf\sqlserver_telegraf.conf"}
} else {
Write-Host "Telegraf Service already exists, replacing config file with new version."
Write-Host "Stopping Telegraf Service." -ForegroundColor Cyan
Invoke-Command -ComputerName $server -ScriptBlock {& "C:\Program Files\Telegraf\telegraf.exe" --service stop}
Write-Host "Copying Telegraf files to Target Server $server" -ForegroundColor Cyan
$OutputConffile=Set-TelegrafConfig -Computername $server -TemplateConfig $templateConfig -pluginconfig $pluginconfig
Move-Item $OutputConffile -Destination "\\$server\C$\Program Files\telegraf\sqlserver_telegraf.conf" -Force
If ($upgradetelegrafexe){
Write-Host "...also copying new Telegraf.exe file as requested" -ForegroundColor Yellow
Copy-Item "$SourceFolder\telegraf.exe" -Destination "\\$server\C$\Program Files\telegraf\telegraf.exe" -Force
}
}
Write-Host "Starting Telegraf Service." -ForegroundColor Cyan
Invoke-Command -ComputerName $server -ScriptBlock {& "C:\Program Files\Telegraf\telegraf.exe" --service start}
Write-Host "Telegraf installation/config Complete." -ForegroundColor Green
}
catch {
$exception = $_.Exception.Message
write-host "Failed Telegraf Installation: $Server - $exception" -ForegroundColor Red
}
finally {
Stop-Transcript
}