ZenPacks : WMI MSSQL : Comments

RE: WMI MSSQL

community@zenoss.org — Sat, 02 Jan 2010 04:40:07 GMT

I started having a problem with this ZenPack. I has a windows 2003 server that has mssql client but no server installed. Is started getting wmi errors on the system. Uninstalling the ZenPack Fixed. Might be a problem with the dectect code.

Thanks

Robert

Error

zenperfwmi|/Status/Wmi||4|Could not read the WMI value (list index out of range).

RE: WMI MSSQL

community@zenoss.org — Mon, 04 Jan 2010 10:46:59 GMT

I installed the Zenpack without problems on my Zenoss 2.5.1 server, however i don't see any performance graphs. Only the icons.

I configured the zSQLInstance in zProperties for the specified device and Binded the template but doesn't seem to work.

Can you help me?

Thanks

Arnaud

RE: WMI MSSQL

community@zenoss.org — Tue, 05 Jan 2010 18:30:06 GMT

I'm getting the same issue. I see SQL events but no perf graphs. Doesn't look like the data is being collected?

RE: WMI MSSQL

community@zenoss.org — Tue, 12 Jan 2010 02:49:16 GMT

Has anyone tried this with Windows 2008 and SQL 2008? Are the WMI calls for SQL2008 the same?

RE: WMI MSSQL

community@zenoss.org — Fri, 15 Jan 2010 16:31:26 GMT

Hi, I found a problem in your Zenpack and I think I resolv it.

This is a sample line:

SELECT UserConnections FROM Win32_PerfRawData_MSSQL${dev/zSQLInstance}_MSSQL${dev/zSQLInstance}GeneralStatistics

Your zenpack set zSQLInstance=SQLSERVER, then the command line is look like this:

wmic -U 'DOMIAN\ad-user'%'password' //10.0.0.156 --namespace='root\cimv2' "SELECT LockRequestsPersec,LockTimeoutsPersec,LockWaitTimems,LockWaitsPersec,NumberofDeadLocksPersec FROM Win32_PerfRawData_MSSQLSQLSERVER_MSSQLSQLSERVERLocks WHERE Name='_Total'"

The problem is here:

Win32_PerfRawData_MSSQLSQLSERVER_MSSQLSQLSERVERLocks

The Name is wrong.

The correct is:

Win32_PerfRawData_MSSQLSERVER_SQLServerLocks

So, the solution is simple: Change the zSQLInstance to SERVER and delete the MS in "_MSSQL${dev/zSQLInstance}Locks. Its appears like this: "_SQL${dev/zSQLInstance}"

I hope it was useful

Diorgenes Mello

RE: WMI MSSQL

community@zenoss.org — Fri, 15 Jan 2010 17:47:59 GMT

If someone else can confirm this fix I'll make the change and rev the ZenPack.

RE: WMI MSSQL

community@zenoss.org — Mon, 18 Jan 2010 15:57:14 GMT

I can confirm that he is correct, but I don't know how to edit the ZenPack to test this. I have tested with WMI Tester...

RE: WMI MSSQL

community@zenoss.org — Mon, 18 Jan 2010 19:53:29 GMT

Hi, I don´t no how edit the zenpack directly, but you can try change the template in "Devices" => Server => Windows, go in "Templates" tab, then "WMIPerfMSSQL" . Then you can change the data sources.

Good Luck.

Diorgenes Melo

RE: WMI MSSQL

community@zenoss.org — Mon, 18 Jan 2010 19:56:56 GMT

I've attached a 1.2.1 version with the change that Diorgenes Mello recommended. If someone wants to verify that fixes it, I'll check in the changes and announce the fix.

RE: WMI MSSQL

community@zenoss.org — Tue, 19 Jan 2010 03:05:13 GMT

Strange...When I go into the template to try and edit it I get:

Type: KeyError
Value: 'editWMIDataSource'

Traceback (innermost last):

Module ZPublisher.Publish, line 119, in publish
Module ZPublisher.mapply, line 88, in mapply
Module ZPublisher.Publish, line 42, in call_object
Module Products.ZenModel.ZenModelBase, line 65, in __call__
Module OFS.Traversable, line 301, in restrictedTraverse
Module OFS.Traversable, line 284, in unrestrictedTraverse
__traceback_info__: ([], 'editWMIDataSource')

