Administration Guide › Administration Searches › Run a New Administration Search › SQL Search Expressions › Search for Data - SQL Requirements

Search for Data - SQL Requirements

CA DataMinder is optimized to allow fast, safe and flexible database searching. But if you want to edit the SQL search expression that CA DataMinder generates automatically, or if you want to write your own customized SQL search expressions, be aware of the following issues:

Note: You can only edit the SQL search expression if you have the Events: Allow unrestricted SQL searches privilege.

SELECT keyword

CA DataMinder will only recognize search expressions that begin with SELECT. To maintain database integrity, it will not allow other keywords such as DROP, INSERT, UPDATE or DELETE.

Result sets

CA DataMinder will only return objects from the first database table specified after the FROM operator. That is, the result set of a search must comprise a single table of a supported type.

Furthermore, CA DataMinder supports 'entire row select' on those supported tables. So for example, within a single search expression, you cannot search for all machines and all users.

In effect, this means that all search expressions must comply with the following syntax examples:

SELECT * FROM WgnMachine

SELECT i.* FROM WgnUser i

Arithmetic operators

CA DataMinder supports the following arithmetic operators, > < <= >= and <>. You can use these operators in conjunction with the Event Size and Date filters, and also when limiting result sets chronologically.

Dates

The search filter tabs let you quickly define date ranges, without needing to edit the SQL search expression directly. But if you want to run repeat searches using different date ranges, you may find it faster to edit the appropriate SQL lines directly before running each search. Detail:

1. Use the search filter tabs to find all e-mail events captured between two specific dates.
2. Now go to the SQL tab and click Edit to customize the automatically-generated SQL search expression.
3. Find the lines similar to these:

   AND e.EventTimestamp>={d '2002-08-01'} This defines the start date.

   AND e.EventTimestamp<{d '2002-08-02'} This defines the end date.

4. Edit the dates directly and click Search Now.

Limiting result sets chronologically

CA DataMinder supports search expressions that limit the size of a result set. When used in combination with chronological result sorting, this enables you to retrieve, for example, the 25 most recent Web page warnings.

SQL Server and Jet

Use a SELECT TOP expression. The syntax must comply with the following example:

SELECT TOP 25 * FROM WgnEvent
  ORDER BY EventTimeStamp desc;

Oracle8i

Because SELECT TOP expressions are not supported, the syntax must take this format:

SELECT * FROM
  (SELECT * FROM WgnEvent
  ORDER BY EventTimeStamp desc)
  WHERE rownum <=25;

More information:

Supported Table Types - Administration Data Searches