[ Main Index | IPSentry Application Index ]
 

The IPSentry Database Connection and Query Monitoring Add-In provides you with the ability to monitor database connectivity and accessibility utilizing local data sources and optionally SQL queries to verify that your databases are functioning and responding.

The Database Monitor is based on a two step process of being able to connect to the specified data source and then optionally execute SQL query. The resulting row count or specified variable value can be compared against a user defined value range to insure that the quantity or resulting data is within a specified threshold.

The Process

During a monitoring cycle, the ODBC add-in will perform the following tasks:

Connect to the database using the specified Data Source and optional security credentials.

Perform any SQL queries specified.

Evaluate either that execution was successful or the row-count / specified variable value is within specified threshold.

If any these steps fail, alerts will be generated and the reason for the failure will be returned to IPSentry.

* This add-in requires the latest Microsoft Data Access Components 2.6 .

From the Entry Editor, set the Type of entry to Add-In, click Select Add-In and select the Database Connection and Query Monitor.
Click on the Configure Add-In button.

Database Connection and Query Monitor Configuration - General Settings

Database Connection and Query Monitoring Add-In Configuration

When you select "Configure Add-In" from the IPSentry machine options editor, you will be presented with the main ODBC Configuration window which allows you to specify the data source and login information to be used when connecting with your database.

This window contains fields allowing you to specify the required details and optional queries to perform against the database

Data Source Name (connection)
This drop-down is used to select a pre-defined Data Source defined on your system through the ODBC applet in control panel.

If you want to add a new Data Source, select the "<new>" item (or simply click "Configure" and the windows Data Source Configuration dialogue will be presented to you.

If you want to use a customized data source and you are familiar with Data Source connection strings, select "<custom>" in this list.  This will enable the custom connection string input field and allow you to enter the "Custom Connection String".

Documentation relevant to the contents of either configuring a data source or creating a custom connection string is beyond the scope of this documentation and you should check your database documentation, MSDN, or other information source or knowledgebase for more information on data source configuration requirements.

Custom Connection String
Editing this field allows you to create customized data source connection settings.

Configure
Clicking this button will present you with the Windows DSN configuration dialogue.

Login Name
To override the login name (user id) configured in the data source, fill out both the Login Name and Password Fields.  This information will be used to login to the data source rather than the DSN configured security information.

Password
To override the login name (user id) configured in the data source, fill out both the Login Name and Password Fields.  This information will be used to login to the data source rather than the DSN configured security information.

Timeout
Enter the number of seconds in which a successful connection must be made to the database.

Ignore Connection Failure
If a connection can not be established, you can configure the add-in to return a 'success' result or a failure result depending on your desired use of the monitor.  For example, if you are not interested in whether or not the database is available and only desire alerts if the defined queries fail, check this box.  We recommend leaving this entry un-checked.

Return Only Record Count
This option is very specific to your use of the %%mach.trimres_fxxxx%% keyword.  By selecting this option, the data returned from the add-in to IPSentry will contain only the value / row count of the defined query with no additional information regarding failure or success.  Unless you have a specific purpose to return a single value without contextual reference, leave this option unchecked.

Test Connection
Click this button to test the ability of the add-in to connect to the data source using the current configuration.  No queries will be executed as only the connection will be tested.

Test Configuration
Click this button to test both the connection and any defined queries.

DB Queries

Database Connection and Query Monitoring Add-In Configuration
The DB Queries tab contains the list of queries that will be executed during the monitoring test.

Query Tests (list)
This list contains any queries you have defined. All queries in this list will be executed on the open connection and if any fail, an alert will be triggered.

Add (button)
Click this button to add a new query.

Modify (button)
Click this button to modify the selected query.

Remove (button)
Click this button to remove the selected query.

Add/Modify SQL Query

Database Connection and Query Monitoring Add-In Configuration
When you select to add or modify a query in the DB Queries list, you will be presented with the Add/Modify Database Query configuration window.

The above query is preferred over performing as Select * from table as we will only be receiving one row of data rather than a collection of data.
Here is a similar query.

Basically, the same test - but we are receiving much more data.
 IPSentry Database Connection and SQL Query Monitor

Run Query
Click this button to run the query.  Results will be displayed in the lower text field.

Execute Only
Check this box if your query does not return data.  Such as a request to execute a stored procedure.  The monitor will simply send the execution and if no error is returned, the query is deemed successful if it returns within the timeout specified.

Min Result
Enter the minimum number of rows (or value) that should be returned to be considered successful.

Max Result
Enter the maximum number of rows (or value) that can be returned to be considered successful.
Placing a '-1' in this field causes the add-in to ignore the max threshold.

Timeout
Enter the number of seconds in which the query must complete.  If the query does not complete in this amount of time, the result will be in error and "Timeout" will be returned.

The actual amount of time spent executing will depend on the ODBC Database Driver and whether it provides full asynchronous execution.

Query (SQL)
This is where you enter your SQL Query to be executed after connection to the database has been established.
e.g. Select COUNT(*) from MyTable.

This field accepts standard SQL query commands (valid for the driver in use for the ODBC connection) as well as a custom variable return value for returning accumulated or calculated values from a row.

For example:
<% TCOUNT %>
Select TCOUNT=COUNT(*) From MyTable where fldAmount>1

The above query would return 1 row containing a field named "TCOUNT". The add-in would identify this field and compare it's value against your MIN and MAX row settings.

The resource utilization and total time to execute this query can be substantially less than using "Select * from MyTable where fldAmount>1" since no rows are actually returned to the add-in.

Obviously, using the statement Select * from MyTable where fldAmount>1 would return the same value.  However, the Add-In would actually have to obtain the data (all rows and columns) thus causing a substantial amount of data to be transferred from the database.   Using a variable identifier is highly recommended.

* SQL command syntax is beyond the scope of this documentation.

Query Results
The query results outlines a summary of information returned as would be processed and validated by the Add-In.  This field does not provide a full list of all data elements.  Rather, information regarding field data, processing time, row count, etc..

 

 



     If you require additional assistance, please visit our on-line support forum at http://forum.ipsentry.com.
 
┬ę1997-2012 by RGE, Inc. - All Rights Reserved
IPSentry® is a registered trademark of RGE, Inc.

 
Support Forums: http://forum.ipsentry.com
Web Site: http://www.ipsentry.com
Support Email: support@ipsentry.com