Administration Guide › Queries and Reports › How to Create a Query › Create a Query SQL Statement

Create a Query SQL Statement

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.

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.