KeyError: 'editWMIDataSource'

RE: WMI MSSQL

community@zenoss.org — Tue, 19 Jan 2010 03:22:19 GMT

1.2.1 still is not right. Example:

SELECT CacheHitRatio,CacheUseCountsPersec FROM Win32_PerfRawData_MSSQL${dev/zSQLInstance}_MSSQL${dev/zSQLInstance}CacheManager WHERE Name='_Total'

even if zSQLInstance = SERVER it's still not right. The WMI call should be to:

Win32_PerfRawData_MSSQLSERVER_SQLSERVERCacheManager

Unfortunatley, per my earlier post, for some strange reason I cannot edit this... I get the KeyError.

Help, please!

RE: WMI MSSQL

community@zenoss.org — Tue, 19 Jan 2010 04:45:44 GMT

Did you restart Zenoss after installing the ZenPack? Or at least 'zopectl restart'? I upgraded my 1.2 to 1.2.1 and can get to the template. For the change recommended above, should this be

FROM Win32_PerfRawData_MSSQL${dev/zSQLInstance}_SQL${dev/zSQLInstance}CacheManager

RE: WMI MSSQL

community@zenoss.org — Tue, 19 Jan 2010 13:21:35 GMT

I did restart Zenoss. I then tried removing the 1.2.1 ZenPack, restarted Zenoss, re-installed 1.2.1, bounced the zopectl service and tried again. Still get the error trying to edit the Template.

Looking further at it, though, I cannot edit any template. I get the same error. Will have to look at this separately.

To answer your question on the CacheManager call, what I think makes more sense is have it be:

FROM Win32_PerfRawData_MS${dev/zSQLInstance}_${dev/zSQLInstance}CacheManager

and have the user define the zSQLInstance as originally designed: SQLSERVER (instead of SERVER).

Below is the result of WBEMTEST connected to a SQL2008 server. This should help:

RE: WMI MSSQL

community@zenoss.org — Tue, 19 Jan 2010 15:40:19 GMT

I don't have access to a box for testing this, but do the other queries need to be updated as well?

_MS${dev/zSQLInstance}_${dev/zSQLInstance}

It appears that the other queries are all of the same form. What happens if the zSQLInstance isn't "SQLSERVER"? Do the queries all break?

RE: WMI MSSQL

community@zenoss.org — Tue, 19 Jan 2010 18:21:15 GMT

I tought I found the problem, There are no such class "Win32_PerfRawData_MSSQLSERVER_SQLServerCacheManager" in MSSQL 2005.

I have Windows 2003 and 2008 and MSSQL 2000 and 2005. I just found this class in MSSQL 2000.

I´ll work in a new ZenPack for MSSQL 2005.

Just to make sure, take a look on this:

MS SQL Server 2000:

And MS SQL Server 2005:

Thanks

Diorgenes Mello

RE: WMI MSSQL

community@zenoss.org — Tue, 19 Jan 2010 19:00:33 GMT

If we can distinguish which queries work on 2005 and which work on 2000, we could make 2 templates within the ZenPack.

RE: WMI MSSQL

community@zenoss.org — Tue, 19 Jan 2010 19:38:24 GMT

I agree.I´ll woking on it next week, soon I´ll bring some news...

RE: WMI MSSQL

community@zenoss.org — Wed, 20 Jan 2010 09:02:49 GMT

Hello,

thanks for the feedback!

I think you don't use instance on your server that's why!

I've only tested on our production servers (we install MSSQL using instance name).

The best way to make it work would be to use :

SELECT UserConnections FROM Win32_PerfFormattedData_MSSQL${dev/zSQLInstance}_MSSQL${dev/zSQLInstance}GeneralStatistics

if the zSQLInstance is not empty (eg. there is an instance)

And to use :

SELECT UserConnections FROM Win32_PerfFormattedData_MSSQLSQLSERVER_SQLServerGeneralStatistics

If the zSQLInstance is empty (eg. not using instance name)

Using your modification (version 1.2.1) I think it will not work on MSSQL servers installed using instance names.

I've also installed a MSSQL 2005 and 2008 server here, we I have some spare time, I'll make templates for them.

PS : I've also update the source files (and commited them to the SVN rep) to change the request from PerfRAW to PerfFormatted which works better.

Regards,

Richard

RE: WMI MSSQL

