WMI MSSQL 2005

WMI MSSQL 2005 Server Profile

This profile monitors several statistics for Windows 2005 MSSQL servers via WMI.

Additionally, you may want to refer to the WMI documentation. This project consists of a collection of script monitors (.vbs for starters) that use the Microsoft .Net Framework and WMI to retrieve performance data from remote Windows hosts without the need for agents on the remote hosts.

Services Configuration
  • Service - Definitions in Monarch are stored under this name.
  • Command Line - Service command name with arguments to be passed to the plugin.
  • Plugin Command Line - Plugin script called by Nagios for this Service.
    Command lines displayed below are intended to be single line commands.
    Service Command Line Plugin Command Line
    wmi_mssql_checkpointpagespersec check_wmi_mssql_
    checkpointpagespersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager" "*" "CheckpointpagesPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_lazywritespersec check_wmi_mssql_
    lazywritespersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager" "*" "LazywritesPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_pagelifeexpectancy check_wmi_mssql_
    pagelifeexpectancy!600000!700000     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_large_rawcount -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager" "*" "Pagelifeexpectancy" "$ARG1$" "$ARG2$"
    wmi_mssql_pagelookupspersec check_wmi_mssql_
    pagelookupspersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager" "*" "PagelookupsPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_pagereadspersec check_wmi_mssql_
    pagereadspersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager" "*" "PagereadsPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_pagewritespersec check_wmi_mssql_
    pagewritespersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager" "*" "PagewritesPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_readaheadpagespersec check_wmi_mssql_
    readaheadpagespersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager" "*" "ReadaheadpagesPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_logbytesflushedpersec check_wmi_mssql_
    logbytesflushedpersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases" "Name=_Total" "LogBytesFlushedPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_logflushwaitspersec check_wmi_mssql_
    logflushwaitspersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases" "Name=_Total" "LogFlushWaitsPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_logflushespersec check_wmi_mssql_
    logflushespersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases" "Name=_Total" "LogFlushesPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_transactionspersec check_wmi_mssql_
    transactionspersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases" "Name=_Total" "TransactionsPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_processesblocked check_wmi_mssql_
    processesblocked!80!90     		 $USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_large_rawcount -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics" "*" "Processesblocked" "$ARG1$" "$ARG2$"
    wmi_mssql_userconnections check_wmi_mssql_
    userconnections!80!90     		 $USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_large_rawcount -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics" "*" "UserConnections" "$ARG1$" "$ARG2$"
    wmi_mssql_locktimeoutspersec check_wmi_mssql_
    locktimeoutspersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerLocks" "Name=_Total" "LockTimeoutsPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_locktimeoutstimeout0persec check_wmi_mssql_
    locktimeoutstimeout0persec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerLocks" "Name=_Total" "LockTimeoutstimeout0Persec" "$ARG1$" "$ARG2$"
    wmi_mssql_lockwaitspersec check_wmi_mssql_
    lockwaitspersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerLocks" "Name=_Total" "LockWaitsPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_deadlockspersec check_wmi_mssql_
    deadlockspersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerLocks" "Name=_Total" "NumberofDeadlocksPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_targetservermemory check_wmi_mssql_
    targetservermemory!300000!400000     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_large_rawcount -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerMemoryManager" "*" "TargetServerMemoryKB" "$ARG1$" "$ARG2$"
    wmi_mssql_totalservermemory check_wmi_mssql_
    totalservermemory!3000!17000     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_large_rawcount -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerMemoryManager" "*" "TotalServerMemoryKB" "$ARG1$" "$ARG2$"
    wmi_mssql_errorspersec check_wmi_mssql_
    errorspersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerSQLErrors" "Name=_Total" "ErrorsPersec" "$ARG1$" "$ARG2$"
    wmi_mssql_batchrequestspersec check_wmi_mssql_
    batchrequestspersec!80!90     		$USER1$/check_nrpe -t 60 -H $USER21$ -c get_counter_bulk_count -a "$HOSTADDRESS$" "Win32_PerfRawData_MSSQLSERVER_SQLServerSQLStatistics" "*" "BatchRequestsPersec" "$ARG1$" "$ARG2$"
Profile Package

This package includes the following files:

Profile Definitions

  • service-profile-wmi-mssql2005.xml
  • perfconfig-wmi-mssql2005.xml

Plugins Scripts on the GroundWork Server

  • check_nrpe
Installation

