[#ZBX-18115] Template DB MSSQL by ODBC: Most dependent items fail with: Preprocessing failed

[ZBX-18115] Template DB MSSQL by ODBC: Most dependent items fail with: Preprocessing failed Created: 2020 Jul 20 Updated: 2024 Apr 10 Resolved: 2020 Jul 28
Status:	Closed
Project:	ZABBIX BUGS AND ISSUES
Component/s:	Templates (T)
Affects Version/s:	5.0.2
Fix Version/s:	5.0.3rc1, 5.2.0alpha1, 5.2 (plan)

Type:

Problem report

Priority:

Trivial

Reporter:

Marco Hofmann

Assignee:

Maxim Chudinov (Inactive)

Resolution:

Fixed

Votes:

Labels:

odbc, template

Remaining Estimate:

Not Specified

Time Spent:

Not Specified

Original Estimate:

Not Specified

Environment:

Debian amd64 10 Buster
MariaDB 10.4
Zabbix Server 5.0.2
Microsoft SQL Server 2016 13.0.5820.21

Attachments:

axsql2016-01.png

image-2020-07-20-16-08-47-741.png

image-2020-07-20-19-30-45-810.png

image-2020-07-20-19-32-20-311.png

odbc1.png

sql2016-01.png

Team:

Team INT

Sprint:

Sprint 66 (Jul 2020)

Story Points:

Description

Steps to reproduce:

Import latest Version of Template Template DB MSSQL by ODBC from:
https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/mssql_odbc
Link Template with a Windows Host, that has SQL Server 2016 installed.
Create a SQL server with appropriate permissions
Create the corresponding macros:
{$MSSQL.DSN}
{$MSSQL.USER}
{$MSSQL.PASSWORD}
{$MSSQL.PORT}
Wait for results and watch item status

Result:
See screenshot...
Example errors from several failed dependent items:

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

Expected:
Working items.

Comments

Comment by Marco Hofmann [ 2020 Jul 20 ]

I did a manual test of the raw master item, and noticed that the returned JSON is far from complete, as it's cut at some length or size. Maybe that's part of the issue.

Comment by Marco Hofmann [ 2020 Jul 20 ]

I did more tests. The first test, pictured above, is from a big test and dev MS SQL Server from ours. When I execute the SQL Query from the Master Item, I receive a whopping 3419 rows!

I did a second test on a much smaller SQL Server, and there the query returns 1967 rows. Still the JSON return value is capped and incomplete.

I think there might not be an issue with the template itself, but with the size of the value a db.odbc.get may return.

If I'm on the right track, that would mean the following:

Zabbix db.odbc.get should be able to handle much larger return values
Or the Template must be able to handle the limit and split its one big RAW item into many much smaller raw items.

I hope this all makes sense and I'm on the right track. But it might also be, that the initial Preprocessing failed error has a completly different reason.

Comment by Alexey Pustovalov [ 2020 Jul 20 ]

Hello!

May I ask you to attach both queries output to the issue. If you worry about private information, feel free to send it to [email protected].

Comment by Marco Hofmann [ 2020 Jul 21 ]

Hi. I did a "Save results as.." CSV and send it to [email protected]. Hope that's what you need.

Comment by Alexey Pustovalov [ 2020 Jul 21 ]

Marco,

Just one additional question: I see MSSQL$AX and MSSQL$ZENTRAL are differ with SQLServer, did you specify it manually?

Comment by Marco Hofmann [ 2020 Jul 21 ]

I don't understand the question? These are the results of two total different SQL Server Hosts. Two VMs and two Hosts in Zabbix.
They both share the same Zabbix Proxy with the msodbcsql17 driver.
If that's not the answer to your question, may I kindly ask you for an easier wording?

Comment by Alexey Pustovalov [ 2020 Jul 21 ]

I see object name contains object name + prefix (MSSQL$AX or MSSQL$ZENTRAL). Where from is it? Host name?

Comment by Marco Hofmann [ 2020 Jul 21 ]

I'm sorry. Now I get your question.
Both SQL Servers I used for testing are Multi-Instance SQL Servers. From each server I choose one instance for testing:
Hostname: axsql2016-01 has three instances:

Hostname: sql2016-01 has two:

I choose the instance, by setting the IP:Port in the /etc/odbc.ini
So I guess the name is generated by MSSQL itself, not by me, because of the Multi Instance. It states MSSQL$INSTANCE I guess.

Comment by Alexey Pustovalov [ 2020 Jul 21 ]

Thank you for the explanation!

Comment by Maxim Chudinov (Inactive) [ 2020 Jul 28 ]

Available in:

5.0.3rc1 3f3f69d8f29
5.2.0alpha1 6c32136ce25

Comment by Marco Hofmann [ 2020 Jul 30 ]

I've tested the new Template from the Git Repo. Seems to work now. I can't find errors, yet. Thank you!

But I've a question: Now that I can define the SQL Instance, how can I monitor the four instances I have on a single VM, as I can only define one macro? Do I have to create four Hosts in Zabbix, each targeting a different SQL Instance via a Macro?

Comment by Alexey Pustovalov [ 2020 Jul 30 ]

Marco,

It is better to use different host, so in this case host is just application

Comment by Maxim Chudinov (Inactive) [ 2020 Jul 31 ]

Hi starko.
To monitor via ODBC each SQL instance on one VM firstly you must define TCP port in SQL Server Configuration Manager for each instance. Then create a host in Zabbix with the corresponding macro value. Our approach is one service(instance) - one host. Thank you for help with testing.

Comment by Jesús Chávez Bastías [ 2023 Jun 07 ]

Same problem with metrics in ODBC MSSQL Template, SQL Query shows correctly the results but pre-procesing is wrong. No data is showed for any instances.


	MSSQL: Get Buffer Manager counters
	MSSQL: Get Cache counters
	MSSQL: Get DB counters
	MSSQL: Get General Statistics counters
	MSSQL: Get job status
	MSSQL: Get last backup
	MSSQL: Get Latches counters
	MSSQL: Get Locks counters
	MSSQL: Get Memory counters
	MSSQL: Get performance counters
	MSSQL: Get SQL Errors counters
	MSSQL: Get SQL Statistics counters

Generated at Tue Apr 29 10:02:21 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.

[ZBX-18115] Template DB MSSQL by ODBC: Most dependent items fail with: Preprocessing failed Created: 2020 Jul 20 Updated: 2024 Apr 10 Resolved: 2020 Jul 28