☰

性能计数器 zabbix、MSSQL 服务器预处理失败

tag-icon • tag-icon sql-server monitoring zabbix zabbix-agent visualization
性能计数器 zabbix、MSSQL 服务器预处理失败

我已通过 ODBC 将我的 MSSQL 服务器连接到 zabbix。

我收到了某些项目的结果，但对于其他项目却返回了错误：

Preprocessing failed for: [{"object_name":"SQLServer:Buffer Manager","cntr_value":"4947","counter_name":"Background writer ...
1. Failed: cannot extract value from json by path "$[?(@.counter_name=='BufferCacheHitRatio')].cntr_value.first()": no data matches the specified path

显示错误的图像1：https://i.stack.imgur.com/ZhrNL.png”

对于这 3 个指标“[相关指标][2][2]: https://i.stack.imgur.com/stMjX.png”

又名： CacheHitRatio, WorktablesFromCacheRatio and BufferCacheHitRatio，正在与该项目一起提取：MSSQL: Get performance counters 该项目使用此 SQL 请求来提取性能计数器指标：

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 + ':Databases' 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'

我不知道哪里出了问题，请注意，当我使用 ODBC 和 isql 登录到我的用户并尝试获取其中一个计数器指标（例如命中率）时，我成功了。 isql 的结果

所以问题肯定不是来自用户。我还在 zabbix 配置文件中启用了 ODBC 的轮询器并将其设置为 5，然后重新启动了 zabbix。我不知道问题可能是什么，任何帮助都将不胜感激。zabbix 的项目中使用的 json 预处理步骤是“$[?(@.counter_name=='BufferCacheHitRatio')].cntr_value.first()"

相关内容