community@zenoss.org — Thu, 21 Jan 2010 17:42:45 GMT

Hi, Just now I can make my SQL server 2005 to work on zenoss. All I did was run "wmiadap /f" on the server. But I still missing "Win32_PerfRawData_MSSQLSERVER_SQLServerCacheManager" class in SQL 2005. So the SQLServer_CacheHitRatio is broken.

Any ideas???

RE: WMI MSSQL

community@zenoss.org — Sat, 23 Jan 2010 03:45:52 GMT

OK...I'm almost "there" now. I have most items resolved EXCEPT - the SQL server I am querying is 64-bit! The counters are stored as CIM_uint64. The ZenPack does not seem to like that. I am not returning any values.

Anyone else run into this?

RE: WMI MSSQL

community@zenoss.org — Sat, 23 Jan 2010 16:54:00 GMT

I think you cannot get MSSQL WMI counters from a 64bit OS...unless you make some changes :

http://blogs.msdn.com/psssql/archive/2009/03/03/how-it-works-almost-everything-you-wanted-to-know-about-the-sql-server-2005-2008-performance-counter-collection-components.aspx

Also, SQLServerCacheManager seems to have been removed since MSSQL2005. I'll try to make 3 templates (1 for each version) if I can get an hand on a production like MSSQL2005 and 2008 servers.

RE: WMI MSSQL

community@zenoss.org — Sat, 23 Jan 2010 19:50:26 GMT

So...on a 64-bit OS with 64-bit SQL SERVER, what are my options? If not WMI, then what?

RE: WMI MSSQL

community@zenoss.org — Sun, 24 Jan 2010 09:46:14 GMT

If you read the link I posted above, you'll see :

Wow64 Installations

The WOW installations of SQL Server are 32 counter DLLs so collecting and monitoring them from a 64 bit Windows Performance Utilityis not possible or supported. To monitor such counters you must start the 32 bit Windows Performance Monitor or other utility. To monitor 32 bit SQL Server installations on a 64 bit operating system use the 32 bit version of Windows Performance Monitor.

Starting a 32 bit Windows Performance Monitor session: mmc.exe /32 perfmon.msc

The performance DLLs installed and the services registry keys are critical to understanding what you can and cannot collect from WOW installations. There is only a single service key for the instance in the registry. Unlike other locations in the registry that support the WOW6432Node services are singular on the system. For a instance named SQL2008 the service key is MSSQL$SQL2008. This key contains the pointer to the performance library details, including the performance library DLL.

SQL Server places the performance counter libraries in the System32 location and allows loading via the standard path searches. In the case of a 32 bit installation (in the WOW) on a 64 bit OS the sqlctr*.dll is placed in the Windows\SysWow64 directory.

So when you run Perfmon or WMI they are 64 bit applications and attempt to load the counter from Windows\System32. This will be a 64 bit counter DLL and allow access to SQL Server counters. However, for a WOW only installation of SQL Server the performance dll is located in Windows\SysWow64 and won't be loaded. Even if the DLL is found in a path search the 64 bit application is not allowed to load a 32 bit binary and will fail.

So you say: "Just copy a x64 sqlctr*.dll into the Windows\System32 and it should work." - Wrong

This is not supported by Microsoft
The Services key contains a library validation code which is a checksum of the binary and will be incorrect as it was loaded for the 32 bit version.
If you use unlodctr/lodctr to correct the validation code to match that of the 64 bit DLL 32 bit collection will now fail as the validation code is not the same for the 32 bit version of the DLL
Failures to access the DLL result in 'Disable Performance Counters = 4' so it is likely that shortly after you get this working the performance counters will get disabled from a failed access attempt.
SQL won't know you copied this and can't apply the proper updates to the files

SQL 2000: Does not have a x64 build of the performance library. SQL 2000 only shipped x86 and IA64 builds so you can't accomplish this.

SQL 2005: Ships the sqlctr90.dll so you can get this to work but as mentioned above it will be problematic.

SQL 2008: Does not use sqlctr100.dll but instead copies it to the system path and renames it to match the instance name and version. So even if you copy sqlctr100.dll to the system path it still won't work as expected.

RE: WMI MSSQL

community@zenoss.org — Mon, 25 Jan 2010 14:18:01 GMT

Hello,

Ok, I'm almost done for the new version of the ZenPack.

It will include :