GroundWork Monitor includes many monitoring profiles for a variety of devices, systems and applications. Profiles already imported on a new GroundWork installation include Service Ping, SNMP Network, and SSH UNIX. The GroundWork Monitor Configuration tool is used to import updated Profiles and Profiles that require additional setup; the Profile XML file and its companion Performance Configuration definition file. Services can also be imported in addition to Service Profiles in the Profile Importer. The import process is documented under GROUNDWORK PROFILES > How to import profiles.

Implementation

This section contains detail settings used by this Profile. These parameters can be altered with the Configuration tool.  The WMI proxy Server must be installed with the GW NRPE Version 2 agent, and the NRPE_NT agent running with the credentials of a user having local administrator privileges at the target (monitored) hosts.

Command Parameters

Command parameters are in the Configuration Services section with the following names and default values.

wmi_mssql_checkpointpagespersec

  • The rate of recording check point pages per second
  • $ARG1$ - Warning threshold.  80
  • $ARG2$ - Critical threshold.     90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi wmi_mssql_lazywritespersec

  • The rate of lazy writes per second
  • $ARG1$ - Warning threshold.  80
  • $ARG2$ - Critical threshold.     90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_pagelifeexpectancy

  • The life expectancy of a page in milliseconds
  • $ARG1$ - Warning threshold.  600,000
  • $ARG2$ - Critical threshold.  700,000
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_pagelookupspersec

  • The rate of page look ups per second
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_pagereadspersec

  • The rate of page reads per second
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_pagewritespersec

  • The rate of page writes per second
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_readaheadpagespersec

  • The rate of read ahead in pages per second
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_logbytesflushedpersec

  • The rate of log bytes flushed per second
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_logflushwaitspersec

  • The rate of log flush waits per second
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_logflushespersec

  • The rate of log flushes per second
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_transactionspersec

  • The rate of transactions per second
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_processesblocked

  • The number of processes in a blocked state
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.   90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_userconnections

  • The number of user connections
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_locktimeoutspersec

  • The rate of lock timeouts per second
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_locktimeoutstimeout0persec

  • The per second rate of lock timeouts where timeout is at 0
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_lockwaitspersec

  • The rate of lock waits per second
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_deadlockspersec

  • The rate of deadlocks per second
  • $ARG1$ - Warning threshold. 80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_targetservermemory

  • The level in bytes of target server memory use
  • $ARG1$ - Warning threshold.  300,000
  • $ARG2$ - Critical threshold.     400,000
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_totalservermemory

  • The level in bytes of total server memory use
  • $ARG1$ - Warning threshold.   3,0000
  • $ARG2$ - Critical threshold.    17,000
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_errorspersec

  • The rate of errors per second
  • $ARG1$ - Warning threshold.   80
  • $ARG2$ - Critical threshold.      90
  • $USER21$ - The WMI Proxy Server IP Address.

wmi_mssql_batchrequestspersec

  • The rate of batch requests per second
  • $ARG1$ - Warning threshold.  80
  • $ARG2$ - Critical threshold.    90
  • $USER21$ - The WMI Proxy Server IP Address
Performance Graphing Parameters

The Performance Configuration database has been set up for each of the service checks to record a single line graph.  The RRD files will be recorded in the /usr/local/groundwork/rrd directory with the Host name.

The graphed elements may be viewed in the Performance application and in the Status object under Status Views and Dashboards, for the monitored Hosts.

For each RRD the values are set as detailed here; if changes are desired then the Performance> Configure tool should be used to modify the values prior to data collection and RRD creation. Alternatively, make changes, then delete the RRD files and Host_Service Table entries and new files will be created automatically:

  • The step value is 600 matching the Normal Check Interval of 10 minutes (600 seconds).  Change this to match if you change Normal Check Interval
  • The Heartbeat is 1800. Change this to an appropriate value if you change the step value.
  • The number of Primary Data Points in the first Averaging function is 8640 allowing 60 days of directly recorded data.
  • The number of Consolidated Data Points is 9480 with an averaging function of 12 allowing 790 days of consolidated data points (12 Primary Data Points averaged together to one CDP.
  • All the values are recorded as type GAUGE.
Implementation Notes

The nrpe.cfg file on the Windows server maps commands issued by the GroundWork server to scripts in the c:\nrpe_nt directory. Commands issued by this profile are installed with this package. If new NRPE commands are added, this file must be modified. Go to the GroundWork Support Portal - GroundWork Connect at www.groundworkconnect.com and search for Installing GroundWork WMI NRPE.

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.