SQL ADO wizard

Step 1: Select database type

The following example illustrates the configuration process for creating an ADO – User Experience monitor to monitor the Microsoft® SQL Server® database type.

  1. Log in to the ipMonitor administrator interface.
  2. Click Configuration in the toolbar.
  3. Click Add/Edit a Device or Monitor.
  4. Click Add > Add a Monitor.
  5. Select the ADO-User Experience (SQL Query) monitor and click Continue.
  6. Select a Database Type from the list.

Properly-installed OLE DB providers can be selected for monitor configuration. OLE DB providers that are not installed on the ipMonitor host machine are displayed under Unavailable Database Types. Contact your vendor to obtain the correct OLE DB provider for your database type and install it before you continue.

Select the Other database type to manually configure the ADO - User Experience monitor to test other data sources such as SAP DB, FrontBase, FoxPro, and LDAP.

Step 2: Data source location

Select the server that hosts the SQL Server database you want to monitor.

Use TCP/IP versus Named Pipes

Select this option to force ipMonitor to use TCP/IP instead of a named pipe to connect to a Microsoft SQL Server database. Enter the IP address and port of the server database in the Server Address field.

For example, xxx.xxx.xxx.xxx,1433 where 1433 is the default port number for SQL Servers.

Use Data Encryption

Select this option to force ipMonitor to encrypt the data transmitted between the ipMonitor host machine and the database you are querying.

Specify a Microsoft SQL Server Instance

Select this option to connect to a named instance of Microsoft SQL Server. Enter the instance name in the Server Address field.

Step 3: Assign login credential

Select a credential to use while monitoring. Depending on the data source configured for the ADO monitor, some form of authentication to the data source will be required to connect and log in.

Step 4: Select database

Select the database you will use in the monitor. Depending on the database type, this step may not be displayed.

Step 5: Select database table

Select the database table to be used in the monitor.

Show only User Tables

Select this option to display only user-created tables in the Tables list.

Show both User and System Tables

Select this option to display both user-created tables and system tables in the Tables list.

Step 6: Generate SQL Query

ipMonitor uses an SQL statement to query the database. You can supply your own SQL statement or have ipMonitor generate an SQL statement automatically based on your selected table columns.

Automatically generate an SQL statement from selected columns

Select this option to list the columns in the database table. The list displays the data type and length of each column, and whether the column is allowed to contain a null value.

Select the check boxes next to the columns you want to include in the automatically- generated SQL statement.

Manually supply an SQL Statement

Select this option to enter your own SQL statement in a text box.

Step 7: Analysis of results

ipMonitor matches the results of the SQL query to the test parameters that you set up to determine whether the monitor should pass or fail.

Maximum Rows to Retrieve

This value represents the maximum number of rows that the query will be permitted to return to the ADO - User Experience monitor for analysis. The default value is set to 300.

Examine the number of Retrieved Rows based on a numeric equation

If this option is selected, select an operator and number that will be used to test the number of rows returned by the query. Supported numeric tests include:

Pass if the number of rows is:
  • <: less than X
  • >: greater than X
  • <=: less than or equal to X
  • >=: greater than or equal to X
  • ==: equal to X
  • !=: not equal to X

Examine Row Content to perform textual or numeric analysis

Use this option to perform textual or numeric analysis on the data contained in the specified column.

If the query result set returns more than one row, each row in the result set is examined sequentially from the first to last. All configured tests are performed on the column you select.

The test passes if any condition you select is met.

Examine column

When you select the column to examine, note that column 1 is the first column. Counting is 1-based, not 0-based. The data in the selected column can be a string or a number.

Column Will

Several numeric and text comparison methods are available, including regular expression and string matches. The comparison passes if the content of the column adheres to any method listed below.

Method Description
RegEx match Enter a regular expression to match
RegEx non-match Enter a regular expression not to match
Substring search Search for a case-sensitive substring
== Exact match
!= Non-match

Preview

Click Preview to display the data contained within the selected columns. Rows highlighted in red indicate a fail scenario. Clicking Stretch Table in the preview window expands the table to fit the selected content. If you chose to view a large number of columns, some content may be truncated unless you expand the resulting preview table.

Step 8: Name Monitor

Enter a descriptive name for the new monitor.

Create Monitor Enabled

After the monitor is created, it begins querying the database with the selected configuration options. This option is enabled by default.

Store Monitor Statistics for Recent Activity and Historical Reports

ipMonitor begins to record test results, which are used to generate recent activity and historical reports. This option is disabled by default.

Create

Click Create to exit the wizard and access the new monitor in edit mode. You can make any final modifications to the monitor in this mode, including setting Timing and Notification parameters.