Query Conditions

The AND, OR and HAVING operators are located in the Query Conditions title bar in the Query Builder.

Select from the Entity List and use the operators to build query conditions as part of your query.

There are two parts in the condition display panel: one starts with a WHERE condition and another one starts with a HAVING condition.

In the HAVING part, the aggregate field has options: Count, Min, Max, and AVG. The option SUM also applies to certain entities with ID in name (Session ID, Global ID, Full SQL ID, Instance ID). If the HAVING button is not checked, the condition is inserted into the WHERE part with the aggregate field as empty string. If the HAVING button is checked, the condition is inserted into the HAVING part and the aggregate field has options. After adding or removing a condition, the condition option will be updated. Pressing SAVE generates a SQL. The SQL is validated before saving it. If validation failed (for example, syntax error), it generates an alert error message and puts a more detailed error description in the log. If adding a condition at the wrong part, (for example, HAVING button is set, and the attribute icon is dropped on the WHERE part, or vice versa) it generates a not-matched alert message. If the selected condition is in WHERE part, but the HAVING button is set, the adding condition fails because the setting is not matched.

The attributes Total Access, Failed SQLs, and Successful SQLs can be added only under a HAVING clause (not the WHERE clause).

Allowed queries must have one time stamp column and either at least one column with Mode=Count OR the count flag set (or both). The query column to be evaluated by the query must be one of the columns with Mode=Count OR the total access column (if the count flag is set).

Add or Remove a Query Condition

1. To remove a query condition, mark the check box in the row for that condition, and click the X button (Delete marked item) in the Query Conditions title bar.
2. To add a condition, create a row in the Query Conditions list for the appropriate field from the Entity List pane.

   To add an AND condition, select the AND radio button in the Query Conditions title bar and do one of the following:

   • Select an entity from the Entity List pane and select Add Condition from the pop-up menu.
   • Drag the field icon from the Entity List pane, and drop it in the Query Conditions pane.

   To add an OR condition, select the OR radio button in the Query Conditions title bar and do one of the following:

   • Drag the field icon from the Entity List pane, and release it to the start of the condition for which it is an OR condition.
   • Mark the check box for the condition to which you want to add the OR condition, click the field in the Entity List pane, and then select Add Condition from the pop-up menu.

3. Optional: Use the Aggregate drop-down to select an aggregate of the attribute to be used for the query condition: Count, Min (minimum value), Max (maximum value), or AVG (average value). Restrictions apply, as follows:
   • You cannot use an aggregate in an OR condition.
   • You cannot add an OR condition to one that contains an aggregate.