- MSSQL 2000 template (updated to make it work better)

- MSSQL 2005 template

- MSSQL 2008 template

All these templates are for MSSQL installed using instance.

Shall I make 3 more templates for "non-instance" installation? (you just need to modify the template...)

I should be ready soon, it takes a little bit more time because I erased by accident my Test Zenoss machine .

Richard

RE: WMI MSSQL

community@zenoss.org — Mon, 25 Jan 2010 15:28:41 GMT

The template for MSSQL 2008 instance is available on the new version (1.4).

I've uploaded it on SVN (http://zenpacks.zenoss.org/trac-zenpacks/browser/zenpacks/ZenPacks.community.WMIPerf_MSSQL).

The new template adds some neat counters.

I've also corrected the old one.

I couldn't add (yet) the MSSQL 2005, I hope to bring it soon!

I'll wait for your comments/remarks.

Regards,

Richard

RE: WMI MSSQL

community@zenoss.org — Tue, 26 Jan 2010 14:27:14 GMT

All 3 templates are ready!

- 2000

- 2005

- 2008

All 3 of them are different and can monitor different items (based on what is available).

Please do send me your feedback and comments !

Regards,

Richard Esteve

RE: WMI MSSQL

community@zenoss.org — Tue, 26 Jan 2010 16:51:26 GMT

Updated the attached ZenPack and documenatation.

RE: WMI MSSQL

community@zenoss.org — Mon, 08 Feb 2010 09:13:40 GMT

New version : 1.4.1

- Bug correction on the SQL2008 template

- Optimisation on the SQL2000 template

- Graphs tunning on the SQL2005 template

RE: WMI MSSQL

community@zenoss.org — Fri, 12 Feb 2010 00:35:28 GMT

I'm grateful for the work being done on these zenpacks. I have installed 1.4.1. We have sql 2005, 32-bit, and just one instance of sql 2005 (not sure if that makes it an instance or non-instance installation). I'm still not getting the graphs. I've applied the templates and it looks right. Do the instructions mentioned above still apply, ie., should the instance be named "SQLSERVER"? Also, you mentioned making a non-instance zenpack-was that still in the works? Really hoping to get this working.

RE: WMI MSSQL

community@zenoss.org — Fri, 12 Feb 2010 21:14:09 GMT

Well, looks like we have 64-bit sql. I'm more than happy to do whatever testing/work needed to modify an existing zenpack to make it work for 64-bit MSSQL. Just don't know how to do it--anyone out there with ability to get 64-bit up and working?

RE: WMI MSSQL

community@zenoss.org — Fri, 12 Feb 2010 21:51:10 GMT

Hi,

from what I've read and tested, it's not possible to monitor 64bit MSSQL using WMI yet

For non-instance setup, I'm working on it

RE: WMI MSSQL

community@zenoss.org — Wed, 26 May 2010 15:03:58 GMT

hi, i found other issue on last zenpack version:

i've sql2008 32 bit on windows 2008

all couter linked to OLAP class FAIL

Example:

ServerNAV|ServerNAV|/Status/Wbem||4|Could not get WMI Instance (Received NT code 0x80041010 from query: SELECT MemoryUsageKB,FilestoreReadsPersec,FilestoreWritesPersec FROM Win32_PerfFormattedData_MSOLAPSQLServer_MSOLAPSQLServerMemory).\n

this because my corret sintax taken from wmiexplorer is:

SELECT MemoryUsageKB,FilestoreReadsPersec,FilestoreWritesPersec from Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2008Memory

i've to fix manually? it's my fault?

RE: WMI MSSQL

community@zenoss.org — Tue, 01 Jun 2010 14:28:36 GMT

Have you installed SQL2008 as normal or with instance?

I've done all my tests with an instance installation :

Executing command wmic -U 'anamnisgdm\administrateur'%'*****' //192.168.95.21 --namespace='root\cimv2' "SELECT MemoryUsageKB,FilestoreReadsPersec,FilestoreWritesPersec FROM Win32_PerfFormattedData_MSOLAPTEST_2008_MSOLAPTEST_2008Memory" against s-35c-sql.anamnisgdm.local

CLASS: Win32_PerfFormattedData_MSOLAPTEST_2008_MSOLAPTEST_2008Memory

FilestoreReadsPersec|FilestoreWritesPersec|MemoryUsageKB

