MSSQL by ODBC - cannot extract value from json

[piotrl]

Hi everyone,

I had install Zabbix 5.0.1 and wanted to use Template DB MySQL by ODBC (downloaded from here https://git.zabbix.com/projects/ZBX/.../db/mssql_odbc). Odbc is configured on zabbix server and it is able to connect to it. Discovery rules are not showing any errors. I've got problems with items created by them:


This is error message:

[LIST][*]cannot extract value from json by path "$[?(@.object_name=='SQLServer:Databases' && @.counter_name=='Active Transactions' && @.instance_name=='MYDATABASENAME')].cntr_value.first()": cannot parse as a valid JSON object: invalid object format, expected opening character '{' or '[' at: '$[?(@.object_name=='SQLServer:Databases' && @.counter_name=='Active Transactions' && @.instance_name=='MYDATABASENAME')].cntr_value.first()'[/LIST]

Nothing beside macros (username, password, dsn) was changed in template. Anyone has an idea why it is not working?

[jbukowskipl]

Hallo, Guy's.. What's is wrong? I have latest Zabbix instance Zabbix 5.0.1 and i try collect MSSQL 2017 stats using this official ODBC MS SQL template.
Soo.. I have the same problem like piotrrl. ODBC connection is stable, template correctly discover databases but have problem with datacollect with jsonpath
Maybe this template is not ready for use with instance name? Any Idea? Regards

[ryujigoku]

Good morning, I would like to know how you made the jsonpath of the version, because I see that you could make it work and that it is not working for me. I throw the same value : Failed: cannot extract value from json by path "$[?(@.object_name=='' && @.counter_name=='Version')].instance_name.first()": cannot parse as a valid JSON object: invalid object format, expected opening character '{' or '[' at: '1'
From already thank you very much

[jbukowskipl]

I finally solved my problem. But I'm not sure if I did it correctly ..
I modified the base template and changed in it:
object_name == 'SQLServer to my instance: object_name ==' MSSQL $MYINSTANCE:

[piotrl]

jbukowskipl,

I solved my problem based on your solution. I added new macro to the template {$MSSQL.INSTANCE} with default value SQLServer for no named SQL instances. And now if I have named instance I just have to overwrite this macro value on host level with my instance name. Of course I had to change JSON path like you did but in my case it looks like this object_name =={$MSSQL.INSTANCE}

[SzymonUnion]

Hello,

colleagues from Poland I suppose May I ask how did you manage to work with this template? I have Zabbix 5.0.2, ODBC is installed, I can access DB via isql -v or sqlcmd, but I cannot run the simplest discovery (db.odbc.discovery) with parameters. I defined $MSSQL.USER, $MSSQL.PASSWORD, $MSSQL.DSN, but it it still not working.

Thanks in advance for any answer regarding my issue as I am quite new with Zabbix.

PS. My problem is described here

EDIT - solved - capital letters in SQL query helped Very strange, but works fine!

Regards,
Szymon

[sysadm.es]

jbukowskipl,

I solved my problem based on your solution. I added new macro to the template {$MSSQL.INSTANCE} with default value SQLServer for no named SQL instances. And now if I have named instance I just have to overwrite this macro value on host level with my instance name. Of course I had to change JSON path like you did but in my case it looks like this object_name =={$MSSQL.INSTANCE}

Greetings.

Could you explain your workaround more detailed ? I found with the same issue and after hours of debugging can't find where comes the error.

If you can upload your xml also would be great.

Thanks

[sysadm.es]

I found where the problem and a workaround

explained here:

https://support.zabbix.com/projects/...sues/ZBX-18554

[JeremyK]

I know this is an old thread but I am experiencing the same issue. (This link takes me to something different https://support.zabbix.com/projects/...sues/ZBX-18554)
I thought to post in this thread 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","inst 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 magix me a solution.

[clearsky.vn]

I know this is an old thread but I am experiencing the same issue. (This link takes me to something different https://support.zabbix.com/projects/...sues/ZBX-18554)
I thought to post in this thread 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","inst 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 magix me a solution.

try this:
with DB1 is your sql instance name.

[Sarang-D]

Hello jbukowskipl piotrl sysadm.es Need your help. I have Azure managed MSSQL instance. Following your suggestions and solution described here https://support.zabbix.com/projects/...sues/ZBX-18554, I'm able to retrieve the instance specific metrics & databases are being discovered in Zabbix. However, discovered databases are not showing any metrics. It has been still facing below error Preprocessing failed cannot extract value from json by path(As shown in screenshot). Can you please help me with suggestion/solution, What could be the reason behind discovered databases not showing any metrics and How to get those metrics?

one more thing, JSON return by parent item MSSQL: Get performance counters, itself doesn't have database level details/metrics.

Error: Preprocessing failed for: [{"object_name":"MSSQL$C63F88CEFE8B","counter_na me" :"Uptime","instance_name":"","cntr_value":"634. ..
1. Failed: cannot extract value from json by path "$[?(@.object_name=='MSSQL$C63F88CEFE8B Databases' && @.counter_name=='Active Transactions' && @.instance_name=='distribution')].cntr_value.first()": no data matches the specified path

[cyber]

one more thing, JSON return by parent item MSSQL: Get performance counters, itself doesn't have database level details/metrics.

Here's your problem...

I hope you do understand, what are "performance counters" and why you do not have them in managed MSSQL instance.

[Sarang-D]

Here's your problem...

I hope you do understand, what are "performance counters" and why you do not have them in managed MSSQL instance.

Hello cyber Do you mean Azure MSSQL managed instance not exposing those database metrics or user don’t have required permissions to read the metrics from the tables?

[cyber]

You answer shows, that you do not understand, what is "performance counter" and where it originates...

Instead of those mssql templates you should look over the Azure templates.. https://www.zabbix.com/integrations/azure#azure_http