Unable to monitor MSS SQL Server

[Joerg80]

Hi

i have a fresh installation of Zabbix 7 on a Ubuntu 24 and tried to monitor several SQL Servers (Domain Members and also standalone systems) but it always failed with the same error message:

 24820:20240709:072600.605 item "myServerName.mydomain:mssql.cache_hit_ratio" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.cache_object_counts" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.cache_objects_in_use" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.cache_pages" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.data_files_size" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.log_files_size" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.log_files_used_size" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.transactions_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.errors_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.info_errors_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.kill_connection_errors_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.offline_errors_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.user_errors_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.forwarded_records_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.full_scans_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.index_searches_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.page_splits_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.table_lock_escalations.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.workfiles_created_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.worktables_created_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.worktables_from_cache_ratio" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.granted_workspace_memory" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.maximum_workspace_memory" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.memory_grants_outstanding" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.memory_grants_pending" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.target_server_memory" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.total_server_memory" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.logins_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.logouts_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.processes_blocked" became not supported: Unsupported item key.
 24820:20240709:072600.605 item "myServerName.mydomain:mssql.user_connections" became not supported: Unsupported item key.
 24820:20240709:072600.611 item "myServerName.mydomain:mssql.batch_requests_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072600.611 item "myServerName.mydomain:mssql.readahead_pages_sec.rate" became not supported: Unsupported item key.
 24820:20240709:072603.979 error reason for "myServerName.mydomain:mssql.average_latch_wait_time" changed: Cannot evaluate function: item "/myServerName.mydomain/mssql.average_latch_wait_time_raw" is not supported at "last(//mssql.average_latch_wait_time_raw) - last(//mssql.average_latch_wait_time_raw,#2)) /
