Aggregate Functions
Details of the following aggregate functions are available.
- AVG
- COUNT
- COUNT DISTINCT
- FIRST
- LAST
- MAX
- MEDIAN
- MIN
- MODE
- PctChange
- PCTILE
- Pctile95
- STDDEV
- SUM
- VARIANCE
AVG Function
AVG(<field>) returns the average of the values of the numerical field in all events matching the query criteria.
Syntax
AVG(<eventAttribute>) - eventAttribute must be a numerical type.
AVG (Function(<eventAttribute>)) – The Function must return a numerical value.
In Elasticsearch, these nested function categories are allowed:
- Conversion Functions, Evaluate-and-Set Function, Extraction Function, String Manipulation Functions
Notes: LOG not allowed in Conversion Functions, only LEN allowed in String Manipulation Functions
In ClickHouse, these nested function categories are allowed:
- Conversion Functions, Date Conversion Functions, Evaluate-and-Set Function, String Manipulation Functions
Note: Only LEN allowed in String Manipulation Functions
Scope
AVG() is available in EventDB, ClickHouse and Elasticsearch. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.
Example
|
eventType |
srcIpAddr |
sentBytes |
|---|---|---|
|
E1 |
10.1.1.1 |
10 |
|
E2 |
10.1.1.1 |
20 |
|
E3 |
10.1.1.2 |
30 |
AVG (sentBytes) = 20
With a Group By on srcIpAddr:
|
srcIpAddr |
AVG(sentBytes) |
|---|---|
|
10.1.1.1 |
15.5 |
|
10.1.1.2 |
30 |
COUNT Function
COUNT(*) returns the number of occurrences of each matched row in a query. COUNT(<eventAttribute>) counts the number of occurrences of the event attribute in a query.
Syntax
COUNT(*) or COUNT(<eventAttribute>) or COUNT(Function(<eventAttribute>))
In Elasticsearch, these nested function categories are allowed:
- Extraction Function
In ClickHouse, these nested function categories are allowed:
- Extraction Function
Scope
COUNT is available in EventDB, ClickHouse and Elasticsearch queries and rules. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.
Example
|
eventType |
srcIpAddr |
user |
|---|---|---|
|
E1 |
10.1.1.1 |
Bob |
|
E2 |
|
Alice |
|
E3 |
10.1.1.2 |
|
COUNT (eventType) = 3
COUNT (srcIpAddr) = 2
COUNT(user) = 2
COUNT DISTINCT Function
COUNT DISTINCT(<field>) returns the number of distinct occurrences of the field in all events matching the query criteria.
Syntax
COUNT (DISTINCT <eventAttribute>), COUNT(DISTINCT Function(<eventAttribute>))
In Elasticsearch, these nested unction categories are allowed:
- Extraction Function
In ClickHouse, these nested function categories are allowed:
- Extraction Function
Scope
COUNT DISTINCT is available in EventDB, ClickHouse and Elasticsearch queries and rules. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.
Example
|
eventType |
srcIpAddr |
user |
|---|---|---|
|
E1 |
10.1.1.1 |
Bob |
|
E2 |
10.1.1.1 |
Alice |
|
E3 |
10.1.1.2 |
Bob |
COUNT DISTINCT (eventType) = 3
COUNT DISTINCT (srcIpAddr) = 2
COUNT DISTINCT (user) = 2
FIRST Function
FIRST(<eventAttribute>) returns the value of the <eventAttribute> in the event with earliestphRecvTime among all events matching the query criteria. Note that for every event, phRecvTime attribute is set to the time at which the event was first received at any FortiSIEM node (Collector, Worker or Supervisor).
Syntax
FIRST (<eventAttribute>) or FIRST (FUNCTION(<eventAttribute>))
In Elasticsearch, nested functions are not allowed.
In ClickHouse, these nested function categories are allowed:
- Extraction Function, Evaluate-and-Set Function
Scope
FIRST() is available in EventDB, ClickHouse and Elasticsearch.
Example
|
Time |
Event Type |
user |
loginTime |
|---|---|---|---|
|
T1 |
Login Success |
Alice |
T5 |
|
T1+1 |
Login Success |
Bob |
T6 |
|
T1+2 |
Login Success |
Bob |
T7 |
|
T1+3 |
Login Success |
Carl |
T6 |
With a group By on user, then
|
user |
FIRST(loginTime) |
|---|---|
|
Alice |
T5 |
|
Bob |
T6 |
|
Carl |
T6 |
LAST Function
LAST(<eventAttribute>) returns the value of the <eventAttribute> in the event with latest phRecvTime among all events matching the query criteria. Note that for every event, phRecvTime attribute is set to the time at which the event was first received at any FortiSIEM node.
Syntax
LAST(<eventAttribute>) or LAST(FUNCTION(<eventAttribute>))
In Elasticsearch, nested functions are not allowed.
In ClickHouse, these nested function categories are allowed:
- Extraction Function, Evaluate-and-Set Function
Scope
LAST() is available in EventDB, ClickHouse and Elasticsearch for all releases.
Example
|
Time |
Event Type |
user |
loginTime |
|---|---|---|---|
|
T1 |
Login Success |
Alice |
T5 |
|
T1+1 |
Login Success |
Bob |
T6 |
|
T1+2 |
Login Success |
Bob |
T7 |
|
T1+3 |
Login Success |
Carl |
T6 |
With a group By on user, then
|
user |
LAST(loginTime) |
|---|---|
|
Alice |
T5 |
|
Bob |
T7 |
|
Carl |
T6 |
MAX Function
MAX(<field>) returns the maximum of the values of the numerical field in all events matching the query criteria.
Syntax
MAX(<eventAttribute>) - eventAttribute must be a numerical type.
MAX (Function(<eventAttribute>)) – The Function must return a numerical value.
In Elasticsearch, these nested function categories are allowed:
- Conversion Functions, Evaluate-and-Set Function, Extraction Function, String Manipulation Functions
Notes: LOG not allowed in Conversion Functions, only LEN allowed in String Manipulation Functions,
In ClickHouse, these nested function categories are allowed:
- Conversion Functions, Date Conversion Functions, Evaluate-and-Set Function, String Manipulation Functions
Note: Only LEN allowed in String Manipulation Functions
Scope
MAX() is available in EventDB, ClickHouse and Elasticsearch. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.
Example
|
eventType |
srcIpAddr |
sentBytes |
|---|---|---|
|
E1 |
10.1.1.1 |
10 |
|
E2 |
10.1.1.1 |
20 |
|
E3 |
10.1.1.2 |
30 |
MAX (sentBytes) = 30
With a Group By on srcIpAddr:
|
srcIpAddr |
MAX(sentBytes) |
|---|---|
|
10.1.1.1 |
20 |
|
10.1.1.2 |
30 |
MEDIAN Function
MEDIAN(<field>) returns the value lying at the midpoint of a frequency distribution of observed values of the field in all events matching the query criteria. In other words, it is the middle number in a sorted, ascending or descending list of the values.
Syntax
MEDIAN(<eventAttribute>) - eventAttribute must be a numerical type.
MEDIAN(Function(<eventAttribute>)) – The Function must return a numerical value.
In Elasticsearch, these nested function categories are allowed:
- Conversion Functions, Evaluate-and-Set Function, Extraction Function, String Manipulation Functions
Notes: LOG not allowed in Conversion Functions, only LEN allowed in String Manipulation Functions
In ClickHouse, these nested function categories are allowed:
- Conversion Functions, Date Conversion Functions, Evaluate-and-Set Function, Extraction Function
Note: Only LEN allowed in String Manipulation Functions
Scope
MEDIAN() is available in ClickHouse and Elasticsearch from release 7.0.0 onwards. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.
Example
|
Time |
hostName |
cpuUtil (%) |
memUtil(%) |
|---|---|---|---|
|
T1 |
Host1 |
2.58 |
1.33 |
|
T2 |
Host1 |
0.00 |
0.07 |
|
T3 |
Host1 |
0.04 |
0.00 |
|
T4 |
Host1 |
0.02 |
1.16 |
MEDIAN(cpuUtil) = 0.66
MEDIAN(memUtil) = 0.64
MIN Function
MIN(<field>) returns the minimum of the values of the numerical field in all events matching the query criteria.
Syntax
MIN(<eventAttribute>) - eventAttribute must be a numerical type.
MIN (Function(<eventAttribute>)) – The Function must return a numerical value.
In Elasticsearch, these nested function categories are allowed:
- Conversion Functions, Evaluate-and-Set Function, Extraction Function, String Manipulation Functions
Notes: LOG not allowed in Conversion Functions, Only LEN allowed in String Manipulation Functions
In ClickHouse, these nested function categories are allowed:
- Conversion Functions, Date Conversion Functions, Evaluate-and-Set Function
Note: Only LEN allowed in String Manipulation Functions
Scope
MIN() is available in EventDB, ClickHouse and Elasticsearch. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.
Example
|
eventType |
srcIpAddr |
sentBytes |
|---|---|---|
|
E1 |
10.1.1.1 |
10 |
|
E2 |
10.1.1.1 |
20 |
|
E3 |
10.1.1.2 |
30 |
SUM (sentBytes) = 10
With a Group By on srcIpAddr:
|
srcIpAddr |
MIN(sentBytes) |
|---|---|
|
10.1.1.1 |
10 |
|
10.1.1.2 |
30 |
MODE Function
MODE(<field>) returns the value that appears most frequently in the field among all events matching the query criteria.
Syntax
MODE(<eventAttribute>) or MODE (Function(<eventAttribute>))
In Elasticsearch, these nested function categories are allowed:
- Evaluate-and-Set Function, Extraction Function
In ClickHouse, these nested function categories are allowed:
- Date Conversion Functions, Evaluate-and-Set Function, Extraction Function
Scope
MODE() is available in ClickHouse and Elasticsearch from release 7.0.0 onwards. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.
Example
Identify the most common source IP address for each user.
|
Time |
Event Type |
user |
srcIpAddr |
|---|---|---|---|
|
T1 |
Login Success |
Alice |
10.1.1.1 |
|
T2 |
Login Success |
Bob |
10.1.1.2 |
|
T3 |
Login Success |
Bob |
10.1.1.2 |
|
T4 |
Login Success |
Carl |
10.1.1.3 |
MODE(srcIpAddr) = 10.1.1.2
MODE(user) = Bob
With a group By on user, then
|
user |
MODE(srcIpAddr) |
|---|---|
|
Alice |
10.1.1.1 |
|
Bob |
10.1.1.2 |
|
Carl |
10.1.1.3 |
Restrictions
- HAVING clause not supported.
- Trend displayed only if X in MODE(X) is a numeric value.
PctChange Function
PctChange(<eventAttribute >) returns the percentage change of the numerical < eventAttribute > from the first event and the last event among all events matching the query criteria.
Syntax
PctChange(<eventAttribute>) - eventAttribute must be a numerical type.
In Elasticsearch, these nested function categories are allowed:
- Conversion Functions
In ClickHouse, these nested function categories are allowed:
- Conversion Functions, Evaluate-and-Set Functions
Scope
Available for EventDB, ClickHouse and Elasticsearch queries.
Example
Consider the following set of events matching the query conditions:
|
phEventRecvTime (ascending) |
hostName |
cpuUtil |
|---|---|---|
|
T1 |
Host1 |
10 |
|
T2 |
Host1 |
20 |
|
T3 |
Host1 |
30 |
|
T4 |
Host1 |
40 |
Then PctChange (cpuUtil) = (40-10)/10 * 100 = 300%
PCTILE Function
PCTILE(N,<field>) returns Nth percentile value of the numeric valued <field> in all events matching the query criteria. N is between 0 and 100. This generalizes the Pctile95() function found in earlier releases.
Syntax
PCTILE (N,<eventAttribute>) - eventAttribute must be a numerical type.
PCTILE (N,Function(<eventAttribute>)) – The Function must return a numerical value.
In Elasticsearch, these nested function categories are allowed:
- Conversion Functions, Extraction Function, String Manipulation Functions
Notes: LOG not allowed in Conversion Functions, only LEN allowed in String Manipulation Functions
In ClickHouse, these nested function categories are allowed:
- Conversion Functions, String Manipulation Functions
Note: Only LEN allowed in String Manipulation Functions
Scope
Available for ClickHouse and Elasticsearch queries from release 7.0.0 onwards.
Example
|
hostName |
diskName |
cpuUtil |
diskUtil |
|---|---|---|---|
|
Host1 |
Disk1 |
2.58% |
20.98% |
|
Host1 |
Disk1 |
0.00% |
19.09% |
|
Host1 |
Disk1 |
0.04% |
3.21% |
|
Host1 |
Disk1 |
0.02% |
1.02% |
PCTILE(95,cpuUtil) = 2.58
PCTILE(95,diskUtil) = 20.98
Pctile95 Function
Pctile95(<field>) returns the 95th percentile value of the numeric valued <field> in all events matching the query criteria.
Syntax
Pctile95(<eventAttribute) - eventAttribute must be a numerical type.
In Elasticsearch, these nested function categories are allowed:
- Conversion Functions, Extraction Function, String Manipulation Functions
Notes: LOG not allowed in Conversion Functions, only LEN allowed in String Manipulation Functions
In ClickHouse, these nested function categories are allowed:
- Conversion Functions, String Manipulation Functions
Note: Only LEN allowed in String Manipulation Functions
Scope
Available for EventDB, ClickHouse and Elasticsearch queries.
Example
|
hostName |
intfName |
intfUtil |
|---|---|---|
|
Host1 |
Wan |
2.58 |
|
Host1 |
Wan |
0.00 |
|
Host1 |
Wan |
0.04 |
|
Host1 |
Wan |
0.02 |
PCTILE(95,intfUtil) = 2.58
STDDEV Function
STDDEV(<field>) returns the standard deviation of the values of the numerical valued field among all events matching the query criteria. STDDEV(<field>) is the square root of VARIANCE(<field>) defined below.
Syntax
STDDEV (<eventAttribute>) - eventAttribute must be a numerical type.
STDDEV (Function(<eventAttribute>)) – The Function must return a numerical value.
In Elasticsearch, these nested function categories are allowed:
- Conversion Functions, Extraction Function, Evaluate-and-Set Function, String Manipulation Functions
Notes: LOG not allowed in Conversion Functions, only LEN allowed in String Manipulation Functions
In ClickHouse, these nested function categories are allowed:
- Conversion Functions, Date Conversion Functions, Evaluate-and-Set Function, Extraction Function, String Manipulation Functions
Note: Only LEN allowed in String Manipulation Functions
Scope
Works for ClickHouse and Elasticsearch queries from release 7.0.0 onwards.
Example
|
Time |
hostName |
cpuUtil |
memUtil |
|---|---|---|---|
|
T1 |
Host1 |
2.58 |
1.33 |
|
T2 |
Host1 |
0.00 |
0.07 |
|
T3 |
Host1 |
0.04 |
0.00 |
|
T4 |
Host1 |
0.02 |
1.16 |
STDDEV(cpuUtil) = 1.1086
STDDEV(memUtil) = 0.6084
SUM Function
SUM(<field>) returns the sum of the values of the numerical field in all events matching the query criteria.
Syntax
SUM(<eventAttribute>) - eventAttribute must be a numerical type.
SUM (Function(<eventAttribute>)) – The Function must return a numerical value.
In Elasticsearch, these nested function categories are allowed:
- Conversion Functions, Evaluate-and-Set Function, Extraction function, String Manipulation Functions
Notes: LOG not allowed in Conversion Functions, only LEN allowed in String Manipulation Functions
In ClickHouse, these nested function categories are allowed:
- Conversion Functions, Date Conversion Functions, Evaluate-and-Set Function, String Manipulation Functions
Note: Only LEN allowed in String Manipulation Functions
Scope
SUM is available in EventDB, ClickHouse and Elasticsearch. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.
Example
|
eventType |
srcIpAddr |
sentBytes |
|---|---|---|
|
E1 |
10.1.1.1 |
10 |
|
E2 |
10.1.1.1 |
20 |
|
E3 |
10.1.1.2 |
30 |
SUM (sentBytes) = 60
With a Group By on srcIpAddr:
|
srcIpAddr |
SUM(sentBytes) |
|---|---|
|
10.1.1.1 |
30 |
|
10.1.1.2 |
30 |
VARIANCE Function
VARIANCE(<field>) returns the variance of the values of the numerical valued <field> among all events matching the query criteria.
Syntax
VARIANCE (<eventAttribute>) - eventAttribute must be a numerical type.
VARIANCE (Function(<eventAttribute>)) – The Function must return a numerical value.
In Elasticsearch, these nested function categories are allowed:
- Conversion Functions, Evaluate-and-Set Function, Extraction Function, String Manipulation Functions
Notes: LOG not allowed in Conversion Functions, only LEN allowed in String Manipulation Functions
In ClickHouse, these nested function categories are allowed:
- Conversion Functions, Date Conversion Functions, Evaluate-and-Set Function, String Manipulation Functions
Note: Only LEN allowed in String Manipulation Functions
Scope
Works for ClickHouse and Elasticsearch queries from release 7.0.0 onwards.
Example
|
Time |
hostName |
cpuUtil |
memUtil |
|---|---|---|---|
|
T1 |
Host1 |
2.58 |
1.33 |
|
T2 |
Host1 |
0.00 |
0.07 |
|
T3 |
Host1 |
0.04 |
0.00 |
|
T4 |
Host1 |
0.02 |
1.16 |
VARIANCE(cpuUtil) = 1.229
VARIANCE(memUtil) = 0.37025
Copyright © 2024 Fortinet, Inc. All Rights Reserved. | Terms of Service | Privacy Policy