0|0|39800

DONE in 2 seconds

I've not yet tested it with non-instance but normaly the syntax should be as followed :

non-instance : Win32_PerfFormattedData_MSOLAP_MSOLAPMemory

instance name "test" : Win32_PerfFormattedData_MSOLAPTEST_MSOLAPTESTMemory

With your syntax (eg. SELECT MemoryUsageKB,FilestoreReadsPersec,FilestoreWritesPersec from Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2008Memory ) do you have results ?

Thanks

Richard

RE: WMI MSSQL

community@zenoss.org — Tue, 01 Jun 2010 14:52:44 GMT

your query --> wmic -U 'mydom.local\administrator'%'*****' //192.168.0.4 --namespace='root\cimv2' "SELECT MemoryUsageKB,FilestoreReadsPersec,FilestoreWritesPersec FROM Win32_PerfFormattedData_MSOLAPTEST_2008_MSOLAPTEST_2008Memory"
ERROR: Retrieve result data.
NTSTATUS: NT code 0x80041010 - NT code 0x80041010
[zenoss@zenoss root]$ wmic -U 'mydom.local\administrator'%'***' //192.168.0.4 --namespace='root\cimv2' "SELECT MemoryUsageKB,FilestoreReadsPersec,FilestoreWritesPersec from Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2008Memory"
CLASS: Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2008Memory
FilestoreReadsPersec|FilestoreWritesPersec|MemoryUsageKB
0|0|0

RE: WMI MSSQL

community@zenoss.org — Tue, 01 Jun 2010 14:56:25 GMT

if usefoul i found that link

http://ms-olap.blogspot.com/2009/04/performance-counters-for-ssas-2008.html

Object	Counter	Description
MSAS 2008:Memory	Memory Usage KB	Memory usage of the server process. Same as perfmon counter for Process\PrivateBytes.

RE: WMI MSSQL

community@zenoss.org — Wed, 02 Jun 2010 10:19:09 GMT

Ok !

this is for the SQL Server Analysis Services.

Your working query gets the result for the Analysis Services, which is not the same service as the SQL Server Service.

The zenpack is only for the SQL Server, and based on a Named Instance setup.

Can you please tell me how was installed your SQL Server (Name Instance or standard)?

RE: WMI MSSQL

community@zenoss.org — Mon, 07 Jun 2010 08:53:16 GMT

i check it and is installed as standard (not instance)

RE: WMI MSSQL

community@zenoss.org — Thu, 22 Jul 2010 08:21:18 GMT

For 64 bits version check this out : http://community.zenoss.org/thread/13867?tstart=0

Note : you will need SNMP Informant to make it work.

RE: WMI MSSQL

community@zenoss.org — Wed, 04 Aug 2010 08:46:19 GMT

I'm working on the Zenoss 3.0.0 compatible version.

MSSQL 2000 is ready. I hope to finish the 2 others soon!

Richard

RE: WMI MSSQL

community@zenoss.org — Thu, 05 Aug 2010 15:29:02 GMT

The new version (2.0.1) is available. It includes Zenoss 3.0.0 compatibility.

Richard

RE: WMI MSSQL

community@zenoss.org — Thu, 05 Aug 2010 20:18:02 GMT

You are doing incredible work on this, thanks guys. We are running 64-bit SQL, 2008 enterprise, do we have any other options?

RE: WMI MSSQL

community@zenoss.org — Fri, 06 Aug 2010 07:16:45 GMT

Hello,

It is not possible to get performance counters from 64bits apps. See : http://blogs.msdn.com/psssql/archive/2009/03/03/how-it-works-almost-everything-you-wanted-to-know-about-the-sql-server-2005-2008-performance-counter-collection-components.aspx

The only way I found to monitor it, is to do it by using SNMP. In order to do so, you will have to buy this :

http://www.wtcs.org/informant/SQL/SQL-overview.htm

A good way to do it would be to develop a program that spool the performance counters and print them. Zenoss would then call it by using winexe. That's what does SNMP informant basicely. I have not yet had the time to do it myself

Richard

RE: WMI MSSQL

community@zenoss.org — Fri, 06 Aug 2010 08:30:16 GMT

Ok, I've done a little .NET program that prints out the value of the performance counter given in the arguments.

It is called like this : PCRead.exe objectName counterName instanceName

