Database Conditions

<< Click to Display Table of Contents >>

Navigation:  Introduction > Creating Alerts and Reports > Conditional Alerts > Alert Conditions >

Database Conditions

A Database Alert Condition monitors a SQL Connection (MS SQL,  MySQL or ODBC) and will Activate if a connection to the database cannot be established. You can extend this by entering a SQL Query which will be executed and the condition will be Activated based on the results. The condition will be Triggered if it stays Activated for an extended period of time (configured by you).

 

Creating a New Database Condition

New Database Condition (Click to Zoom)

 

 

Database Alert Conditions

Database Alert Conditions (Click to Zoom)

The Database Alert Condition window has 3 different sections working from the top of the screen to the bottom:

 

yellow1

Section 1. The name of the condition (every Database Alert Condition must have a unique name). The MS SQL or MySQL database connection profile to use (see SQL Connection Profiles). And finally the database polling interval.

 

yellow2

Section 2. The logic which will Activate the condition. The condition can be used only to verify that a connection can be established to the database OR it can be based on the results of a SQL Query. If you opt to use a SQL Query the condition can be activated based on either the number of rows your query returns OR the value of a field in your query.

 

yellow3

Section 3. The Trigger threshold (the amount of time which must pass AFTER the condition is activated before it is triggered), enabling or disabling the condition and saving changes.
Database Condition (Section 1)

Section 1 (Click to Zoom)

Section 1. The name and SQL Connection Properties

 

oIn the Condition Name field enter a unique name for this Alert Condition.

oClick the button to select or create a SQL Connection Profile (see SQL Connection Profiles).

oEnter the Polling Interval.
Section 2 (Connection Only)

SQL Connection Only (Click to Zoom)

Section 2a. Activation Logic - Verify Connection Only

.

If you only want your Alert condition to verify that the SQL Server/Database is online then you just check the Only if the Connection Fails button and proceed to Section 3.
Section 2 (Using a SQL Query)

Using a SQL Query (Click to Zoom)

Section 2b. Activation Logic - Using a SQL Query

A very powerful feature in the Database Alert Condition allows you to Activate the condition based on the results of a SQL Query. To do this:

•Check the Based On Query Results button.

•Select whether your alert will be Activated based on the Field Value returned in the query OR just the Number of Rows returned in the query.

•In the Query field enter your SQL query statement (see rules below).

General Rules for all Queries

➢ Keep them small. Whether you're using the # of rows or the Field Value option limit your query to ONLY 1 field.

➢ Keep them fast. Insure that your query will execute quickly and is optimized for speed. Any query you enter MUST be able to be executed and returned within the Database Connection Timeout defined in your SQL Connection Profile. If your query times out that counts as a fail and will Activate the alert condition.

 

Rules When Using # of Rows

➢ Only select 1 field (NO Select * From ...). It's just as easy to count the rows from a query with 1 field as a query that selects all fields.

➢ Use common sense. If your query has the potential to retrieve huge numbers of rows (tens of thousands, hundreds of thousands, or millions) then DO NOT USE the # of Rows option, it will just create a lot of SQL or Network traffic. Instead use a Count query and the Field Value option.

 

Rules When Using Field Value

➢ Your query MUST only return 1 field and that field MUST be numeric. We recommend using a COUNT or SUM Query.

➢ IF your query returns multiple rows only the value in the TOP row of the returned data set will be used by WinScry to determine whether to Activate the alert condition.

➢ Use common sense. If your query has the potential to retrieve huge numbers of rows (tens of thousands, hundreds of thousands, or millions) then DO NOT USE the # of Rows option, it will just create a lot of SQL or Network traffic. Instead use a Count query and the Field Value option.

 

•Enter the RESULT logical operator. Since the results of the SQL Query will always be numeric (see Rules for SQL Queries above) enter the operator and value which Activates the condition (Greater Than 0, Less Than 1000, etc)

 

After entering values in Section 2a click the btnTestQuerya button to test your query.
Database Condition (Section 3)

Section 3 (Click to Zoom)

Section 3. The Trigger Threshold

 

oSet the DURATION (the Trigger Threshold)

oEnable the condition by checking the Enable This Alert Condition box.

oSave your Database Alert Condition by clicking the OK button.

 

animatedlink Troubleshooting Tip: The single most common mistake people make when using the Database Alert Condition is not properly setting up their SQL Database Connection Profile. If you opt to use the Windows Logon (Trusted) database authentication then you will likely find that you can successfully test your SQL Query in the WinScry Console software (click the btnTestQuerya button) but the WinScry Windows Service consistently fails. This is because when you are running the WinScry software from your desktop and you test your query you are running it as YOU (the user ID you used to log in to Windows) and YOU have a Trusted connection to the SQL Server, but, by default, the WinScry Windows Service runs as the built-in local computer account Local System which (most likely) does NOT have a Trusted connection to the SQL Server. This condition can be easily fixed by EITHER changing your connection profile to use MS SQL Server Security (see SQL Connection Profiles) OR by configuring the WinScry MS Windows Service to run as a domain user who DOES have a Trusted connection to the SQL Server (See Running as a User).