Adding and editing a SQL Query performance monitor

This monitor allows you to check for certain conditions in a Microsoft SQL, MySQL, or ORACLE database, based on a database query.

Note: This feature is available with WhatsUp Gold Premium Edition only. To update your license, visit the WhatsUp Customer Portal.

Important: To use the SQL Query monitor to monitor a MySQL database, you must first download and install the MySQL .NET Connector on the WhatsUp Gold machine. Note that only MySQL version 5.2.5 .NET Connector is supported due to compatibility issues. The connector is located on the WhatsUp Gold website (http://www.whatsupgold.com/MySQL525Connector). This link downloads the mysql-connector-net-5.2.5.zip file. After the file downloads, extract the MySQL.Data.msi and run the MySQL Connector setup utility by double-clicking on the MySQL.Data.msi icon. On the Choose Setup Type dialog, select Typical, then click Install. The MySQL .NET Connector is installed in the following location: C:\Program Files\MySQL\MySQL Connector Net 5.2.5\. After the .NET Connector has been installed, restart the WhatsUp Gold machine.

Note: The SQL Query monitor supports Windows and ADO authentication. Make sure that credentials are setup in the Credentials Library for the database for which you want to query. The Credentials system stores Windows and ADO database credential information in your WhatsUp Gold database to be used when a database connection is required. For more information, see Using Credentials.

Note: When connecting to a remote SQL instance, WhatsUp Gold only supports the TCP/IP network library.

To add a new SQL Query performance monitor:

1. From the WhatsUp Gold web interface, go to Admin > Monitor Library. The Monitor Library dialog appears.
2. Click the Performance tab. The Performance Monitor list appears.
3. Click New. The Select Performance Monitor Type dialog appears.
4. Select SQL Query Performance Monitor, then click OK. The New SQL Query Monitor dialog appears.
5. Enter the appropriate information:
   • Name. Enter a unique name for the performance monitor. This name displays in the Performance Monitor Library.
   • Description. (Optional) Enter additional information about the monitor. This description displays next to the monitor in the Performance Monitor Library.
6. Enter or select the appropriate information for the Server Properties section:
   • Server Type. Select Microsoft SQL Server, MySQL, or ORACLE as the database server type.

   Note: MySQL database is supported and listed as a server type option only if the MySQL 5.2.5 Connector is installed.

   • Connection Timeout (sec). Used by the SQL Query monitor to determine how long to wait for the server to respond before terminating the connection and returning the timeout error. Minimum allowed value is 1 second whereas maximum allowed value is 120. The default value is 15 seconds.

   Note: The connection timeout setting configured by the user is used for polling only; the query builder does not use it. Instead, the query builder assumes a default of 15 seconds for the connection timeout.

   • Server Address. ServerName\Instance format for Microsoft SQL Server (for example, WUGServer\SQLEXPRESS), ServerName for MySQL (for example, WUGServer), or ServerName/ServiceName for Oracle (for example, WUGServer/Oracle).

   Note: When using an Oracle server type, the SQL query monitor does not make use of the tsnnames.ora file on the client (i.e. WhatsUp Gold system).

   • Port (optional). The database server port number if other than the standard database port number.
   • SQL Query to Run. A query you want to run against a database to monitor and check for certain database conditions. Only select queries are allowed.

     Important: Make sure that you include the full database name in your query. For query help, click Build. The SQL Query Builder will assist you in developing proper query syntax.

     Important: The SQL query you enter must return a single numeric value. Specifically, a single record that has just one column. If the query returns more than one record, the monitor will fail to store the data. If the query returns a single records but there are multiple columns in the record returned, then the monitor will pick the first column as the value to store and this first column has to be numeric, otherwise the monitor will fail to store the data.

   • Build. Click to open the SQL Query Builder dialog for assistance building queries.
   • Verify. Click to check that the query is valid. If there is a syntax error with the SQL query, a message appears with tips about the syntax issue.
7. Click OK to save changes.
8. After the monitor has been added to the library, you can enable the monitor through Device Properties > Performance Monitors. For more information, see Enabling performance monitors.

To edit an existing SQL Query performance monitor:

1. From the WhatsUp Gold web interface, go to Admin > Monitors. The Monitor Library dialog appears.
2. Click the Performance tab. The Performance Monitor list appears.
3. Select the performance monitor you would like to edit from the list of current monitors, then click Edit. The Edit SQL Query Performance Monitor dialog appears.
4. Enter the appropriate information:
   • Name. Enter a unique name for the performance monitor. This name displays in the Performance Monitor Library.
   • Description. (Optional) Enter additional information about the monitor. This description displays next to the monitor in the Performance Monitor Library.
5. Enter or select the appropriate information for the Server Properties section:
   • Server Type. Select Microsoft SQL Server, MySQL, or ORACLE as the database server type.

   Note: MySQL database is supported and listed as a server type option only if the MySQL 5.2.5 Connector is installed.

   • Connection Timeout (sec). Used by the SQL Query monitor to determine how long to wait for the server to respond before terminating the connection and returning the timeout error. Minimum allowed value is 1 second whereas maximum allowed value is 120. The default value is 15 seconds.

   Note: The connection timeout setting configured by the user is used for polling only; the query builder does not use it. Instead, the query builder assumes a default of 15 seconds for the connection timeout.

   • Server Address. ServerName\Instance format for Microsoft SQL Server (for example, WUGServer\SQLEXPRESS), ServerName for MySQL (for example, WUGServer), or ServerName/ServiceName for Oracle (for example, WUGServer/Oracle).

   Note: SQL query monitors do not make use of tsnnames.ora file on the client (i.e. WhatsUp Gold system).

   • Port (optional). The database server port number if other than the standard database port number.
   • SQL Query to Run. A query you want to run against a database to monitor and check for certain database conditions. Only select queries are allowed.

     Important: Ensure that you include the full database name in your query.

   • Build. Click to open the SQL Query Builder dialog for assistance building queries.
   • Verify. Click to check that the query is valid. If there is a syntax error with the SQL query, a message appears with tips about the syntax issue.
6. Click OK to save changes.

See Also Using Performance Monitors Performance monitors overview Using the Performance Monitor Library Working with Performance Monitors Adding and editing an Active Script Performance Monitor Adding and Editing an APC UPS Performance Monitor Adding and editing a PowerShell Scripting performance monitor Example - PowerShell performance monitor scripts Adding and editing a Printer performance monitor Adding and editing an SNMP Performance Monitor SQL Query Builder Adding and editing an SSH Performance Monitor Adding and Editing a WMI Formatted Performance Monitor Adding and editing a WMI Performance Monitor