Adding and editing a SQL Query active monitor

This monitor lets you check that certain conditions exist in a Microsoft SQL, MySQL, or ORACLE database, based on a database query. You can define the criteria you want to exist in the database and as long as the specified conditions are present, the SQL Query monitor is in an up state. If the database data changes outside the boundaries of the query criteria, the monitor triggers to a down state.

After the monitor is configured on this dialog, you must assign the monitor to a device through the Device Properties > Active Monitors dialog.

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

Important: To monitor a MySQL database, download and install the MySQL .NET Connector on the WhatsUp Gold machine. Please note, only MySQL .NET Connector version 5.2.5 is supported due to potential compatibility issues. The MySQL .NET Connector can be downloaded here.

If you would like to begin monitoring MySQL 5.6/5.7 using this version of WhatsUp Gold, you’ll first need to download the v5.2.5 connector in .zip archive format here. After accessing the MySQL Product Archives URL, select 5.2.5 as the Product Version and .NET & Mono as the Operating System from the applicable drop-down menus, then click Download. Extract the MySql.data.dll file from the downloaded .zip package, then copy it into the following two locations inside the WhatsUp Gold installation folder:
C:\Program Files (x86)\Ipswitch\WhatsUp\
C:\Program Files (x86)\Ipswitch\WhatsUp\html\NM.UI\bin\extensions\WUG\
Please note, the file paths displayed here represent the default locations used by the installation program. If you have WhatsUp Gold installed in a different location, copy the .dll file into the corresponding folders on your server.

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 active monitor:

1. From the WhatsUp Gold web interface, go to Admin > Monitors. The Monitor Library dialog appears.
2. Click the Active tab. The Active Monitor list appears.
3. Click New. The Select Active Monitor Type dialog appears.
4. Select SQL Query Monitor, then click OK. The New SQL Query Monitor dialog appears.
5. Enter or select the appropriate information:
   • Name. Enter a unique name for the monitor. This name displays in the Active Monitor Library.
   • Description. (Optional) Enter additional information about the monitor. This description displays next to the monitor name in the Active Monitor Library.

   Server Properties

   • 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). Enter the amount of time WhatsUp Gold waits 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. Enter 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). Enter the database server port number if other than the standard database port number.
   • SQL Query to Run. Enter 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 record 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.

   Monitor is up if

   Important: All database rows must match the criteria settings in the Monitor is up if section for the monitor to be considered up. If multiple threshold criteria is used in the Content of each retrieved row matches the following criteria, all thresholds must match the criteria in each row.

   • Number of rows returned is. Select this option to determine the success or failure of the monitor scan based on rows returned by the SQL query.
     For the following options, select the appropriate variables to determine the success or failure of the monitor scan:
     • less than
     • less than or equal to
     • greater than
     • greater than or equal to
     • equal to
     • not equal to

   Enter a numeric value for number of rows in the box to the right of the conditions list.

   • Content of each retrieved row matches the following criteria. Select to set criteria that each database row must match to determine the success or failure of the monitor scan.
     • Add. Click to open the New Row Content Threshold dialog. This dialog lets you set the database column values and conditions that must be matched for each table row.
     • Edit. Click to modify existing row criteria.
     • Delete. Click to remove existing row criteria.

   As you specify the desired monitor criteria settings, this description updates to verbally illustrate the monitor you have configured.

6. Click OK to save changes.

To edit an existing SQL Query active monitor:

1. From the WhatsUp Gold web interface, go to Admin > Monitors. The Monitor Library dialog appears.
2. Click the Active tab. The Active Monitor list appears.
3. Select the monitor you would like to edit, then click Edit. The Edit SQL Query Monitor dialog appears.
4. Enter or select the appropriate information:
   • Name. Enter a unique name for the monitor. This name displays in the Active Monitor Library.
   • Description. (Optional) Enter additional information about the monitor. This description displays next to the monitor name in the Active Monitor Library.

   Server Properties

   • 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). Enter the amount of time WhatsUp Gold waits 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. Enter 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). Enter the database server port number if other than the standard database port number.
   • SQL Query to Run. Enter 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 record 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.

   Monitor is up if

   Important: All database rows must match the criteria settings in the Monitor is up if section for the monitor to be considered up. If multiple threshold criteria is used in the Content of each retrieved row matches the following criteria, all thresholds must match the criteria in each row.

   • Number of rows returned is. Select this option to determine the success or failure of the monitor scan based on rows returned by the SQL query.
     For the following options, select the appropriate variables to determine the success or failure of the monitor scan:
     • less than
     • less than or equal to
     • greater than
     • greater than or equal to
     • equal to
     • not equal to

   Enter a numeric value for number of rows in the box to the right of the conditions list.

   • Content of each retrieved row matches the following criteria. Select to set criteria that each database row must match to determine the success or failure of the monitor scan.
     • Add. Click to open the New Row Content Threshold dialog. This dialog lets you set the database column values and conditions that must be matched for each table row.
     • Edit. Click to modify existing row criteria.
     • Delete. Click to remove existing row criteria.

   As you specify the desired monitor criteria settings, this description updates to verbally illustrate the monitor you have configured.

5. Click OK to save changes.

See Also Using Premium active monitors Adding and editing an APC UPS Monitor Monitoring mail servers Monitoring Microsoft Exchange 2003 servers Monitoring a Microsoft Exchange Server Adding and editing a Fan Monitor Adding and editing a File Content monitor Adding and editing a File Properties monitor Adding and editing a Folder Monitor Adding and editing an FTP Monitor Adding and editing an HTTP Content Monitor Adding and editing a JMX active monitor Adding and editing a Network Statistics Monitor Adding and editing a PowerShell active monitor Adding and editing a Printer Monitor Adding and editing a Process Monitor Adding and editing a SQL Server 2000 monitor Adding and editing an SSL Certificate monitor Adding and editing a Temperature Monitor Adding and editing a VoIP Monitor Adding and editing a WMI Formatted active monitor Adding and Editing a WMI Monitor Assigning active monitors Removing and deleting active monitors About critical active monitors Group and Device active monitor reports