4. Select the operator for the new condition from the list. Not every attribute type has the same set of operators available. For example, attributes that cannot be associated with groups will not have any of the group options (IN GROUP, LIKE GROUP). However, when adding tuples (multiple attributes that are combined together to form a single group) as a condition of a query, all operators for new condition are available for selection.

   Table 1. Operator for New Condition

   Operator	Description
   <	Less than
   < =	Less than or equal to
   < >	Not equal to
   =	Equal to
   >	Greater than
   > =	Greater than or equal to
   CATEGORIZED AS	Member of a group belonging to the category selected from the drop-down list, which appears when a group operator is selected.
   CLASSIFIED AS	Member of a group belonging to the classification selected from the drop-down list, which appears when a group operator is selected.
   IN DYNAMIC GROUP	Member of a group that is selected from the drop-down list in the runtime parameter column, which appears when a group operator is selected.
   IN GROUP	Member of the group that is selected from the drop-down list in the runtime parameter column, which appears when a group operator is selected. IN GROUP or IN ALIASES GROUP cannot both be used at the same time.
   IN DYNAMIC ALIASES GROUP	The operator works on a group of the same type as IN DYNAMIC GROUP, however assumes the members of that group are aliases.
   IN ALIASES GROUP	The operator works on a group of the same type as IN GROUP, however assumes the members of that group are aliases. Note that the IN GROUP/IN ALIASES GROUP operators expect the group to contain actual values or aliases respectively. An alias provides a synonym that substitutes for a stored value of a specific attribute type. It is commonly used to display a meaningful or user-friendly name for a data value. For example, Financial Server might be defined as an alias for IP address 192.168.2.18.
   IS NOT NULL	Attribute value exists, but might be blank or unprintable
   IS NULL	Empty attribute
   IN PERIOD	For a time stamp only, is within the selected time period
   LIKE
   LIKE GROUP	Matches a like value that is specified in the boxes. A like value uses the percent sign as a wildcard character, and matches all or part of the value. Alphabetic characters are not case-sensitive. For example, %tea% would match tea, TeA, tEam, steam. If no percent signs are included, the comparison operation is an equality operation (=).
   NOT IN DYNAMIC GROUP	Not equal to any member of a group, which is selected from the drop-down list in the runtime parameter column, which appears when a group operator is selected.
   NOT IN DYNAMIC ALIASES GROUP	The operator works on a group of the same type as NOT IN DYNAMIC GROUP, however assumes the members of that group are aliases.
   NOT IN GROUP	Not equal to any member of the specified group, which is selected from the drop-down list in the runtime parameter column, which appears when a group operator is selected.
   NOT IN ALIASES GROUP	The operator works on a group of the same type as NOT IN GROUP, however assumes the members of that group are aliases.
   NOT IN PERIOD	For a time stamp only, not within the selected time period
   NOT LIKE	Not like the specified value (see the description of LIKE)
   NOT LIKE GROUP	Not like the value that is specified in LIKE GROUP
   NOT REGEXP	Not matched by the specified regular expression
   REGEXP	Matched by the specified regular expression For detailed information about how to use regular expressions, see Regular Expressions.

   Note: There are four special words that are not allowed as the name of a parameter: user; group; role; page.

   An error results if an attempt is made to save a query with any of these words in the parameter. There are two types of conditions where this applies:

   • When creating a query condition with an operator such as =, <, LIKE, etc, and then selecting Parameter. This field does not allow the special words.
   • When creating a query condition with a DYNAMIC GROUP type operator (IN, NOT IN, IN ALIAS, etc), this field does not allow the special words.
5. For a group operator, select a group from the list.

   For most other operators, you must supply a value for the condition, or indicate that a runtime parameter value (not containing exclamation points) is supplied later (when the query is run). In these cases, a drop-down with three options appears. Do one of the following:

   • Select Value and enter an exact value in the box.
   • Select Parameter and enter a name for the runtime parameter (the name must not contain spaces).
   • Select Attribute and select another attribute to match the selected one (for example, this can be used to test for local traffic by matching the client and server IP addresses).

   There is an Add Expression icon next to the Value, Parameter, Attribute selections. Use this icon to enter query conditions, including user-defined string and mathematical expressions.

   Use this feature where the user needs to add a condition that is based not on the entire content of the attribute as is, but on part of the attribute, a function of the attribute, or a function that combines more than one attribute.

   An example is: INSTR(:attribute, '150.1') = 5, which returns all instances of Client IP matching the 5 characters listed. Type the character 5 in the entry box next to the Add Expression icon. Type the INSTR(:attribute, '150.1') expression in the separate Build Expression window. Test the validity of the expression in the Build Expression window. Another example is: LENGTH(:attribute) >= 40, which returns the length of any SQL statement greater than 40 characters. The expression might or might not contain references to the actual attribute and can also contain references to other attributes.

6. When you are done adding all conditions, remember to save the definition.

Build Expression on Query condition

There is an Add Expression icon next to the Value, Parameter, Attribute selections. Use this icon to enter query conditions, including user-defined string and mathematical expressions.

Use this feature where the user needs to add a condition that is based not on the entire content of the attribute as is, but on part of the attribute, a function of the attribute, or a function that combines more than one attribute.

An example:

Return the location of the string 150.1, from the value 192.150.1.x., where the string 150.1 is at the fifth character of the value. The string 150.1 represents all instances of Client IP matching the 5 characters listed.

When the function is run in the Expression field, it returns a value, and that value should be in the entry box.

Use the function, INSTR(:attribute, '150.1') with a 5"\ value in the entry box next to the Add Expression icon to return the records with 150.1 in the fifth location.

If the function is INSTR(:attribute, '150.1') = 5, then it becomes a Boolean phrase, and the only values in the entry box are 0 or 1.

Type the INSTR(:attribute, '150.1') expression in the separate Build Expression window.

Test the validity of the expression in the Build Expression window.

Another example: LENGTH(:attribute) >= 40, which returns the length of any SQL statement greater than 40 characters. The expression might or might not contain references to the actual attribute and can also contain references to other attributes.