Feedback:

aggr

Use aggregation functions to summarize results as a single value (or an array).

You can also drilldown on the aggregated values to launch a new search that uses these aggregated values as a filter in the where operator and display more details about these specific values. See Aggregate, Sort, and Limit Result Rows for more information.

Syntax

|aggr <aggr function> [as new_field] [, <aggr function> [as new_field], ...] by <field> [, <field>, ...]

Usage

Use aggr to drilldown on the aggregated values to launch a new search that uses these aggregated values as a filter in the where operator and display more details about these specific values. See Aggregate, Sort, and Limit Result Rows for more information.

aggr supports the following aggregation functions:

Function Usage

array_agg()

values()

Return an array of distinct result values. The array_agg() and values() functions are equivalent.

Note: This function consumes significant memory to store aggregated values. See AQL Best Practices to use array_agg() effectively.
avg() Return the average of result values. Applies to numeric and date fields.
count() Return a count of results.
count(distinct <field>) Return a count of distinct values in results.

count_distinct()

distinct_count()

dc()

Return a count of distinct values in results.
distinct() Returns distinct values for the specified field.
first() Returns the first entry in a result set. For more information, see first.
last() Returns the last entry in a result set. For more information, see last.
latest() Returns the latest or the most recent entry in a result set.
max() Return the highest value among results. Applies to numeric and date fields.
median() Return the middle value of ordered results. Applies to numeric and date fields.
min() Return the lowest value among results. Applies to numeric and date fields.
mode() Return the most frequent value of the specified field. Applies to numeric and date fields.
perc()

Return the specified percentile value of the specified field. Applies to numeric fields only.

The function has the following syntax:

perc(<field>, <percentile>)

The following example returns the 25th percentile value for the volume field:

|aggr perc(volume, 25)

range()

Return the difference between the lowest and highest value for the specified field. Applies to numeric fields.
sparkline[(<func>(<field>), <timespan>)]

Returns one or more columns having a series of computed values to plot a sparkline visualization. The maximum number of buckets is 60.

If you do not specify options, the default aggregation function is count(*) and the timespan is computed dynamically.

You can specify one of the following options:

  • <func>(<field>): Specifies an aggregation function and field argument.

  • <timespan>: Specifies a timespan for buckets

  • Specify timespan in the the following form:

    • Copy 
    <integer>{s,h,m,d,w,mon,y}

  • Multiple units are not allowed for week, month, or year. In other words, specify 1 for the integer place when specifying week, month, or year.

  • Similar to the timetrend operator, after you enter a search, the value for timespan may be recalculated and rationalized to create meaningful buckets, given the configured time range.
stdev() Return the sample standard deviation for the set of values returned the specified field.
stdevp() Return the population standard deviation for the set of values returned the specified field.
sum() Return the sum of result values. Applies to numeric and date fields.
var()

Return the sample variance of a data set. Applies to numeric and date fields.

Example:

|aggr var(field) as field_variance

Note: In a complex AQL expression that includes aggr sum() operations, the variance function should be last in the sequence.

Examples

Note: For OCSF schema fields, array indexes start at array[1] and not array[0].

Example 1: Aggregate distinct source port values grouped by source port.

Copy 
|aggr array_agg(src_port) by src_port

Example 2: Calculate the average of incoming bytes.

Copy 
|aggr avg(bytes_in)

Example 3: Count events grouped by source address.

Copy 
|aggr count by src

Example 4: Count distinct source addresses with a custom result field name.

Copy 
|aggr count(distinct src) as distinct_sources

Example 5: Find the highest destination port value.

Copy 
|aggr max(dest_port)

Example 6: Calculate the range of byte values.

Copy 
|aggr range(bytes_in)

Example 7: Calculate standard deviation of an age field.

Copy 
|aggr stdev(age)

Example 8: Visualize data with sparklines grouped by source type.

Copy 
|aggr sparkline, count as cnt by sourcetype

Example 10: Counts the number of unique source IPs per OCSF event category.

Copy 
ocsf | aggr count(distinct src_endpoint.ip) by category_uid

Example 11: Generates a sparkline of hourly OCSF event counts per product name, visualizing activity trends for each security source in a compact inline chart.

Copy 
ocsf | aggr sparkline(count(class_name), 1h) as sparkline_action by metadata.product.name