If you can read the counter on Perfmon, this program can.

All you'll have to do is create a command datapoint on Zenoss like this :

winexe --user=${dev/zWinUser}%${dev/zWinPassword} //${dev/manageIp} 'c:\PCRead.exe "System" "Threads"'

in this exemple it will show the number of threads running on the system where the command was launched.

I can provide you the source code for PCRead.exe (C#). I have not (yet) 64bits SQL server running so I cannot do counter testing.

If you could list the counters on your setup, I could make a specific zenpack for 64bits SQL servers. But you will have to copy my little program on each servers.

Richard

RE: WMI MSSQL

community@zenoss.org — Tue, 02 Nov 2010 16:15:05 GMT

Great Zenpack, having some issues getting it up and running in my Zenos 3.0.2 installation. I installed the ZenPack as per normal and rebooted Zope. The ZenPack showed up properly in my ZenPacks listing but I don't see the addition of any monitoring temlates or any new classes in my Infrastructure. Is there a step I missed? I removed the ZenPack - restarted Zope - installed the ZenPack and restarted Zope with no success. Performed a full Zenoss restart with no change as well.

RE: WMI MSSQL

community@zenoss.org — Sat, 06 Nov 2010 16:12:02 GMT

I've got a need to monitor a mixture of various SQL versions on various versions of windows, mostly 64 bit. After reading through the comments here, I'm at a loss for why folks are saying WMI doesnt work for SQL 64 BIT counters. Let me add I'm not a SQL expert, and I am also new to Zenoss. That said, I've never had an issue collecting data via WMI on 64 bit windows server.

As a test I installed this Zenpack and targeted a Windows 2008 Server (64bit) running SQL 2008. I'm running Zenoss 3.0.2 and the Zenpack version I installed is 2.0.1

Out of the gate it doesn't work and there are alot of WMI events thrown, however its not because the counter don't work. A simple VBScript running on a remote machine confirms the counters return data just fine via WMI. The issue I found in this specific case was that the queries all reference invalid object names.

Lets take a look at the CacheHitRatio Data Source as one example. Here is what the query looks like:

SELECT Directhitratio,DirecthitsPersec FROM Win32_PerfFormattedData_MSOLAP${dev/zSQLInstance}_MSOLAP${dev/zSQLInstance}Cache

The box I have targeted (not using a named instance) has no such object. I also spot checked a few other SQL2008 boxes and none them have any such object either.

As a second example I took the data source 'SQLServer_Locks' The as-is query looks like

SELECT Currentlocks,LatchwaitsPersec,LockRequestsPersec,LockWaitsPersec,Totaldeadlocksdetected FROM Win32_PerfFormattedData_MSOLAP${dev/zSQLInstance}_MSOLAP${dev/zSQLInstance}Locks

This also did not work on my installation, but this time the Locks object exists, albeit via a different name than what is in the template

Lets assume we insert MSSQLServer as the instance name since we are not using a named instance installation. Using the zproperty to fill in the TALES statement we would end up with an object name of 'Win32_PerfFormattedData_MSOLAPMSSQLServer_MSOLAPMSSQLServerLocks'

The MSOLAP seems to be the issue here. On my servers the object name is 'Win32_PerfFormattedData_MSSQLSERVER_SQLServerLocks'

So to validate, I create new empty template and using the WMI data source I create a new data template to capture this. I didnt bother with the zProperty, but I have no reason to believe it wouldnt work. I just didn't bother with it as we dont run named instances in our environment. I couldn't get an exact match as the properties being selected seem to differ as well. One instance is that LatchWaits are stored in Win32_PerfFormattedData_MSSQLSERVER_SQLServerLatches instead of Locks. That aside, I setup the following query:

SELECT LockRequestsPersec,LockWaitsPersec,NumberofDeadlocksPersec FROM Win32_PerfFormattedData_MSSQLSERVER_SQLServerLocks Where Name = '_Total'

This seems to do the trick. So I was thinking I could create new 2008 template using the object names, but before I go through that I wanted see if anyone:

1) has already done that and I just missed it, and

2) perhaps get a little more information on why folks think its not possible to query such counters on a 64 BIT machine via WMI?

3) Thinks any additional or different counters are more valuable. As I'm not a SQL guy, I don't have a know of insight into what the valuable counters are, I'm just going off a few google hits, as well as the counters in this template.

