MSSQL Monitors not working - Shows N/A in OpManager

MSSQL Monitors not working - Shows N/A in OpManager

This problem could due to the following reasons:

1)WMI is not configured properly in OpManager (OpManager uses WMI credential to monitor MSSQL servers).
2)Required Monitors are not associated to the device  in OpManager.
3)WMI might not be responding to the OpManager WMI request
4)WMI is not working properly on the device itself.

1)WMI is not configured properly in OpManager (OpManager uses WMI credential to monitor MSSQL servers).

Solution: Configuring WMI for multiple devices at a time :

1.Go to Admin --> Credential Settings
2.Click New
3.Configure the following parameters and click Add to add the credentials:

Credential Type: Windows

Name: Configure a name for the credential and also provide the description.

configure the Domain Name\Userd ID  and the password  (Example:- TestDomain\TestUser  Testpassword) > Click on "Add".

Associate the above credential using Quick Configuration Wizard to multiple devices:-

   1. From the Admin tab, select Quick Configuration Wizard.
   2. Select the option Associate a credential to several devices and click Next.
   3. All the available Credentials are listed. Select the Credential (the one you created above ).
   4. Select the devices to which you want to assign the credential from the left column  and move them to the right.
   5. Click Finish. The Credential is associated to the selected devices.

Configuring WMI credential using device snap shot page(Single device):

Go to device snap shot page > Click on "Click here to change" option besides Passwords >select "Use the below credential for the device" option >Type in the username (Ex:domainname\username)  and Password > Click on Test credential (make sure its gets passed) and then Ok to save it.

In case  OpManager itself is going to be MSSQL server then leave the username and password blank and click OK as we do not require credential for the local host.

2)Required Monitors are not associated to the device  in OpManager.

Solution: Adding monitors for MSSQL Monitors:-

Here are the steps to associate the MSSQL monitors to a device:

   1.Go to the snapshot page of a device.
   2.Scroll down and select the Monitors tab.
   3.Click on Performance Monitors. The monitors are listed on the right.
   4.Click the Add Monitor button on the right. A list of monitors is displayed.
   5.Click the MSSQL Monitors button on top of this list. The monitors of all the MSSQL parameters are displayed.
   6.From this list, select the required MSSQL Monitors and click on  Add > List of MSSQL databases will be listed for which you want to
      associate Monitors
   7.Check mark all the required databases and click on OK , Now you will get a dash board for MSSQL and data will be populated after
      some time.

3)
WMI might not be responding to the OpManager WMI request

Solution:Use the wmiadap utility to update the WMI performance classes from the performance libraries. Running wmiadap updates all the performance classes. The /f switch in the below command  forces an update of the WMI classes from the performance libraries.

Step 1:

1. Go to the monitored device.
2. Click Start-->Run,
    For Windows 2000 winmgmt /resyncperf' command.
    For Windows XP and 2003 'wmiadap /f' command.
3. After running this command about 4 times, wait for an hour and check whether MSSQL monitors shows data.

Step: 2

1. Go to the monitored device.
2. Click Start-->Run-->services.msc
3. Restart WMI service

4)WMI is not working properly on the device itself.

Solution: Still if you do not see the counter values, verify  whether OpManager server is able to pull up MSSQL counters information  by executing the following queries.If you are able to pull the information for the following queries OpManager should be able to show the information in the dashboard.

If some of the below query gets the output and some of them not then the respective counters information associated with the non working classes will not be shown on the dash board.

1. From OpManager command prompt go to <OpManager\conf\application\scripts>
2. Execute the following commands and make sure we get the output from the device.

cscript wmiget.vbs <Devicename or IP> <Domainname\Username> <Password> ROOT\CIMV2 "select *  from Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager " 

Example:\\
OpManager\conf\application\scripts>cscript wmiget.vbs MSSQLSERVER1 Test\Username Password  ROOT\CIMV2 "select *  from Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager "

cscript wmiget.vbs <Devicename or IP> <Domainname\Username> <Password> ROOT\CIMV2 "select *  from Win32_PerfRawData_MSSQLSERVER_SQLServerCacheManager "

cscript wmiget.vbs <Devicename or IP> <Domainname\Username> <Password> ROOT\CIMV2 "select *  from Win32_PerfRawData_MSSQLSERVER_SQLServerPlanCache "

cscript wmiget.vbs <Devicename or IP> <Domainname\Username> <Password> ROOT\CIMV2 "select *  from Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases"

cscript wmiget.vbs <Devicename or IP> <Domainname\Username> <Password> ROOT\CIMV2 "select *  from Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics "

cscript wmiget.vbs <Devicename or IP> <Domainname\Username> <Password> ROOT\CIMV2 "select *  from Win32_PerfRawData_MSSQLSERVER_SQLServerReplicationAgents "

cscript wmiget.vbs <Devicename or IP> <Domainname\Username> <Password> ROOT\CIMV2 "select *  from Win32_PerfRawData_MSSQLSERVER_SQLServerLocks"

cscript wmiget.vbs <Devicename or IP> <Domainname\Username> <Password> ROOT\CIMV2 "select *  from Win32_PerfRawData_MSSQLSERVER_SQLServerMemoryManager"

cscript wmiget.vbs <Devicename or IP> <Domainname\Username> <Password> ROOT\CIMV2 "Select *  from Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics"

If you are getting "Invalid class" error message for the above queries then either the corresponding wmi classes are absent or corrupted on the MSSQL server.

Please refer the link below to reregister or rebuild the WMI classes.

http://windowsxp.mvps.org/repairwmi.htm

For any further assistance please  contact support@opmanager.com.