Administration Guide › Queries and Reports › How to Create a Query › Add Query Details

Add Query Details

The first step in creating a query is entering identifying information and setting any tags you want to include.

To add a new query

1. Open the query design wizard.
2. Type a required query name, and optional short name for use in reports. The short name appears in the report's individual query pane when the query is included in a report.
3. Select the database you want your query to apply to :

   Event

   Applies the query to the event database, which stores all raw and refined event information received by the current server, or available through federation.

   Incident

   Applies the query to the incident database, which stores incidents created by the event correlation system, and event information used to create those incidents. The specific components of an event that are used to create an incident, and thus stored in the incident database, are set by the correlation rule.

4. Type any design notes you want in the Description entry field.

   Note: We recommend using this field for information about the query structure. For example, it could contain an explanation of why the query contains certain fields and function.

5. Select one or more tags that you want your query to be associated with using the Tags shuttle control.
6. (Optional) To add a custom category tag, enter a tag name in the Add Custom Tag entry field, and click the Add Tag button.

   The custom Tag appears, already selected, in the Tags shuttle control.

7. (Optional) To add one or more nested custom tags, select a tag, or type the name of the parent category tag, followed by a backslash, followed by the name of the child tag, then click Add Tag. For example, you could type: "Regulations\Industry Standards". You can add additional tags, maintaining the format: a\b\c and so on.

   Note: If you delete one of the custom nested tags, all the custom tags in which it is nested are also deleted, including the parent tag. If you nest a custom tag inside a subscription tag, and then delete it, only the custom tags are deleted.

   When you complete the process, the new tags appear in the list, with the nested custom tags visible when you expand the parent tag.

8. Click the appropriate arrow to advance to the query design step you want to complete next, or click Save and Close.

   If you click Save and Close, the new query appears in the Query List, otherwise the query design step you choose appears.

More information:

Tag Tasks

Add Query Columns

To create a query, write a SQL statement that retrieves the event information you want from the event log store. The query design wizard helps automate this process.

To create a query SQL statement

1. Open the query design wizard.
2. Enter the name and tag, if not already specified, then advance to the Query Columns step.
3. (Optional) Select the Unique events only check box.
4. Set the CEG columns you want to query by dragging them from the list of Available Columns on the left into the Column field of the Selected Columns pane. They appear in the query display in the order in which they are entered.
5. (Optional) Select the settings you want for each column, including:

   Display Name

   Lets you enter a different name for the column, when it is displayed in Table or Event Viewer format. If you enter no Display Name, the native field name is used as the column name, "event_count" for example.

   Function

   Lets you apply one of the following SQL functions to the column values:

   • COUNT - returns the total number of events.
   • AVG - returns the average of the event_count values. This function is only available for event_count fields.
   • SUM - returns the sum of the event_count values. This function is only available for event_count fields.
   • TRIM - Removes any spaces in the queried text string.
   • TOLOWER - Converts the queried text string to lowercase.
   • TOUPPER - Converts the queried text string to uppercase.
   • MIN - returns the lowest event value.
   • MAX - returns the highest event value.
   • UNIQUECOUNT - returns the number of unique events.

   Group Order

   Sets the query display to show the selected columns grouped by the selected attribute. For example, you can set the query to group events by source name. You can control the order in which it is applied to various columns. If the first column values are identical, the second are applied. For example, you can group multiple events from the same source by username.

   Sort Order

   Controls the order in which the selected value is sorted. You can control the order in which it is applied to various columns. If the first column values are identical, the second are applied.

   Descending

   Sets the column values to display in descending order (highest to lowest value) rather than the default ascending order.

   Not Null

   Controls whether the row is displayed in a table or Event Viewer if it contains no value. Selecting the Not Null check box removes the row from the query result if it contains no displayable value.

   Visible

   Controls whether the column is visible in a table or Event Viewer format. You can use this setting to make the column data available in the details view without showing it in the display itself.

   Note: If you select a Function except TRIM, TOLOWER, TOUPPER or a Group Order setting for a column, you must select the same setting for other columns too. Otherwise, CA Enterprise Log Manager displays error messages.

6. (Optional) Use the up and down arrows at the top of the Selected Columns pane to change the column order as needed.
7. Click the appropriate arrow to advance to the Query Design step you want to complete next, or click Save and Close.

   If you click Save and Close, the new query appears in the Query List, otherwise the Query Design step you choose appears.