InfluxDB - retrieving a table from SQL Server (returns only the last row)

[pak_vit]

Hi, I am fairly new to InfluxDB and Telegraph.

I’m having trouble configuring the “SQL” plugin in Telegraf.

I have a query:

SELECT CustomerID as ID, FirstName, LastName, GETDATE() AS Timestamp FROM [AdventureWorksLT].[SalesLT].[Customer];

Which returns 847 rows:

The Telegraph agent retrieves them all:

I would like to retrieve all of them and display them in a table in Grafana (via InfluxDB). Even though Telegraf fetches all these rows, only the last one is shown to me in InfluxDB

Does anyone know what could be the cause of this?

[pak_vit]

And this is a screenshot from the Telegraf agent:

[Hipska]

You probably didn’t have any field configured as a tag don’t you? I would for example have the customer ID as a tag, that way InfluxDB is able to tell them apart.

Also this doesn’t look like time series data, or are you going to add some metrics(data) to that query (like number or amount of sales)?

[pak_vit]

Hey, thanks for the reply.

Our main idea is to monitor space consumption in data + log files in SQL Server + other MSSQL settings. Currently we have over 140 instances under control and Telegraf does not contain everything we need. This is the origin of the need to call custom queries like this one:

(This is just an example):

Can you give me a hint on how to add the “tag” correctly? I searched for a long time for the answer and it’s hard to find something…

[Hipska]

Maybe have a look at the documentation? You need to put the name of the columns you want as tags in the tag_columns_include array.

Also, if you are trying to monitor the SQL Server, why not using the sqlserver input? It might already have all the metrics you need…

[pak_vit]

Thank you, I will look at the links provided.

We use SQL Server plugin and it works very well. Unfortunately, we often need some additional information that this plugin does not collect.

UPDATE:

Thank you @Hipska!!!

Everything is now working as it should.

All I had to do was add:

tag_columns_include = [“*”]

… exactly as you wrote.

Once again, thank you very much!

[Hipska]

No no, I did not write that. I said to only add the columns that you want as a tag. You really don’t want FreeSpaceMB and similar as a tag, they are field data. You will run into cardinality and other issues if you do it like that.