Also, as I am new to zenoss, if I do go ahead and create the new template and get it working, whats the proper method way to 'give it back' so it gets included?

Thanks

RE: WMI MSSQL

community@zenoss.org — Mon, 08 Nov 2010 03:13:20 GMT

dpetzel -

I do not know of anyone that has made this work yet. I would certainly use a SQL 2008 WMI-based zenpack.

RE: WMI MSSQL

community@zenoss.org — Mon, 08 Nov 2010 07:31:03 GMT

Hello,

thanks for the feedback!

WMI counters do work for 64 bit OS, it doesn't for 64 MSSQL. If you installed a 32 bits MSSQL2008 it should work. The counters don't exist for 64 bits MSSQL Installs.

http://blogs.msdn.com/b/psssql/archive/2009/03/03/how-it-works-almost-everything-you-wanted-to-know-about-the-sql-server-2005-2008-performance-counter-collection-components.aspx :

"Wow64 Installations

The WOW installations of SQL Server are 32 counter DLLs so collecting and monitoring them from a 64 bit Windows Performance Utility is not possible or supported."

Please note that I haven't tested if with 2008R2. If you have the counters with a 64bits MSSQL2008 setup, I would love to know how you did that!

Regards,

Richard

RE: WMI MSSQL

community@zenoss.org — Mon, 08 Nov 2010 12:23:31 GMT

So a WOW installation generally means you have SQL32 BIT running on a 64 BIT OS. We've actually dealt with this annoyance in the past on a few SQL2005 32 BIT running on Windows 2003 64BIT, and as a result we've not deployed any 32BIT SQL2008 Installations as a result of this lesson.

Collection data on 64BIT SQL running on 64BIT windows should be a non-issue.

I got the basics of this working over the weekend. I'm going to touch base with a couple of our DBA's this week and ensure the data points being collected make sense and add any they feel revelant.But, in any case it is working.

It seems there are already a couple of SQL related ZENPACKS out there, I'd hate to release another one just for the 2008 template. Would it be possible/feasible for me to export the template and get it incorporated into this existing PACK? or does it make more sense to export a fresh ZENPACK and submit it that way?

RE: WMI MSSQL

community@zenoss.org — Mon, 08 Nov 2010 17:48:46 GMT

I've managed to get this working using the WMI Data source. While digging through the WMI classes I did encounter a discprenency in the naming format between a named instance and an un-named instance.

Lets take a look at Locks for an example.

On the un-named instance the class is presented as:

Win32_PerfFormattedData_MSSQLSERVER_SQLServerLocks

Now a named instance looks like {MyInstance} is of course the name of the instance:

Win32_PerfFormattedData_MSSQL{MyInstance}_MSSQL{MyInstance}Locks

This makes leveraging the zProperty awefully tricky when switching between named and unnamed instances, since the format of the class name actually changes. If the un-named instance had included the extra "MS" in the last segment of the name, things would line up, but sadly they do not. Rather than make things more complicated than they needed to be, I created a new template that does not use the zProperty.

I've incorported the additional data points from our DBA's and have a working template for SQL2008 64 Bit (Un-named instance).

Should I export this at a standalone ZenPack and upload it? Or does it make more sense to include this extra template into this existing ZenPack?

Thanks

RE: WMI MSSQL

community@zenoss.org — Mon, 08 Nov 2010 17:53:43 GMT

Hello,

yes, please do send me the template! I'll test it out and will try to update the ZenPack to include it.

Regards,

Richard

RE: WMI MSSQL

community@zenoss.org — Mon, 08 Nov 2010 18:11:07 GMT

This is going to sound really dumb and show you just how new to Zenoss I am

I can't seem to locate any documentation on how to grab just the template in a transferable manner. Should I just create a dummy zenpack, add my template to it, export that zenpack, and hand over the dummy zenpack?

Whats the prefered method of delivering the files?

RE: WMI MSSQL

community@zenoss.org — Mon, 08 Nov 2010 18:36:30 GMT

Yep, you can do a dummy ZenPack and then send it to me (private message). I'll then check it out

RE: WMI MSSQL

community@zenoss.org — Thu, 11 Nov 2010 03:32:50 GMT

Doesnt appear Private Message allows for attachments. Is there an alternate method you'd prefer that I could get you these files?

RE: WMI MSSQL

