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

Add Query Columns

To create a query against the incident or event databases, 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 clicking the Add button at the top of the pane. Columns appear in the selected display in the order in which they are added. You can alter the order using the up/down buttons at the top of the pane.
5. 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.

   Each CEG field belongs to one of three types; string, integer, or datetime. When you add a CEG field to a query, CA User Activity Reporting Module makes functions that apply to its type available. For example, if you select ideal_model, only those functions that apply to string values appear.

   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 other than TRIM, TOLOWER, TOUPPER or a Group Order setting for a column, you must also select the same setting for other columns. Otherwise, CA User Activity Reporting Module 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.

More information:

How to Create a Query

Add ODBC Query Columns