(last(//mssql.average_latch_wait_time_base) - last(//mssql.average_latch_wait_time_base,#2) +
(last(//mssql.average_latch_wait_time_base) - last(//mssql.average_latch_wait_time_base,#2)=0))".
 24820:20240709:072603.979 error reason for "myServerName.mydomain:mssql.average_wait_time" changed: Cannot evaluate function: item "/myServerName.mydomain/mssql.average_wait_time_raw" is not supported at "last(//mssql.average_wait_time_raw) - last(//mssql.average_wait_time_raw,#2)) /
(last(//mssql.average_wait_time_base) - last(//mssql.average_wait_time_base,#2) +
(last(//mssql.average_wait_time_base) - last(//mssql.average_wait_time_base,#2)=0))".
 24820:20240709:072603.979 error reason for "myServerName.mydomain:mssql.percent_of_adhoc_queries" changed: Cannot evaluate function: item "/myServerName.mydomain/mssql.sql_compilations_sec.rate" is not supported at "last(//mssql.sql_compilations_sec.rate) * 100 /
(last(//mssql.batch_requests_sec.rate) + (last(//mssql.batch_requests_sec.rate)=0))".
 24820:20240709:072603.979 error reason for "myServerName.mydomain:mssql.percent_recompilations_to_compilations" changed: Cannot evaluate function: item "/myServerName.mydomain/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))".
 24820:20240709:072603.979 error reason for "myServerName.mydomain:mssql.scan_to_search" changed: Cannot evaluate function: item "/myServerName.mydomain/mssql.full_scans_sec.rate" is not supported at "last(//mssql.full_scans_sec.rate) / (last(//mssql.index_searches_sec.rate) + (last(//mssql.index_searches_sec.rate)=0))".

With ODBC:

24820:20240708:151200.307 item "myServerName.mydomain:mssql.page_lookups_sec.rate" became not supported: Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]
 24820:20240708:151200.307 item "myServerName.mydomain:mssql.page_writes_sec.rate" became not supported: Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]
 24820:20240708:151200.307 item "myServerName.mydomain:mssql.readahead_pages_sec.rate" became not supported: Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]
 24820:20240708:151200.307 item "myServerName.mydomain:mssql.target_pages" became not supported: Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]
 24820:20240708:151200.307 item "myServerName.mydomain:mssql.cache_hit_ratio" became not supported: Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]
 24820:20240708:151200.307 item "myServerName.mydomain:mssql.cache_object_counts" became not supported: Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]
 24820:20240708:151200.307 item "myServerName.mydomain:mssql.cache_objects_in_use" became not supported: Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]
 24820:20240708:151200.307 item "myServerName.mydomain:mssql.cache_pages" became not supported: Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]
 24820:20240708:151200.307 item "myServerName.mydomain:mssql.data_files_size" became not supported: Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found and no default driver specified]
​

For the setup i have used the manual of the zabbix site: https://www.zabbix.com/integrations/mssql

SQL Server(s) where i have test it: MS SQL Server 2016 - 2022 (Standard + Enterprise Editions), SQL Express 2019 on Windows Server 2016 - 2022

• The Agent Communication is working fine, the standard OS related information like CPU Load, Disk usage ... working without of any problem.
• Connection by telnet from the zabbix host is working (telnet servname 1433), so there is no connection issue
• Firewall Rules on the SQL Server is configured (Any Port, Any Protocoll from & to zabbix server = allowed)

I have check already the forum and several other sites but can not find any hint or solution for the error, maybe some one have an idea how to fix ist.

Greetings
Joerg

[vijayk]

Which version of ZabbixAgent installed in SQL Server?

You must install plugins if you are using MSSQL by ZabbixAgent2 template and ODBC driver in server if you are running with MSSQL by ODBC template.

[Joerg80]

Ah ok, that wasn't shown in the manual.

I only have had installed "Zabbex Agent", now is "Zabbex Agent 2" in Version 7.

Where i can find the plugins? In older Screenshot of the Agent installer or some plugins listet, but in the v7 only "Agent daemon", "Zabbix sender" and "Zabbix get" als addtional option listed.

The "new" error message from the "MSSQL by Zabbix agent 2: Get database: Database discovery" is now "Unknown metric mssql.job.status.get", i think that is also a result of the missing plugin?

I have found this: https://git.zabbix.com/projects/AP/repos/mssql/browse is the that the correct "nyssql.conf" file that have to be place into the folder "C:\Program Files\Zabbix Agent 2\zabbix_agent2.d\plugins.d"? I have tried it, but it seems not to be working :c

[Joerg80]

ok, tried some suggestions here from the forum, incl installation of the plugins of an <7 version, it doesn't work.

We will not monitor the SQL servers by zabbix for now. But thx for your assist.

[vijayk]

https://cdn.zabbix.com/zabbix/binaries/stable/7.0/7.0.0/

You can find the plugin from above link

[Joerg80]

some great magic, i have do no futher investigation and it runs now ... i dont know why :c.

It will be nice, when the "manual" of monitoring a SQL server give that kind of information (agent 2 + plugins requiered) that the monitoring of a SQL server on windows need that (incl. the setup of the .conf file).

For anyone who runs in the same trouble (links from vijayk post)

• agent 2: zabbix_agent2-7.0.0-windows-amd64-openssl.msi
• agent 2 Plugins: zabbix_agent2_plugins-7.0.0-windows-amd64.msi

need to be installed

Edit the config File in the installation directory of the agent
e.g.: "C:\Program Files\Zabbix Agent 2\zabbix_agent2.d" -> mssql.conf

Important settings for a single Instance Server:

• Plugins.MSSQL.Default.Uri=sqlserver://hostname.fqdn:1433 (or sqlserver://localhost:1433 when no remote connection is allowed)
• Plugins.MSSQL.Default.User=username
• Plugins.MSSQL.Default.Password=password *1

*1: as special chars use only "-" or "_" but than more letters (e.g. 30-50) for the password, i have run in various trouble with $ or ! or " or ' so we changed the password of the monitoring user to a long but less complex password

For the user setup on the SQL server use the setup-information site of the plugin: https://git.zabbix.com/projects/AP/repos/mssql/browse (Header: Microsoft SQL Server requirements) and not that one from the main(?) site.

Anything is testet on Zabbix 7 with Ubuntu 24