Inspiration help: Monitoring SQL server and sending the data to Azure monitor

Telegraf
, ,
1

Hi all,

I am new to the InfluxData/Telegraf world, and what I’m trying to do is use a Telegraf Docker setup to monitor a SQL Server installation running on a VM—either in Azure or Azure Stack/Local.

I have a running Docker container on Rocky Linux.

The Docker container is running, it connects to one of our SQL Servers, and it is successfully sending metrics to Azure Monitor.

Some of the data is useful when it ends up as metrics, but most of it doesn’t make much sense in Azure Metrics.

It might be due to my limited experience with Telegraf, but what I’d like to achieve is a setup where I can create a dashboard showing the state and performance of the SQL Server, as well as the state and performance of the individual databases.
We build dashboards in SquaredUp, which allows us to query almost anything in Azure, so it doesn’t matter too much whether the data ends up in Metrics or Log Analytics—but personally, I would prefer Log Analytics.

My Telegraf config file currently looks like this:
(Of course, I have anonymized it.)

[agent]
interval = “10s” # Collect every 10s; Azure Monitor output will aggregate into 1m buckets
round_interval = true
flush_interval = “15s”
flush_jitter = “0s”
metric_batch_size = 1000
metric_buffer_limit = 20000
precision = “1s”
omit_hostname = false
debug = true
quiet = false

#========================================================================

INPUTS

#========================================================================

[[inputs.sqlserver]]
servers = [“Server=1.2.3.4;User Id=telegraf;Password=pw;app name=telegraf;”]
database_type = “SQLServer”

query_timeout = “30s”
#========================================================================

OUTPUTS

#========================================================================

[[outputs.azure_monitor]]
timeout = “20s”
region = “westeurope”
resource_id = “/subscriptions/xxx/resourceGroups/rg-xxxx-vm-p-we/providers/Microsoft.Compute/virtualMachines/xxx”
namespace_prefix = “Telegraf/”

strings_as_dimensions = false

If I change strings_as_dimensions to true, I get a lot of errors about the length of the input.

So—does anyone have suggestions for how I can make the data more useful in Azure Monitor and SquaredUp?
Or any good ideas for an alternative approach to reach the same goal?

Reagards

Jan Dam

2

This is the error i get in the log, when i set strings_as_dimensions = true

E! [agent] Error writing to outputs.azure_monitor: failed to write batch: [400] 400 Bad Request: {“error”:{“code”:“BadRequest”,“message”:“The field Items in DimValues must be a string with a maximum length of 256.\r\n”}}

Is there a way i can see what metric it is there is to long?

3

@JanLD, you could add a file output plugin to your config so it would output all the metrics to a local file as well as Azure. This would allow you to inspect the actual metrics being sent.