Error in inputs.sql.query getting metrics from sqlserver - One database multiple instances -

InfluxDB 2
1

Good morning,
I’m new in telegaf world and I have a problem when I need to obtain metrics from one database multiple instances in a windows server.
I obtain metrics form the sqlserver correctly, but when I want to obtain a specific metric with one query, the log shows an error in which it tells that I’m configuring wrong the query.
The config file to obtain the specific metric is:
[[inputs.sql.query]]
query = “”"
USE ‘NEUTRA602’ GO
SELECT
CAST(CAST(SUM(CAST(FILEPROPERTY(name, ‘SpaceUsed’)/128.0 AS DECIMAL(38,2)))*100 AS DECIMAL(38,2)) / CAST(SUM([maxsize]/128.0) AS DECIMAL(38,2)) AS DECIMAL(38,2)) AS Used_Percent
FROM sysfiles where filename not like ‘%log%’ and CAST([maxsize]/128.0 AS DECIMAL(10,2)) > 0
“”"
measurement = “sqlserver_datafile_Neutra602”

The error log file:
2020-05-30T07:24:03Z I! Sintaxis incorrecta cerca de ‘NEUTRA602’.
2020-05-30T07:24:03Z W! Error performing query 'USE 'NEUTRA602’GO
SELECT
CAST(CAST(SUM(CAST(FILEPROPERTY(name, ‘SpaceUsed’)/128.0 AS DECIMAL(38,2)))*100 AS DECIMAL(38,2)) / CAST(SUM([maxsize]/128.0) AS DECIMAL(38,2)) AS DECIMAL(38,2)) AS Used_Percent
FROM sysfiles where filename not like ‘%log%’ and CAST([maxsize]/128.0 AS DECIMAL(10,2)) > 0
'…: mssql: Sintaxis incorrecta cerca de ‘NEUTRA602’.

Could anybody tell me what’s the correct syntax in order to telegraf could read the value correctly? Executing this query in Microsoft SQL Server Management Studio it works properly.

2

Telegraf does not have a plugin called “inputs.sql.query”, is it custom made?

Generally speaking, when writing the Telegraf configuration file you are writing using toml synthax, that’s why you get an error (see toml strings.

In you case wrapping the query sting in single quotes instead of double quotes should work.

query = '''
USE ‘NEUTRA602’ GO
SELECT
CAST(CAST(SUM(CAST(FILEPROPERTY(name, ‘SpaceUsed’)/128.0 AS DECIMAL(38,2)))*100 AS DECIMAL(38,2)) / CAST(SUM([maxsize]/128.0) AS DECIMAL(38,2)) AS DECIMAL(38,2)) AS Used_Percent
FROM sysfiles where filename not like ‘%log%’ and CAST([maxsize]/128.0 AS DECIMAL(10,2)) > 0
'''
3

Good morning,
The tags are configured as in another config file where I’ve made another custom queries. I’ve attached you another query that it works properly.
I’ve changes the syntax as you’ve told me, but the error in the log is the same.

This is the config file configuration:

[[inputs.sqlserver]]
servers = [ “Server=127.0.0.1;User Id=XXX;Password=XXXXXX;app name=telegraf;log=1;encrypt=disable”, ]
query_version = 2

[inputs.sqlserver.tags]
host = “navisionmb…sqlserver__default”

Custom queries

[[inputs.sql]]
driver = “sqlserver”
[inputs.sql.tags]
host = “navisionmb…sqlserver__default”

[inputs.sql.source]
dsn = “Server=127.0.0.1;User Id=XXXX;Password=XXXX;app name=telegraf;log=1;encrypt=disable”

[[inputs.sql.query]]
query = ‘’’
USE ‘NEUTRA602’ GO
SELECT
CAST(CAST(SUM(CAST(FILEPROPERTY(name, ‘SpaceUsed’)/128.0 AS DECIMAL(38,2)))*100 AS DECIMAL(38,2)) / CAST(SUM([maxsize]/128.0) AS DECIMAL(38,2)) AS DECIMAL(38,2)) AS Used_Percent
FROM sysfiles where filename not like ‘%log%’ and CAST([maxsize]/128.0 AS DECIMAL(10,2)) > 0
‘’’
measurement = “sqlserver_datafile_Neutra602”

[[inputs.sql.query]]
query = “”"
SELECT ISNULL(COUNT( * ), 0) as BlockCounter
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
AND r.blocking_session_id > 0
AND r.total_elapsed_time / (1000.0) > 300
“”"
measurement = “sqlserver_locks_5_minutes”

Would you please, tell me what would be wrong?

Kind regards

4

Is it allowed to use the clause USE? It says:

  • Incorrect syntax near ‘NEUTR602’
  • Error perform query 'USE 'NEUTRA602

The log is showing this:
2020-06-02T10:26:03Z I! Sintaxis incorrecta cerca de ‘NEUTRA602’.
2020-06-02T
10:26:03Z W! Error performing query 'USE ‘NEUTRA602’ GO
SELECT
CAST(CAST(SUM(CAST(FILEPROPERTY(name, ‘SpaceUsed’)/128.0 AS DECIMAL(38,2)))*100 AS DECIMAL(38,2)) / CAST(SUM([maxsize]/128.0) AS DECIMAL(38,2)) AS DECIMAL(38,2)) AS Used_Percent
FROM sysfiles where filename not like ‘%log%’ and CAST([maxsize]/128.0 AS DECIMAL(10,2)) > 0
'…: mssql: Sintaxis incorrecta cerca de ‘NEUTRA602’

5

I simply don’t know.
Since this is not a standard/official telegraf plugin I have no clue about what it does and doesn’t allow.

a pair of possible solutions:

  • remove GO and replace it with ; (semicolon), in fact GO is not part of the TSQL language, it is an utility supported by few MS tools (docs here).
  • specify the database in the connection string, which to me looks like a standard ADO connection string
6

Hello,

Thanks a lot for your indications. I’ve managed to get de values.

Kind regard

7

Which was the solution?

8

Hello,

I’ve changed the query as you told me.
Deleting the word ‘go’ and putting ‘;’ behind the name of the instance.
query = ‘’’
USE ‘NEUTRA602’;
SELECTCAST(CAST(SUM(CAST(FILEPROPERTY(name, ‘SpaceUsed’)/128.0 AS DECIMAL(38,2)))*100 AS DECIMAL(38,2)) / CAST(SUM([maxsize]/128.0) AS DECIMAL(38,2)) AS DECIMAL(38,2)) AS Used_PercentFROM sysfiles where filename not like ‘%log%’ and CAST([maxsize]/128.0 AS DECIMAL(10,2)) > 0
‘’’

The query like this, it works properly.

Regards,

Mónica Tato