Centos 7.8 full updated
Zabbix server 5.0.4
Using ODBC Microsoft ODBC 17

History content of the item

MSSQL: Get performance counters output.txt

Tested the same situation on the following environments:

SQL Server 2014, 2016, 2017, 2019 Standard versions.

Tested almost on Zabbix versions: ** 5.0.4 & 5.2.0rc2. all got the same error:

With the macro {$MSSQL.INSTANCE} as value MSSQL$MSSQLSERVER

Preprocessing failed for: [

{"object_name":"MSSQL$MSSQLSERVER","counter_name":"Uptime","instance_name":"","cntr_value":"115"... 1. Failed: cannot extract value from json by path "$[?(@.object_name=='MSSQL$MSSQLSERVER:General Statistics' && @.counter_name=='Logouts/sec')].cntr_value.first()": no data matches the specified path  

Or without,
{$MSSQL.INSTANCE} as value MSSQLSERVER

Preprocessing failed for: [{"object_name":"MSSQLSERVER","counter_name":"Uptime","instance_name":"","cntr_value":"355"},{"ob...
1. Failed: cannot extract value from json by path "$[?(@.object_name=='MSSQLSERVER:Buffer Manager' && @.counter_name=='Free list stalls/sec')].cntr_value.first()": no data matches the specified path

Okey.

I found wheres the problem.

When you executing the postprocessing is coded as this:
preprocessing:
            -
              type: JSONPATH
              parameters:
                - '$[?(@.object_name==''{$MSSQL.INSTANCE}:SQL Statistics'' && @.counter_name==''Auto-Param Attempts/sec'')].cntr_value.first()'
The mainly problem is {$MSSQL.INSTANCE} is not a dynamic value, it's seems is an static value, in this case SQLServer. If your instance name is MSSQLServer this create an error in order to regex the query.

The workaround i do was create an static value macro

 
-
          macro: '{$MSSQL.PREPROC}'
          value: SQLServer
          description: 'This value is static on most SQLSERVERIONS used for preprocessiong the main query.'
 

And replacing all values $MSSQL.INSTANCE for MSSQL.PREPROC

After this the value MSSQL.INSTANCE is free to named as instance names says.

template_db_mssql_odbc.yaml

This is the yaml i used on 5.2, after this all items will start functioning

as

I'm actually not seeing any difference in which macro you use - both should abide by the same behavior. Did you configure {$MSSQL.INSTANCE} macro value so it points towards your SQL instance on template or host level?

Could you open up the Macros section on your SQL Host, select "Inherited and host macros" tab and show us the values of {$MSSQL.INSTANCE} and {$MSSQL.PREPROC} ?

Hi @Artur.

I was testing with docker. I don't have the environment. Altough the problem is related with the macro $MSSQL.INSTANCE, this value is supposed to name equal to your MSSQLserver instance, but trully must be a fixed value named as "SQLServer",

If your macro $MSSQL.INSTANCE is equal to your real MSSQLServer instance, the error appears:

Preprocessing failed for: [{"object_name":"MSSQL$ISNTANCENAME","counter_na me":"Uptime","instance_name":"","cntr_value":".. .
1. Failed: cannot extract value from json by path "$[?(@.object_name=='MSSQL$ISNTANCENAME:SQL Statistics' && @.counter_name=='Batch Requests/sec')].cntr_value.first()": no data matches the specified path

If your macro $MSSQL.INSTANCE is named as SQLServer ( even if this is not your MSSQLServer instance name ) will work as expected.

Just to confirm: Did you set your {$MSSQL.INSTANCE} macro value correctly? E.g. - MSSQL$NameOfMyInstance?

Could you give us an example of what MSSQL instance name was used for your MSSQL environment?

Regards,
Arturs.

I got some error my instance name like this (DBDEV115\DBDEV115).
I got instance name from query 

select @@servername

Hello everyone, nothing comes of it, I have exactly the same error, I use Zabbix 5.0 and the standard MSSQL ODBC template, send screenshots of what you have written in macros or correct me what is wrong with me I'm a newbie!
while in lates data it will define sql and databases

Let's try two things, first:

On your MSSQL host, open the item: MSSQL: Get performance counters
Change the History storage interval on this item to 1d
Once that is done, navigate to Host - Items - checkbox the item and click "execute now"
This will force the item collect the performance counter data.
Afterwards navigate to Monitoring - Latest data and search for the "MSSQL: Get performance counters" item
Click on "History" and please provide the gathered data.

Meanwhile, you could also try the template provided by Victor (attached in one of the previous comments) - this template uses a static value for the regular expression part of the dependent item and the ODBC part of the master item. Let's see if that works for you.

Confirmed

I just made research and there actually no real issues are presented.

In case if MSSQL is installed with the default configuration and this is the only instance on the server, the name for such instance will be "SQLServer". This is default value for {$MSSQL.INSTANCE} macros and it have corresponding description.

I will change a README for this template to make it more explicit that in case of default installation you do not need to change {$MSSQL.INSTANCE} macro.

Available in:

• 5.0.18rc1 3851d47ab49
• 5.4.7rc1  8a184aaee8a
• 6.0.0alpha5 (master) bb8b12aa175

I have this JSON data, just a few related hashes:

[
{"object_name":"MSSQL$HRM02_B:Access Methods","counter_name":"AU cleanup batches/sec","instance_name":"","cntr_value":"36"},
{"object_name":"MSSQL$HRM02_B:Access Methods","counter_name":"AU cleanups/sec","instance_name":"","cntr_value":"10"},
{"object_name":"MSSQL$HRM02_B:Access Methods","counter_name":"By-reference Lob Create Count","instance_name":"","cntr_value":"0"},

.... ~2K of the custom instance counters ....
... and then some like these, one per example:

{"object_name":"SQL Server 2016 XTP Cursors","counter_name":"Cursor deletes/sec","instance_name":"HRM02_B","cntr_value":"0"},
{"object_name":"SQL Server 2016 XTP Garbage Collection","counter_name":"Dusty corner scan retries/sec (GC-issued)","instance_name":"HRM02_B","cntr_value":"0"},
{"object_name":"SQL Server 2016 XTP IO Governor","counter_name":"Insufficient Credits Waits/sec","instance_name":"HRM02_B","cntr_value":"0"},
{"object_name":"SQL Server 2016 XTP Phantom Processor","counter_name":"Dusty corner scan retries/sec (Phantom-issued)","instance_name":"HRM02_B","cntr_value":"0"},

and then these:

{"object_name":"SQLServer","counter_name":"Uptime","instance_name":"","cntr_value":"1354539"},
{"object_name":"SQLServer","counter_name":"Version","instance_name":"Microsoft SQL Server 2016 (SP3-CU1-GDR) (KB5021128) - 13.0.7024.30 (X64) \n\tDec 24 2022 20:17:51 \n\tCopyright (c) Microsoft Corporation\n\tEnterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)\n","cntr_value":"0"},
{"object_name":"SQLServer:Databases","counter_name":"State","instance_name":"cachee_20210826","cntr_value":"0"},
{"object_name":"SQLServer:Databases","counter_name":"State","instance_name":"cachee_machineControl_20210826","cntr_value":"0"},
{"object_name":"SQLServer:Databases","counter_name":"State","instance_name":"Dba_Support","cntr_value":"0"},
{"object_name":"SQLServer:Databases","counter_name":"State","instance_name":"HRM81","cntr_value":"0"},
{"object_name":"SQLServer:Databases","counter_name":"State","instance_name":"master","cntr_value":"0"},
{"object_name":"SQLServer:Databases","counter_name":"State","instance_name":"model","cntr_value":"0"},
{"object_name":"SQLServer:Databases","counter_name":"State","instance_name":"msdb","cntr_value":"0"},
{"object_name":"SQLServer:Databases","counter_name":"State","instance_name":"tempdb","cntr_value":"0"},
{"object_name":"SQLServer:Databases","counter_name":"State","instance_name":"Workflow87","cntr_value":"0"},
{"object_name":"SQLServer:Databases","counter_name":"State","instance_name":"Workflow87WFM","cntr_value":"0"},
{"object_name":"SQLServer:Databases","counter_name":"State","instance_name":"Workforce110","cntr_value":"0"}]

Note - Uptime and Version are here.
They stopped to work after I specified proper instance name, because in preprocessing they look for an instance name:

$[?(@.object_name=='{$MSSQL.INSTANCE}' && @.counter_name=='Version')].instance_name.first()

Are we sure that for such 2 counters always use named instance ?

Reopening to not lose this question.

If we split the "perfcounters" query into parts and pay attention to this query block:

SELECT object_name,
  counter_name,
  instance_name,
  cntr_value
FROM sys.dm_os_performance_counters

Then we look at the collected data from the user, we will see that in this case an unnamed instance (default) was used. Because this part of the query returns data without any additional transformations.
This can be seen by value of "object_name" field from the data:

{
    "object_name": "SQLServer:Access Methods",
    "counter_name": "AU cleanup batches/sec",
    "instance_name": "",
    "cntr_value": "0"
}

In the case of a named instance, this would be the value like this:

{
    "object_name": "MSSQL$INSTANCE_NAME:Access Methods",
    "counter_name": "AU cleanup batches/sec",
    "instance_name": "",
    "cntr_value": "0"
}

According to the user's comments, he filled the "{$MSSQL.INSTANCE}" macro with the instance name, but if we look at the macro description in this commit we can see the description "The instance name for the default instance is SQLServer. For named instance set the macro value as MSSQL$instance name." This means that in this case the macro should be filled as "SQLServer".

In addition, if check the instance name using the "select @@servicename" construct, the server will return the value "MSSQLSERVER" for an unnamed (default) instance and "MSSQL$INSTANCE_NAME" for a named instance. However, it is worth considering that for an unnamed (default) instance, the output of the "perfcounter" query in the "object_name" field will contain not the instance name, but "SQLServer". That is why the description of the macro specifies "The instance name for the default instance is SQLServer". In addition, if the instance name was checked using the `select @@servername` construct, the result will be the server name, but not the instance name.

If we look at the comment, we will see that the user specified the macro value as "MSSQL$MSSQLSERVER" and "MSSQLSERVER", which means that the instance name was checked via the "select @@servicename" construct, which I wrote about above.

It is also worth noting that some of the field names in the "perfcounter" query are generated based on the value of the "{$MSSQL.INSTANCE}" macro.

UNION SELECT '{$MSSQL.INSTANCE}' 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

And if we look at the comment again, we will see that with different values ​​of the macro we get a different value of "object_name":

{"object_name":"MSSQL$MSSQLSERVER","counter_name":"Uptime",...}

{"object_name":"MSSQLSERVER","counter_name":"Uptime",...}

This may be misleading as the instance was named, however based on the attached data we see that the instance was unnamed (default).

This task is not relevant currently.

Starting with commits 5b3b8b13339 and 972c8f2efae, the "{$MSSQL.INSTANCE}" macro was removed from the template. Queries were refactored to work independent of the instance name, as well as the instance type: named or unnamed (default). This fix is ​​available in versions 6.0 and 7.0.