MSSQL by ODBC Template: cannot extract value from json

[JeremyK]

I know this is an old thread discussed here ->> https://www.zabbix.com/forum/zabbix-...alue-from-json but I am experiencing the same issue and unable to resolve the problem. (This link takes me to something different https://support.zabbix.com/projects/...sues/ZBX-18554)


I did post on the old thread but got no traction before starting a new one.

Zabbix Server 6.2 on Ubuntu 20,04
Zabbix Agent 2 (On WIndows 2016)
Microsoft SQL 2014 configured as a named instance
Template MSSQL by ODBC
Microsoft ODBC Driver version 18

Host Macros
{$MSSQL.DSN}
{$MSSQL.INSTANCE}
{$MSSQL.PASSWORD}
{$MSSQL.USER}

System DSN
# [DSN name]
[MSSQLTest]
Driver = ODBC Driver 18 for SQL Server
# Server = [protocol:]server[,port]
Server = tcp:10.1.0.50\\cps,1433 ### double \ is required
TrustServerCertificate = yes
#
# Note:
# Port isn't a valid keyword in the odbc.ini file
# for the Microsoft ODBC driver on Linux or macOS
#

Error from zabbix_server.log
206855:20220902:102604.351 error reason for "DevSQL:mssql.batch_requests_sec.rate" changed: Preprocessing failed for: [{"object_name":"CPS","counter_name":"Uptime","i nst ance_name":"","cntr_value":"232962"},{"object_...
1. Failed: cannot extract value from json by path "$[?(@.object_name=='CPS:SQL Statistics' && @.counter_name=='Batch Requests/sec')].cntr_value.first()": no data matches the specified path

Connection is fine its just the parsing that fails. The log file shows the object_name as the MSSql instance name (being CPS) and instance_name as ""


Please help maggix me a solution.

[JeremyK]

I found the answer.

{$MSSQL.INSTANCE} macro must be MSSQL$instnacename.

It actually says this in the description of the macro. I should read more closely.