- Search Overview
- Search Home Page
- Performing Searches
- Basic Search
- Advanced Search
- Advanced Search Building Blocks
- Running an Advanced Search Query
- Query Syntax
- Query by Subject
- Query by Vendor and Product
- Query by Field and Value
- Query by Context Table
- Query Using Regex
- Free Text Search
- Query Using Advanced Query Language Operators
- Query Using Aggregation Functions
- Query Using Structured Fields
- Dynamic Field Extraction
- Natural Language Search
- Anomaly Search
- Refine a Search
- Context Tables in Search
- Search Best Practices
- Search Results
- Dashboard Visualizations
Query Using Aggregation Functions
In Exabeam advanced query language, a number of aggregation functions are available for creating complex queries that can be used in the Search service. These aggregation functions must be used in conjunction with a SELECT query statement. You can also use an AS clause to provide alias fields names in the output results.
The sections below provide a small table of sample log data, and list the available aggregation functions with syntax examples.
Sample Log Data for Syntax Examples
The sections in Available Aggregation Functions include syntax examples for the use of each function. For the sake of simplicity, the examples will be based on the following table of sample log data.
activity | vendor | product | number |
|---|---|---|---|
login | microsoft | azure | 12 |
logout | null | iphone | 54 |
login | microsoft | xbox | 23 |
logout | exabeam | correlation-engine | 67 |
login | zscaler | windows | 93 |
Available Aggregation Functions
Expand each of the sections below to view information about aggregation functions that are available for constructing complex queries in Search. The syntax examples are based on the table of sample log data in the section above.
Used to count the number of non-null entries for a specific column.
Syntax Example 1: SELECT COUNT(vendor)
Outputs a count of the number of non-null rows in the vendor column.
f0_ |
|---|
4 |
Note
Count columns default to a heading of f0_ unless an alias is applied.
Syntax Example 2: SELECT COUNT(vendor) AS vendor_count
Outputs a count of the number of non-null rows in the vendor column and uses an alias for the column name.
vendor_count |
|---|
4 |
Syntax Example 3: SELECT COUNT(*) AS total_count
Outputs a count of the total number of all rows and uses an alias for the column name.
total_count |
|---|
5 |
Used to retrieve the highest numerical or alphabetical value from a column.
Syntax Example 1: SELECT MAX(vendor)
Outputs the highest alphabetical value from the vendor column.
f0_ |
|---|
zscaler |
Note
Count columns default to a heading of f0_ unless an alias is applied.
Syntax Example 2: SELECT MAX(number) AS max_number
Outputs the highest numerical value in the number column and uses an alias for the column name.
max_number |
|---|
93 |
Used to retrieve the lowest numerical or alphabetical value from a column.
Syntax Example 1: SELECT MIN(vendor)
Outputs the lowest alphabetical value from the vendor column.
f0_ |
|---|
exabeam |
Note
Count columns default to a heading of f0_ unless an alias is applied.
Syntax Example 2: SELECT MIN(number) AS min_number
Outputs the lowest numerical value in the number column and uses an alias for the column name.
min_number |
|---|
12 |
Used to retrieve the sum of the values in a selected column.
Syntax Example: SELECT SUM(number AS sum_number
Outputs the sum of the values in the number column and uses an alias for the column name.
sum_number |
|---|
249 |
Used to retrieve the average of the values in a selected column.
Syntax Example: SELECT AVG(number) AS avg_number
Outputs the average of the values in the number column and uses an alias for the column name.
avg_number |
|---|
49.8 |
Aggregation functions are commonly used in more complex queries with other advanced query language operators such as GROUP-BY and ORDER-BY.
Syntax Example 1: SELECT vendor, COUNT(product) AS numProducts GROUP-BY vendor
Outputs a table that lists the number of products (in a new numProducts column) for each vendor.
 |
Tip
If you don't include the AS clause to provide the new column with a descriptive heading, the count column will have a auto-generated heading as seen in the image below.
 |
Syntax Example 2: SELECT product, AVG(raw_log_size) AS avgLogSize GROUP-BY product ORDER-BY avgLogSize DESC
Outputs a table that lists products and the average log size (in a new avgLogSize column) for each, sorted in descending numerical order by log size.
 |