_agg


The _agg query directive is used to perform aggregations on the result set.

Example: A quick example is given below:

_fetch * from event where $LogID=60 limit 5
_agg count

Here:

1. The _fetch directive retrieves all fields for each event in the event index where $LogID is 60. We limit the result set to the 5 latest events satisfying the where clause. The output is as shown below:

log events for whom the value of logid field is equal to 60

2. In the pipelined query function, the _agg directive uses the count keyword to count the rows in the result set returned by _fetch.

output of the pipelined query function

The _agg query directive lets you:

  • Count rows in a result set
  • Group rows in a result set and perform arithmetic operations (on a field) in each group
  • Group rows in a result set and count the rows in each group
  • Perform arithmetic operations on a field in the entire result set

Syntax

The generic syntax of the _agg directive is as given below:

_agg [function] [$field1, $field2]
function:
count_unique | stat_unique | count | [sum|min|max|avg] [compute field]
stat_unique [$field1, $field2] [sum|min|max|avg] [compute field]
count_unique $field1, $field2…,$field4
count

Here:

  • stat_unique: It performs the sum|min|max|avg operations in each group on the compute field specified in the query. The keywords stand for summation, minimum value, maximum value and average, respectively, of the specified compute field in each group.
  • count_unique: It counts the rows in each group.
  • count: It counts rows in the entire result set.
  • sum/min/max/avg: It gives the summation, minimum value, maximum value and average respectively of the specified compute field in the entire result set.

Stat Unique

The _agg directive uses the stat_unique keyword to perform an aggregation (grouping) on one or more specified fields and give a statistic for each group. These statistics include min, max, sum and avg.

Example 1

Take a look at the example given below:

_fetch * from event where $LogID=60 limit 100
>>_agg stat_unique $DstPort max $EvtLen

Here:

1. The _fetch directive retrieves all fields for each event in the event index where $LogID is 60. We limit the result set to the 100 latest events that satisfy the where clause. The output is as shown below:

log events for whom the value of logid is equal to 60

NOTE: The outputs shown in the screenshot may only be partial outputs in many cases.

2. In the pipelined query function, the _agg directive uses the stat_unique keyword to aggregate and group unique $DstPort (destination port) values and give the maximum $EvtLen (event length) for each group (using the keyword max). The output is as shown below:

aggregate resultset based on destination port

In the result set returned by the _fetch query function, amongst all rows (events) with the destination port 10554, the maximum value of event length was 162. This is highlighted in the image above.

NOTE: You can use the keywords min, sum or average (instead of max) to calculate the minimum value, summation or average respectively of a group.

NOTE: The number of events fetched by the _fetch directive is limited to the duration specified in the Date Selector date selecter icon on the Search page. By default, the duration is the last 24 hours.

Example 2

We can also form groups using more than one field. Take a look at the example given below:

_fetch $LogID, $SrcIP, $DstPort, $EvtLen from event where $LogID=60 limit 100
>>_agg stat_unique $SrcIP, $DstPort max $EvtLen

Here:

1. The _fetch directive retrieves the $LogID, $SrcIP, $DstPort, and $EvtLen fields for each event in the event index where $LogID is 60. We limit the result set to the 100 latest events satisfying the where clause. The output is as shown below:

resultset comprises of those events for wwhich the value of field logid is equal to 60

2. In the pipelined query function, the _agg directive uses the stat_unique keyword to aggregate combinations of the $SrcIP (source IP) and $DstPort (destination port) values and give the maximum $EvtLen (event length) for each group using the keyword max. The output is as shown below:

resultset comprises of events for which unique pair of source ip address and destination port

The destination port 1433 has events from (at least) two source IP addresses (highlighted in the image above). The events in the first combination group of destination port and source IP are such that the maximum length is 162. Similarly, for the second combination group, the maximum event length is 163.

Count unique

The _agg directive can be used to perform an aggregation and count the rows in a group using the count_unique keyword.

Take a look at the example given below:

_fetch * from event where $LogID=60 limit 100
>>_agg count_unique $DstPort

Here:

1. The _fetch directive retrieves all fields for each event in the event index where $LogID is 60. We limit the result set to the 100 latest events that satisfy the where clause. The output is as shown below:

resultset with only those events for which logid is 60

2. In the pipelined query function, the _agg directive uses the count_unique keyword to aggregate unique $DstPort (destination port) values and give the count of rows (events) in each group. The output is as shown below:

aggregated resulset based on unique values for destination port

As seen in the output image above, in the result set returned by the _fetch query function, there are 24 events with the destination port as 67 and only 1 event with destination port as 10554.

Count

The _agg directive can be used to count the rows in the entire result set using the count keyword.

Take a look at the example given below:

_fetch * from event where $LogID=60 limit 100
>>_agg stat_unique $DstPort max $EvtLen
>>_agg count

This example is a continuation of the Example 1 given in the Stat Unique section. The output of the first two query functions was as below:

aggregated resulset based on unique values for destination port with maximum value of event length

In the third query function, _agg uses the count keyword to count the number of rows in the result set above. The output is as shown below:

count of rows

There are 23 rows in the result set.

Arithmetic Functions

Similar to the count keyword, the _agg directive can be used to perform simple arithmetic functions, on a specified compute field in the entire result set, using the sum, min, max and avg keywords.

Take a look at the example given below:

_fetch * from event where $LogID=60 limit 100
>>_agg sum $EvtLen

Here:

1. The _fetch directive retrieves all fields for each event in the event index where $LogID is 60. We limit the result set to the 100 latest events that satisfy the where clause. The output is as shown below:

resultset of the fetch query

2. On the result set returned by _fetch, in a pipeline query, the _agg directive uses the sum keyword to calculate the sum of the values of the $EvtLen column. The output is as shown below:

sum of all the aggregated values of event length field