Microsoft SQL Server 2019 monitoring problem

[riuzaver]

Hi! I've problems with monitoring my Microsft Sql Server 2019. I'm using zabbix 5.4.2 and the newest MSSQL ODBC (https://git.zabbix.com/projects/ZBX/.../db/mssql_odbc).
Some of elements work well like jobs, version and status detection, database discovering and etc. But the most part of them doesn't work. The main error of element looks like this:

Preprocessing failed for: [{"object_name":"MSSQL$PSSQLSRV:Access Methods","counter_name":"AU cleanup batches/sec","instance...
1. Failed: cannot extract value from json by path "$[?(@.object_name=='PSSQLSRV:Buffer Manager' && @.counter_name=='Page lookups/sec')].cntr_value.first()": no data matches the specified path

Also have seen this error:

Cannot evaluate function: item "/PSSQLSRV2/mssql.sql_recompilations_sec.rate" is not supported at "last(//mssql.sql_recompilations_sec.rate) * 100 /
(last(//mssql.sql_compilations_sec.rate) + (last(//mssql.sql_compilations_sec.rate)=0))".

here's my odbc.ini:

[MSSQL01]
Driver = ODBC Driver 17 for SQL Server
Server = 10.28.36.15
User = zabbix
Password = *private*
Port = 1433
Database = msdb

My sql instance name is PSSQLSRV
Microsoft SQL Server ip is 10.28.36.15
Username for connect is zabbix.

here's the example of things that works fine while some of them are not:

[AndreaZ]

Hello riuzaver,
I need your help.
Have you ever got it to work?
I have a similar problem monitoring sql 2014.
Installed freetds and microsoft odbc 17 driver, created two different dsn entries in odbc.ini and can succesfully connect to both datasources with isql.
In Zabbix i configured the following macros:

But I only get 16 values in Latest data when searching for Name "mssql"

And in item configuration I get also Warnings for the most of them:


Any ideas?

[bagahe]

Did someone manage to get Microsoft SQL 2019 working? I´m using Zabbix 5.4 and the most updated template found here: https://git.zabbix.com/projects/ZBX/...at=release/5.4
But I am getting the same errors on the pictures above.

Is there any other way to monitor this?

Thank you.

[cyber]

Those errors here indicate, that there is no such data in that result, what preprocessing expects. If you get back a "master" item, check, that it contains those things, that you want to extract. There can be simple differences sometimes, some field name changes etc...

[bagahe]

Thanks for your reply, cyber.

How do I go about fixing these things? All the dependent items from the template have the same issue.
This is what I get if i try to test the first pre-processing step:


How can I check the JSON values if the test doesnt work? where can I find the parameters names that I should be putting in the zabbix items?

Best Regards

[cyber]

I guess looking into master item and see, if it matches preprocessing requirements, Maybe you have some connection issues and master item has no values, so preprocessing for dependent items fail also...
for testing preprocessing you also need that data, inserted to "value" field..
TBH, I have never used MSSQL DB monitoring and I cannot really help you here.. It would require to have at least somewhat similar setup to look around and test something...