community@zenoss.org — Thu, 11 Nov 2010 19:21:00 GMT

I have a quesiton in regards to the funcitonality of this Zenpack. So I know this uses BigEgor's WMIDataSource however the templates don't appear in the list for his CMI/WMI. I was told that in order to use his WMI Monitoring you needed to have your devies in CMI/WMI. Is there any way to open up the templates so that they can be added to any device class? If not I will have some things under the pre-installed classes and some under the CMI/WMI classes, a bit of a weird organization setup.

RE: WMI MSSQL

community@zenoss.org — Fri, 12 Nov 2010 21:03:47 GMT

I think you should be able to use the override/copy trick at the template level and apply this to any device class you want.

We actually created a /devices/servers/windows/sql device class, use the override template option mentioned in http://community.zenoss.org/message/53969

So we are indeed targeting the template at a device class outside of CIM/WMI

RE: WMI MSSQL

community@zenoss.org — Tue, 23 Nov 2010 10:11:34 GMT

Hello,

you don't need to put device in particular places to monitor them using WMI. All you have to do is bind the template to you device class or item.

Regards,

Richard

RE: WMI MSSQL

community@zenoss.org — Tue, 23 Nov 2010 10:16:14 GMT

Hello,

I've been looking at the template you've sent me. While doing so, I've also tested Egor's MS SQL ODBC ZenPack (http://community.zenoss.org/docs/DOC-3391). It works great with 2005-2008 Instance and non-instance setup. It also gives you a better organization.

I recommend you try it out. Adding non-instance monitoring template to this ZenPack would make it even more messy that it is to my point of view.

Regards,

Richard

RE: WMI MSSQL

community@zenoss.org — Tue, 03 May 2011 21:20:04 GMT

Has anyone got this to work?

I sure cant.

RE: WMI MSSQL

community@zenoss.org — Fri, 06 May 2011 16:13:10 GMT

I'm having some issue getting this going. I'm running Zenoss on Ubuntu 10.04 and trying to monitor SQL Server 2005.

First, the Disk IOPss graph just didn't show up. I got a broken image icon. I just created a new graph and it all worked fine.

The SQL Server graphs just show nan's, and I'm getting an event :

Could not get WMI Instance (Received NT code 0x80041010 from query: SELECT LoginsPersec,LogoutsPersec,UserConnections FROM Win32_PerfFormattedData_MSSQLSERVER_MSSQLSERVERGeneralStatistics)

I don't believe the SQL Server I'm monitoring is an instance, but I could be wrong. This is what I have in HKEY_LOCAL_MACHINE -> SOFTWARE -> MICROSOFT -> MICROSOFT SQL SERVER -> INSTALLEDINSTANCES

MSSQLSERVER

So in zSQLInstance I have SERVER.

Any ideas what I'm missing. I'm really new to ZenOSS.

RE: WMI MSSQL

community@zenoss.org — Fri, 06 May 2011 17:41:36 GMT

Looks like I got it.

I kept SERVER in zSQLInstance, and changed every query from (eg): (Bold text changed)

SELECT LongestTransactionRunningTime FROM Win32_PerfFormattedData_MSSQL${dev/zSQLInstance}_MSSQL${dev/zSQLInstance}Transactions

SELECT LongestTransactionRunningTime FROM Win32_PerfFormattedData_MSSQL${dev/zSQLInstance}_SQLServerTransactions

(Well, I changed the Query so it worked via 'TEST'. I still don't have anything in the graphs... yet.

RE: WMI MSSQL

community@zenoss.org — Fri, 13 May 2011 16:32:47 GMT

I have 2 Win2003 64 bit cluster with 2 MSSQL 64 bit instances. I'm monitoring the machines and the instances separated. (Placed each instance in each machine (active/active)

Run WMI Explorer in one instance and checked if the wmi classes that are in the zen pack are in the instance, and then i verified that my only problem is that i was putting mssqlXXXX in the name of the instance and the zenpack already predict this. so placed the instance name like it is in the service (without MSSQL) and it is running in 64 bit.

Attention for default instances, this ones are named MSSQLSERVER_SQLServer (so place only in zSQLInstance:SERVER_SQLServer)

one more thing i detected that the memory graph have an issue: the max y and the min y are in the reverse order. How it is: MIN Y=0 MAX Y= -1

hope i had helped