MSSQL by ODBC -Few things are Not supported

[bnerusil]

Hi
I would like to kindly ask for your help,

I am struggling with monitoring these three things via MSSQL by ODBC template:

1. MSSQL: Get Buffer Manager counters: MSSQL: Buffer cache hit ratio – I received: status not supported - info - Preprocessing failed for: [{"object_name":"SQLServer:Buffer Manager","cntr_value":"935","counter_name":"Backgr ound writer p...
1. Failed: cannot extract value from json by path "$[?(@.counter_name=='BufferCacheHitRatio')].cntr_value.first()": no data matches the specified path

2.MSSQL: Get Cache counters: MSSQL: Cache hit ratio - I received: status not supported - info - Preprocessing failed for: [{"object_name":"SQLServer:Plan Cache","cntr_value":"1596","counter_name":"Cache Hit Ratio","inst...
1. Failed: cannot extract value from json by path "$[?(@.counter_name=='CacheHitRatio')].cntr_value.first()": no data matches the specified path

3. MSSQL: Get Access Methods counters: MSSQL: Worktables from cache ratio - status not supported - info - Preprocessing failed for: [{"object_name":"SQLServer:Access Methods","cntr_value":"0","counter_name":"AU cleanup batches/se...
1. Failed: cannot extract value from json by path "$[?(@.counter_name=='WorktablesFromCacheRatio')].cntr_value.first()": no data matches the specified path

All three performances are visible in Windows - via Performance monitor



Other things are correctly enabled and working, I followed the steps in manual. - https://www.zabbix.com/integrations/mssql#mssql_odbc

Info:
ZABBIX - Zabbix 6.4.12
template

I would like to monitor - MSSQL 2019
odbc.ini correctly setup (I tried driver FreeTDS., ODBC Driver 18 for SQL Server)

[interpeix]

I'm in the same situation, using Zabbix Agent 2 and MSSQL 2022.
Executing the query manually on the server:
SELECT object_name,
counter_name,
instance_name,
cntr_value
FROM sys.dm_os_performance_counters
UNION SELECT 'MSSQL$' + @@servicename AS object_name,
'Version' AS counter_name,
@@version AS instance_name,
0 AS cntr_value
UNION SELECT 'MSSQL$' + @@servicename AS object_name,
'Uptime' AS counter_name,
'' AS instance_name,
DATEDIFF(second, sqlserver_start_time, GETDATE()) AS cntr_value
FROM sys.dm_os_sys_info
UNION SELECT 'MSSQL$' + @@servicename + 'atabases' AS object_name,
'State' AS counter_name,
name AS instance_name,
state AS cntr_value
FROM sys.databases
UNION SELECT a.object_name,
'BufferCacheHitRatio' AS counter_name,
'' AS instance_name,
cast(a.cntr_value * ISNULL((100.0 / NULLIF(b.cntr_value,0)),0) AS dec(3, 0)) AS cntr_value
FROM sys.dm_os_performance_counters a
JOIN (
SELECT cntr_value,
OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'MSSQL$' + @@servicename + ':Buffer Manager'
) b
ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'MSSQL$' + @@servicename + ':Buffer Manager'
UNION SELECT a.object_name,
'WorktablesFromCacheRatio' AS counter_name,
'' AS instance_name,
cast(a.cntr_value * ISNULL((100.0 / NULLIF(b.cntr_value,0)),0) AS dec(3, 0)) AS cntr_value
FROM sys.dm_os_performance_counters a
JOIN (
SELECT cntr_value,
OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Worktables From Cache Base'
AND OBJECT_NAME = 'MSSQL$' + @@servicename + ':Access Methods'
) b
ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Worktables From Cache Ratio'
AND a.OBJECT_NAME = 'MSSQL$' + @@servicename + ':Access Methods'
UNION SELECT a.object_name,
'CacheHitRatio' AS counter_name,
'_Total' AS instance_name,
cast(a.cntr_value * ISNULL((100.0 / NULLIF(b.cntr_value,0)),0) AS dec(3, 0)) AS cntr_value
FROM sys.dm_os_performance_counters a
JOIN (
SELECT cntr_value,
OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Cache Hit Ratio base'
AND OBJECT_NAME = 'MSSQL$' + @@servicename + ':Plan Cache'
AND instance_name = '_Total'
) b
ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Cache Hit Ratio'
AND a.OBJECT_NAME = 'MSSQL$' + @@servicename + ':Plan Cache'
AND instance_name = '_Total'

It returns the metrics for those not supported items.

[interpeix]

I've solved modifying the template, the problem was at the preprocessing step that is looking for CacheHitRatio, but in the json's response the correct text was "Cache Hit Ratio". The same happens with the rest of the wrong items, pay attention because is case sensitive.
Once that issue have been solved, now the problem is to figure out how to show the value that we want, because de Cache Hit Ratio is not a %, neither the Buffer cache hit ratio

[anslu]

I've solved modifying the template, the problem was at the preprocessing step that is looking for CacheHitRatio, but in the json's response the correct text was "Cache Hit Ratio". The same happens with the rest of the wrong items, pay attention because is case sensitive.
Once that issue have been solved, now the problem is to figure out how to show the value that we want, because de Cache Hit Ratio is not a %, neither the Buffer cache hit ratio

Thank you!

Just to mention, in my case it was:
$[?(@.counter_name=='Cache Hit Ratio')].cntr_value.first()
$[?(@.counter_name=='Worktables From Cache Ratio')].cntr_value.first()
and
$[?(@.counter_name=='Buffer cache hit ratio')].cntr_value.first() - First capital, then small