MSSQL by ODBC Performance Counters JSON paths wrong

[Clifra Jones]

OK, color me crazy but this all used to work, and now it doesn't. (well, I fixed it, so not it works)
I've used this template for years without issues.
So, this last few weeks I've been building a new 7.0 server. I get my SQL servers discovered on the new server. Add this template and configure all the macros and a bunch if items come up not supported.

Item: Get Buffer Manager counters
JSON Path was something quite strange: $[?(@.object_name=~'.*:Buffer Manager')]
In 6.0 it was: $[?(@.object_name=='{$MSSQL.INSTANCE}:Buffer Manager')]
So I changed it to: $[?(@.object_name=='*:Buffer Manager')] and it worked.

OK so maybe that was a bug introduced in 7.0

Now the really wierd part.
All the dependent items have counter names without spaces. I checked the source for 6.0 and 5.0 and they are the same.
Item: Buffer cache hit ratio
7.0
JSONPath: $[?(@.counter_name=='CacheHitRatio')].cntr_value.first()
6.0
JSONPath: $[?(@.counter_name=='CacheHitRatio')].cntr_value.first()
5.0
JSONPath: $[?(@.counter_name=='CacheHitRatio')].cntr_value.first()

But the actual counter name is "Buffer cache hit ratio". When I changed the counter names to the one with spaces they all started working.

When I was running 5.0 and 6.0 I KNOW these worked. I built Dashboards that graphed out these values. I never changed anything!

So, am I crazy or not?! I cannot find anywhere that these counter names have changed with a SQL server update. I am running 2016, 2019 and 2020 servers.

[Clifra Jones]

More Info...
Looking at the Get cache counters item the JSON path is: $[?(@.object_name=~'.*Plan Cache' && @.instance_name=='_Total')]
So I changed the Get Buffer Manager counters JSON path to $[?(@.object_name=~'.*Buffer Manager')] i.e. removed the colon and it works.

Also items like Buffer cache hit ratio and plan cache hit ratio used to return a percentage number from 0-100. Not any more. They return large numbers values.
To get the percentage you need to divide cache hit ratio with cache hit ratio base.
So I added an item to get the cache hit ratio base and then a calculated item for Cache hit ratio percent.
Formula:
(last(//mssql.buffer_cache_hit_ratio) / last(//mssql.buffer_cache_hit_ratio_base)) * 100

I did the same for plan cache hit ratio

I can only conclude that Microsoft changed things! (That does not surprise me). As so much of this template JUST WORKED in the past.

I will be working in detail with this template in the next week or so and if I find more weirdness I will fix any items not working as expected.
When I believe I have it all working properly I will export the updated template and post a link to download it.

[Clifra Jones]

Here is the modified template. This all is working for us in our environment.
I hope it also works for whoever uses it.

https://github.com/Clifra-Jones/MyZabbixTemplated/blob/main/Zabbix%207.0/MSSQLbyODBC_Modified.yaml