perf monitor with MSSQL

[nabberuk]

I'm having an issue with using the performance monitor on Windows. I've imported the template thats available on the wiki and one of the keys i was hoping to use just says "Not support by the zabbix agent". The key i'm talking about is the following one.

perf_counter["\SQLServer:General Statistics\User Connections"]

Do i need to enter anything on the agent side or should this be working?

[tchjts1]

No need to enter anything on your agent side.

Probably something about your particular MSSQL install that is different for that key.

Here's what you need to do. Log onto your MSSQL box (Or have an Admin do it) and pop a DOS command box and use this command:
typeperf -qx > perfcounters.txt

That will generate a file called perfcounters.txt that you can then search through for the key that you need. You are probably going to find that there is just some slight difference in the key that you have right now.

[aib]

BTW, sometimes not all keys from that list are working.
They can exist in the list, they can work in Performance Monitor.
But when you try to get it from zabbix_agent - you are getting error message only: ZBX_NOTSUPPORTED.

I have that issue with PhysicalDisk item.

[root@zabbix ~]# zabbix_get -s 192.168.100.115 -k perf_counter["\PhysicalDisk(_Total)\Avg. Disk Queue Length"]
0.000000
[root@zabbix ~]# zabbix_get -s 192.168.100.115 -k perf_counter["\PhysicalDisk(2 C:)\Avg. Disk Queue Length"]
ZBX_NOTSUPPORTED

Yes, it's looking weird, but my C: drive is a partition on third physical drive.

[tchjts1]

perf_counter["\SQLServer:General Statistics\User Connections"]

perf_counter["\SQLServer:General Statistics\User Connections"]

Just as an FYI, I also do some fairly heavy MSSQL monitoring, and that is one of the metrics I monitor. My key is exactly the same as the one you show and it works for me. I would still do the typeperf command and see if it differs from your key.

[nabberuk]

Just looking through the perfmon which gave me the following (edited the instance name). Do i need to enter all of it?

\MSSQL$SQLINSTANCE:General Statistics\User Connections

[nabberuk]

That seems to have sorted it, i'm using Server 2012 R2 so i wonder if it's a change on this version. Is there a way so i wouldn't have to enter the \MSSQL$SQLINSTANCE: bit on each item else it can be pretty time consuming.

[tchjts1]

I believe it depends on how the MSSQL install was done. Whoever installed it had the option of leaving the name of the SQL install as the default of "SQLServer", or changing the name to whatever they wanted it to. Looks like they chose the latter in your case.

If you are finding that the majority of the MSSQL items in your template use that new naming convention, then the way I would change it is this:

Create a new template called something like Template App MSSQL 2012. Go to your existing MSSQL template and find the items that need to be changed. put a checkmark by each item and then copy those items into the new template you created. Now delete the items that you had checkmarked in your old template.

Export the NEW template which will put it on your local machine in an XML format. Use an editor (I prefer Notepad++ for this) and do a search and replace on all instances of \SQLServer: and replace it with \MSSQL$SQLINSTANCE: or whatever your real common denominator is.

Import that new template back into Zabbix and assign it back to your MSSQL 2012 boxes. So basically you will have 2 MSSQL templates assigned to your Sql servers. One will cover any items with the standard naming convention and the other will cover the items that use the modified naming convention.

Just be aware that if you have a bunch of MSSQL servers, your DBA's could have used any number of SQL naming on your various servers. If that's the case... it will simply be a PITA and you'll have to bite the bullet and change them as needed.

Disclaimer - Do me a favor and test this on a DEV MSSQL box to make sure you are getting the results you want before making the changes to PROD.