Functions in Analytics

This section describes the functions that are used in Analytics (Rules and Queries).

Type	Functions
Aggregation Functions	AVG, COUNT, COUNT DISTINCT, FIRST, LAST, MAX, MEDIAN, MIN, MODE, PctChange, Pctile95, PCTILE, STDDEV, SUM, VARIANCE
CMDB Lookup Function	DeviceToCMDBAttr
Conversion Functions	LOG, TO_DOUBLE, TO_INTEGER, TO_STRING
Date Conversion Functions	DayOfWeek, HourOfDay
Evaluate and Set Function	IF
Extraction Function	EXTRACT
LookupTable Functions	LookuptableGet, LookupTableHas
String Manipulation Functions	LEN, LTRIM, REPLACE, RTRIM, SUB_STR, TO_LOWER, TO_UPPER, TRIM
Time Window Functions	EMA, SMA

Aggregation Functions

• AVG Function
• COUNT Function
• COUNT DISTINCT Function
• FIRST Function
• LAST Function
• MAX Function
• MEDIAN Function
• MIN Function
• MODE Function
• PctChange Function
• Pctile95 Function
• PCTILE Function
• STDDEV Function
• SUM Function
• VARIANCE Function

AVG Function

Description

AVG(<eventAttribute>) returns the average of the values of the numerical field in all events matching the query criteria.

Scope

AVG() is available in EventDB, ClickHouse, Elasticsearch and Rules. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.

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:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions, Conversion Functions
  • Only LEN allowed in String Manipulation Functions
  • LOG not allowed in Conversion Functions
• In ClickHouse, these nested function categories are allowed:
  • Evaluate and Set Function, String Manipulation Functions, Conversion Functions, Date Conversion Functions
  • Only LEN allowed in String Manipulation Functions

Example

Event Type	Source IP	Sent Bytes
E1	10.1.1.1	10
E2	10.1.1.1	20
E3	10.1.1.2	30

If you choose Source IP and SUM(Sent Bytes) in Group By and Display Fields:

Source IP	SUM(Sent Bytes)
10.1.1.1	15.5
10.1.1.2	30

COUNT Function

Description

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 the query result.

Scope

• COUNT(*) is available for EventDB, ClickHouse, Elasticsearch queries and rules.
• COUNT(<eventAttribute>) is only available for ClickHouse and Elasticsearch queries from release 7.0.0 onwards.
• Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.

Syntax

• COUNT(*)
• COUNT(<eventAttribute>)
• COUNT(Function(<eventAttribute>))
• In Elasticsearch only these nested function categories are allowed:
  • Extraction Function e.g. COUNT(EXTRACT<..>)
• In ClickHouse, only these nested function categories are allowed:
  • Extraction Function e.g. COUNT(EXTRACT<..>)

Example

Event Type	Source IP	User
E1	10.1.1.1	Bob
E2	10.1.1.1
E3	10.1.1.2	Bob

COUNT(Event Type) = 3

COUNT(Source IP) = 3

COUNT(User) = 2

COUNT(*) = 3

COUNT DISTINCT Function

Description

COUNT DISTINCT(<eventAttribute >) returns the number of distinct occurrences of the event attribute in the query result

Scope

COUNT DISTINCT is available in EventDB, ClickHouse, Elasticsearch queries and rules. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.

Syntax

• COUNT (DISTINCT <eventAttribute>)
• COUNT(DISTINCT Function(<eventAttribute>))
• In Elasticsearch these nested function categories are allowed:
  • Extraction Function e.g. COUNT(DISTINCT EXTRACT<..>)
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function e.g. COUNT(DISTINCT EXTRACT<..>)

Example

Event Type	Source IP	User
E1	10.1.1.1	Bob
E2	10.1.1.1	Alice
E3	10.1.1.2	Bob

COUNT DISTINCT (Event Type) = 3

COUNT DISTINCT (Source IP) = 2

COUNT DISTINCT (User) = 2

FIRST Function

Description

FIRST(<eventAttribute>) returns the value of the <eventAttribute> in the event with earliest Event Receive Time in the query result.

Note that for every event, Event Receive Time attribute is set to the time at which the event was first received at any FortiSIEM node (Collector, Worker or Supervisor).

Scope

FIRST() is available in EventDB, ClickHouse and Elasticsearch.

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

Example

Event Receive Time	Event Type	User	Login Time
T1	Login Success	Alice	T5
T1+1	Login Success	Bob	T6
T1+2	Login Success	Bob	T7
T1+3	Login Success	Carl	T6

If you choose User in Group By and Display Fields:

User	FIRST(Login Time)
Alice	T5
Bob	T6
Carl	T6

LAST Function

Description

LAST(<eventAttribute>) returns the value of the <eventAttribute> in the event with latest Event Receive Time in the query result.

Note that for every event, Event Receive Time attribute is set to the time at which the event was first received at any FortiSIEM node (Collector, Worker or Supervisor).

Scope

LAST() is available in EventDB, ClickHouse and Elasticsearch for all releases.

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

Example

Time	Event Type	user	loginTime
T1+1	Login Success	Bob	T6
T1+2	Login Success	Bob	T7
T1+3	Login Success	Carl	T6

With a Group By on user:

user	LAST(loginTime)
Alice	T5
Bob	T7
Carl	T6

MAX Function

Description

MAX(<eventAttribute>) returns the maximum value of event Attribute in the query result.

Scope

MAX() is available in EventDB, ClickHouse, Elasticsearch and Rules. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.

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:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions, Conversion Functions
  • Only LEN allowed in String Manipulation Functions
  • LOG not allowed in Conversion Functions
• In ClickHouse, these nested function categories are allowed:
  • Evaluate and Set Function, String Manipulation Functions, Conversion Functions, Date Conversion Functions
  • Only LEN allowed in String Manipulation Functions

Example

Event Type	Source IP	Sent Bytes
E1	10.1.1.1	10
E2	10.1.1.1	20
E3	10.1.1.2	30

If you choose Source IP and SUM(Sent Bytes) in Group By and Display Fields:

Source IP	SUM(Sent Bytes)
10.1.1.1	20
10.1.1.2	30

MEDIAN Function

Description

MEDIAN(<eventAttribute>) returns the value lying at the midpoint of the frequency distribution of the values of the event Attribute in the query result. In other words, it is the middle number in a sorted list of the values.

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.

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:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions, Conversion Functions
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions, Conversion Functions, Date Conversion Functions

Example

Event Receive Time	Host Name	CPU Util	Memory Util
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(CPU Util) = 0.03 (average of two midpoint values – 0.04 and 0.02)

MEDIAN(Memory Util) = 0.615

MIN Function

Description

MIN(<eventAttribute>) returns the minimum value of event Attribute in the query result.

Scope

MIN() is available in EventDB, ClickHouse, Elasticsearch and Rules. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.

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:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions, Conversion Functions
  • Only LEN allowed in String Manipulation Functions
  • LOG not allowed in Conversion Functions
• In ClickHouse, these nested function categories are allowed:
  • Evaluate and Set Function, String Manipulation Functions, Conversion Functions, Date Conversion Functions
  • Only LEN allowed in String Manipulation Functions

Example

Event Type	Source IP	Sent Bytes
E1	10.1.1.1	10
E2	10.1.1.1	20
E3	10.1.1.2	30

If you choose Source IP and SUM(Sent Bytes) in Group By and Display Fields:

Source IP	SUM(Sent Bytes)
10.1.1.1	10
10.1.1.2	30

MODE Function

Description

MODE(<eventAttribute>) returns the most frequently appearing value of the event Attribute in the query result.

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.

Syntax and Restrictions

• MODE(<eventAttribute>) or MODE (Function(<eventAttribute>))
• In Elasticsearch these nested function categories are allowed:
  • Extraction Function, Evaluate and Set Function
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, Evaluate and Set Function, Date Conversion Function
• Display Conditions (i.e. SQL HAVING type of operation) is not supported.
• Trend displayed only if the argument in MODE(X) is a numeric value.

Example

Use Case - Identify the most common source IP address for each user

Event Receive Time	Event Type	User	Source IP
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
T5	Login Success	Bob	10.1.1.4

If you choose User in Group By and Display Fields:

User	MODE(Source IP)
Alice	10.1.1.1
Bob	10.1.1.2
Carl	10.1.1.3

PctChange Function

Description

PctChange(<eventAttribute >) returns the percentage change of < eventAttribute > from the first event and the last event in the query result.

Scope

Available for EventDB, ClickHouse and Elasticsearch queries.

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:
  • Evaluate and Set Function, Conversion Functions

Example

Consider the following set of events matching the query conditions:

Raw Data

Event Receive Time (ascending)	Host Name	Disk Name	Disk Util
T1	Host1	D:\	10
T2	Host1	D:\	20
T3	Host1	D:\	30
T4	Host1	D:\	40

Query Result for the time window [T1,T4]:

Host Name	Disk Name	PctChange (Disk Util)
Host1	D:\	300%

Pctile95 Function

Description

Pctile95(<eventAttribute>) returns the 95th percentile value of the <eventAttribute> in the query result.

Scope

Available for EventDB, ClickHouse and Elasticsearch queries.

Syntax

• Pctile95(<eventAttribute) - eventAttribute must be a numerical type.
• In Elasticsearch these nested function categories are allowed:
  • Extraction Function, String Manipulation Functions, Conversion Functions
• In ClickHouse, these nested function categories are allowed:
  • String Manipulation Functions, Conversion Functions

Example

Event Receive Time	Host Name	Interface Name	Interface Util
T1	Host1	Wan	2.58
T2	Host1	Wan	0.00
T3	Host1	Wan	0.04
T4	Host1	Wan	0.02

Query Result for the time window [T1,T4]:

Host Name	Interface Name	Pctile95(Interface Util)
Host1	Wan	2.2

PCTILE Function

Description

PCTILE(N,<eventAttribute>) returns Nth percentile value of the <eventAttribute> in the query result. N is between 0 and 100. This generalizes the Pctile95() function.

Scope

Available for ClickHouse and Elasticsearch queries from release 7.0.0 onwards.

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:
  • Extraction Function, String Manipulation Functions, Conversion Functions
• In ClickHouse, these nested function categories are allowed:
  • String Manipulation Functions (LEN), Conversion Functions

Example

Event Receive Time	Host Name	Interface Name	Interface Util
T1	Host1	Wan	2.58
T2	Host1	Wan	0.00
T3	Host1	Wan	0.04
T4	Host1	Wan	0.02

Query Result for the time window [T1,T4]:

Host Name	Interface Name	PCTILE(95,Interface Util)
Host1	Wan	2.2

Host Name	Interface Name	PCTILE(80,Interface Util)
Host1	Wan	1.06

STDDEV Function

Description

STDDEV(<eventAttribute>) returns the standard deviation of the values of eventAttribute in the query result. FortiSIEM calculates populationstandard deviation. STDDEV(<eventAttribute>) is the square root of VARIANCE(<eventAttribute>) defined below.

Scope

Works for ClickHouse and Elasticsearch queries from release 7.0.0 onwards.

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:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions, Conversion Functions
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions, Conversion Functions, Date Conversion Functions

Example

Event Receive Time	Host Name	CPU Util	Memory Util
T1	Host1	20	63
T2	Host1	35	65
T3	Host1	50	66
T4	Host1	60	70

If you choose Host Name in Group By and Display Fields:

Host Name	STDDEV(CPU Util)	STDDEV(Memory Util)
Host1	15.15	2.55

SUM Function

Description

SUM(<eventAttribute >) returns the sum of the values of the numerical event Attribute in the query result.

Scope

SUM is available in EventDB, ClickHouse, Elasticsearch and Rules. Nesting capability is available in ClickHouse and Elasticsearch from release 7.0.0 onwards.

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:
  • All Extraction Functions, All Evaluate and Set Functions
  • Only LEN allowed in String Manipulation Functions
  • All Conversion Functions except LOG
• In ClickHouse, these nested function categories are allowed:
  • All Extraction Functions, All Evaluate and Set Functions
  • Only LEN allowed in String Manipulation Functions
  • All Conversion Functions except LOG

Example

Event Type	Source IP	Sent Bytes
E1	10.1.1.1	20
E2	10.1.1.1	20
E3	10.1.1.2	30

If you choose Source IP and SUM(Sent Bytes) in Group By and Display Fields:

Source IP	SUM(Sent Bytes)
10.1.1.1	40
10.1.1.2	30

VARIANCE Function

Description

VARIANCE(<eventAttribute>) returns the variance of the values of the eventAttribute in the query result. FortiSIEM calculates population variance.

Scope

Works for ClickHouse and Elasticsearch queries from release 7.0.0 onwards.

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:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions, Conversion Functions
• In ClickHouse, these nested function categories are allowed:
  • Evaluate and Set Function, String Manipulation Functions, Conversion Functions, Date Conversion Functions

Example

Event Receive Time	Host Name	CPU Util	Memory Util
T1	Host1	20	63
T2	Host1	35	65
T3	Host1	50	66
T4	Host1	60	70

If you choose Host Name in Group By and Display Fields:

Host Name	VARIANCE(cpuUtil)	VARIANCE(memUtil)
Host1	229.69	6.5

CMDB Lookup Function

• DeviceToCMDBAttr Function

DeviceToCMDBAttr Function

Description

DeviceToCMDBAttr(<deviceId>,<cmdbAttribute>) returns the value of <cmdbAttribute> for the device specified by <deviceId>. The device must be present in CMDB PostgreSQL database.

Scope

Available for EventDB, ClickHouse and Elasticsearch queries and Rules.

Syntax

• For simple CMDB Attributes that are attached to a device, the syntax is: DeviceToCMDBAttr(<deviceId >,<cmdbAttr>), where deviceId can be any event attribute that can be used by FortiSIEM can look up the device in CMDB. Common examples are device name and Ip fields, e.g. hostName, hostIpAddr, srcName, srcIpAddr, destName, destIpAddr etc. Note that cmdbAttr are defined in Admin > Device Support > Custom Properties.
• Sometimes CMDB Attributes are attached to an interface or a disk of a device. In this case the syntax is: DeviceToCMDBAttr(<deviceId >,<deviceAttr>,<cmdbAttr>), where
  • deviceId can be any event attribute that can be used by FortiSIEM can look up the device in CMDB. Common examples are device name and Ip fields, e.g. hostName, hostIpAddr, srcName, srcIpAddr, destName, destIpAddr etc.
  • deviceAttr is Device Attribute: intfName, diskName
  • cmdbAttr are defined in Admin > Device Support > Custom Properties.
    Examples:
    • DeviceToCMDBAttr(hostIpAddr,intfName,IntfErrPctThreshCrit))
    • DeviceToCMDBAttr(hostIpAddr,diskName,DiskSpaceUtilThreshCrit)

Example

CMDB:

Device Name	country	intfName	NetIntfUtilThreshCrit
Host1	U.S.A	Intf1	75
Host2	France	Intf1	90
Host2	France	Intf2	75

Query Results:

phRecvTime	eventType	hostName	intfName	intfUtil	DeviceToCMDBAttr (hostname, city)	DeviceToCMDBAttr( hostName, intfName, NetIntfUtilThreshCrit)
T1	Intf_Usage	Host1	Intf1	40	U.S.A	75
T2	Intf_Usage	Host2	Intf1	80	France	90
T2	Intf_Usage	Host2	Intf2	60	France	75
T3	Intf_Usage	Host3	Intf1		<No match>	<No match>

Conversion Functions

• LOG Function
• TO_DOUBLE Function
• TO_INTEGER Function

• TO_STRING Function

LOG Function

Description

LOG(<eventAttribute>, <base>) calculates the log of a numeric <eventAttribute> to its <base>.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• LOG(<eventAttribute>, <base>)
  • <eventAttribute> is a numeric eventAttribute.
  • <base> is optional and default is 10
• LOG(Function(<eventAttribute>), <base>)
  • Function must return a numeric value.
  • <base> is optional and default is 10.
• Elasticsearch does not support nested functions.
• In ClickHouse, these nested function categories are allowed:
  • Aggregation Functions, Evaluate and Set Function, Conversion Functions

Example

Host Name	recvBytes64	LOG(recvBytes64,10)	LOG(recvBytes64,"e")
Host1		6.67	15.36
Host2		7.35	16.73
Host3		2.66	6.12

TO_DOUBLE Function

Description

TO_DOUBLE(<eventAttribute>) converts a string valued event attribute X to a double number.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• TO_ DOUBLE (<eventAttribute>)
  • <eventAttribute> is a string valued eventAttribute or a Function of an eventAttribute that returns a string.
• TO_ DOUBLE (FUNCTION(<eventAttribute>))
  • Function must return a STRING value.
• Elasticsearch does not support nested functions.
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, String Manipulation Functions, Conversion Functions

Example

field	TO_DOUBLE(field)
"1234.56"	1234.56
"-1234.56"	-1234.56

TO_INTEGER Function

Description

TO_INTEGER(<eventAttribute>, <base>) converts a string valued <eventAttribute> to a integer with <base>. <base> is optional and default is 10.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• TO_ INTEGER (<eventAttribute>, <base>)
  • <eventAttribute> is a string valued eventAttribute or a Function of an eventAtribute that returns a string.
  • <base> is an integer. It is optional and default is 10.
• TO_ INTEGER (FUNCTION(<eventAttribute>), <base>)
  • Function must return a STRING value.
  • <base> is an integer. It is optional and default is 10.
• <eventAttribute> has to be string representation of an integer.
• If <base> is 2 or 16, X must be a string representation of a positive integer.
• In Elasticsearch, no nested function categories are allowed.
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, String Manipulation Functions, Conversion Functions

Example

field	TO_INTEGER(field,10)	TO_INTEGER(field,2)	TO_NUMBER(field,16)
"1000"	1000	1111101000	3E8
"1234"	1234	10011010010	4D2

TO_STRING Function

Description

TO_STRING(<eventAttribute>) converts an input <eventAttribute> to a string. If the <eventAttribute> is an integer, then it reformats the number as a string. If the <eventAttribute> is an Boolean value, then it returns "True" of "False".

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• TO_STRING(<eventAttribute>)
  • <eventAttribute> is a numeric eventAttribute, or a Function returning a number, or a Boolean expression.
  • Return value:
    • If the <eventAttribute> is an integer, then it reformats the number as a string.
    • If the <eventAttribute> is a Boolean value, then it returns "True" of "False".
• TO_STRING(FUNCTION(<eventAttribute>))
  • Function must return a numeric value or a Boolean expression.
  • Return value:
    • If the <eventAttribute> is an integer, then it reformats the number as a string.
    • If the <eventAttribute> is an Boolean value, then it returns "True" of "False".
• Elasticsearch does not support nested functions.
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, Evaluate and Set Function, Conversion Functions

Example

attr1	TO_STRING(attr1)
1234	"1234"
1234.56	"-1234.56"

Date Conversion Functions

• DayOfWeek Function
• HourOfDay Function

DayOfWeek Function

Description

DayOfWeek(<eventAttribute>) returns the day of week in the DATE valued <eventAttribute>. 0 is returned for Sunday and 6 for Saturday.

Scope

• DayOfWeek() is available in EventDB, ClickHouse and Elasticsearch queries and rules.
• DayOfWeek() can be used in Filter By and Group By.

Syntax

• DayOfWeek (<eventAttribute>)
  • <eventAttribute> type must be DATE.
  • Returned values are:
    • 0 for Sunday
    • 1 for Monday
    • 2 for Tuesday
    • 3 for Wednesday
    • 4 for Thursday
    • 5 for Friday
    • 6 for Saturday

Example

Event Receive Time	Event Type	DayOfWeek(Event Receive Time)
Monday May 15, 10:37AM	Login-Success	1
Tuesday May 16, 7:37AM	Login-Success	2

HourOfDay Function

Description

HourOfDay (<eventAttribute>) returns the hour of day in the DATE valued argument. For times between 12:00AM and 12:59AM, HourOfDay() returns 0; between 1:00AM and 1:59AM, HourOfDay() returns 1, etc...

Scope

HourOfDay() is available in EventDB, ClickHouse and Elasticsearch Queries and Rules.

Syntax

• HourOfDay (<eventAttribute>) and eventAttribute type must be DATE.

Example

Event Receive Time	Event Type	HourOfDay(Event Receive Time)
Monday May 15, 10:37AM	Login-Success	10
Monday May 15, 7:37AM	Login-Success	19

Evaluate and Set Function

• IF Function

IF Function

Description

IF(<expression>,<trueValue>,<falseValue>) evaluates the <expression> and returns <trueValue> if <expression> is true, and <falseValue> if the <expression> is false.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• IF(<expression>,<trueValue>,<falseValue>)
  • <expression> is a Boolean expression that evaluates to true or false. Currently expression can only be the following expressions joined by AND/OR: e.g. <eventAttribute><Operator><ConstantValue>.
  • <trueValue> is returned when <expression> evaluates to true – can be a string or an integer.
  • <falseValue> is returned when <expression> evaluates to false – can be a string or an integer.
• Nested functions is not supported for IF.

Example

Time	EventvType	User	IF(Event Type=" Login-Success",1,0)	IF(Event Type=" Login-Failure",1,0)
T1	Login-Success	User1	1	0
T2	Login-Success	User2	1	0
T3	Login-Failure	User1	0	1
T4	Login-Failure	User1	0	1
T5	Login-Success	User1	1	0

If you group by on User and then apply aggregate on the IF function, then you get the following result:

User	SUM(IF(eventType=" Login-Success",1,0))	SUM(IF(eventType=" Login-Failure",1,0
User1	2	2
User2	1	0

Extraction Function

• EXTRACT Function

EXTRACT Function

Description

EXTRACT(<eventAttribute>,<pattern>) extracts a value from <eventAttribute> after applying regular expression <pattern>, and then returns the extracted value. The returned type is determined from the regular expression. The EXTRACT function helps to parse new fields from the historical raw log which the parser may have missed.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards. Can extract only one value. Multiple extractions not supported.

Syntax

• EXTRACT(<eventAttribute>,<pattern>)
  • <eventAttribute> is a string valued eventAttribute.
  • <pattern> is a regular expression pattern for value extraction.
• EXTRACT(Function(<eventAttribute>),<pattern>)
  • Function returns a string value.
  • <pattern> is a regular expression pattern for value extraction.
• Elasticsearch does not support nested functions.
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, String Manipulation Functions, Evaluate and Set Function, Conversion Functions

Example

rawEventMsg	EXTRACT(rawEventMsg,".*\[recvBytes64\]=(\d+).*" )
<134>Sep 19 17:55:01 172.30.52.10 java: [PH_DEV_MON_VM_NET_INTF_UTIL]: [eventSeverity]=PHL_INFO,[vmName]=CentOS7-7.2.1, [recvPkts64]=0, [sentPkts64]=0,[recvBytes64]=23000, [sentBytes64]=54999	23000
<134>Sep 19 17:55:01 172.30.52.10 java: [PH_DEV_MON_VM_NET_INTF_UTIL]: [eventSeverity]=PHL_INFO,[vmName]=CentOS7-7.2.1, [recvPkts64]=0, [sentPkts64]=0,[recvBytes64]= 368640, [sentBytes64]=54999	368640

Lookup Table Functions

• LookupTableGet Function
• LookupTableHas Function

LookupTableGet Function

Description

LookupTableGet (<tableName>,<eventAttribute>,<tableColumn>) searches the Lookup Table <tableName> and matches the values of its key columns with <eventAttribute>. If a matching row is found, then it returns the <tableColumn> from that row. If the Lookup Table has multiple keys, then corresponding event attributes must be specified.

LookupTables are defined in Resources tab and details on how to populate them can be found here.

Scope

Available for EventDB, ClickHouse and Elasticsearch queries and Rules.

Syntax

• LookupTableGet (<tableName>,<eventAttribute>,<tableColumn>)
• The number of eventAttributes in the argument match the key fields in the Lookup Table.

Example

LookupTable: UserLoc

User (Key)	Country	Department
Bob	U.S.A	Finance
Alice	Germany	Engineering
John	U.S.A	Marketing

The following table shows the LookuptableGet function applied to events.

Event receive Time	Event Type	User	LookupTableGet(UserLoc, User, Country)	LookupTableGet(UserLoc, User,Department)
T1	Login-Success	John	U.S.A	Marketing
T2	Login-Failure	Alice	Germany	Engineering
T3	Login-Success	Alice	Germany	Engineering

Notes:

• User field in events is matched with the User field in UserLoc Lookup Table.
• LookupTableGet(UserLoc, User, Country) returns the Country field for the matching row in UserLoc Lookup Table.
• LookupTableGet(UserLoc, User, Department) returns the Department field for the matching row in UserLoc Lookup Table.

LookupTableHas Function

Description

LookupTableHas (<tableName>,<eventAttribute>) searches the Lookup Table <tableName> and

• Returns 1 if there is a row in Lookup table <tableName> where the key field(s) match the specified <eventAttribute>.
• Returns 0 if there is no match.

If <tableName> has multiple Keys, then exactly same number of <eventAttributes> must be specified.

LookupTables are defined in Resources tab and details on how to populate them can be found here.

Scope

Available for EventDB, ClickHouse and Elasticsearch queries and Rules.

Syntax

• LookupTableHas (<tableName>,<eventAttribute>)
• The number of eventAttributes must match the key fields in the Lookup Table.

Example

LookupTable: LoginCountry

User (Key)	Country (Key)
Bob	U.S.A
Alice	Germany
John	U.S.A

The following table shows the LookuptableGet function applied to events:

Event receive Time	Event Type	User	Source Geo Country	LookupTableHas(LoginCountry, User, Source Geo Country)
T1	Login-Success	John	U.S.A	1
T2	Login-Failure	Alice	France	0
T3	Login-Success	Alice	Germany	1

Notes:

• Since LoginCountry LookupTable has 2 Keys:
  • User field in events is matched with the User field in UserLoc Lookup Table.
  • Source Geo Country field in events is matched with the Country field in UserLoc Lookup Table.
• LookupTableGet(LoginCountry, User, Source Geo Country) returns
  • 1 if BOTH User field in events matches the User field in UserLoc Lookup Table. And Source Geo Country field in events is matched with the Country field in UserLoc Lookup Table.

String Manipulation Functions

• LEN Function
• LTRIM Function
• REPLACE Function
• RTRIM Function
• SUB_STR Function
• TO_LOWER Function
• TO_UPPER Function
• TRIM Function

LEN Function

Description

LEN(<eventAttribute>) returns the length of a string valued <eventAttribute>.

Scope

• Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.
• In Elasticsearch, no nested function categories are allowed.
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions

Syntax

• LEN(<eventAttribute>) - eventAttribute must be a STRING type.
• LEN(Function(<eventAttribute>)) - The Function must return a STRING value.

Example

Event Type	Raw Event Log	LEN(Raw Event Log)
E1	This is a short event	21
E2	This is a medium event	23
E3	This is a very very long event	30

LTRIM Function

Description

LTRIM(<eventAttribute>,<chars>) returns the string valued <eventAttribute> after trimming characters in <chars> from left side. If <chars> is not specified, then only spaces and tabs are removed.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• LTRIM(<eventAttribute >,<chars>)
  • <eventAttribute> must be a STRING type.
  • <chars> is a list of characters.
• LTRIM(Function(<eventAttribute>), <chars>)
  • Function must return a STRING value.
  • <chars> is a list of characters.
• In Elasticsearch, no nested function categories are allowed.
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions

Example

user	LTRIM(user)	LTRIM(user, "FORTINET\\")
" user1"	"user1"	" user1"
"FORTINET\user3"	" "FORTINET\user3"	"user3"

REPLACE Function

Description

REPLACE(<eventAttribute>,<pattern>,<replaceWith>) returns a string formed by substituting string <replaceWith> for every occurrence of regex string <pattern> in string valued <eventAttribute>.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• REPLACE(<eventAttribute>,<pattern>,<replaceWith>))
  • <eventAttribute> must be a STRING type.
  • <pattern> must be a regular expression and
  • <replaceWith> is a STRING type.
• REPLACE (Function(<eventAttribute>),<pattern>,<replaceWith>)
  • Function must return a STRING value.
  • <pattern> must be a regular expression and
  • <replaceWith> is a STRING type.
• In Elasticsearch, no nested function categories are allowed.
• In ClickHouse, these nested function categories are allowed:
  • Extraction Functions, Evaluate and Set Function, String Manipulation Functions

Example

user	REPLACE(user, "FORTINET\\", "")	REPLACE(user, "Administrator", "admin")
Bob	Bob	Bob
FORTINET\John	John	John
alice@fortinet.com	alice@fortinet.com	alice@fortinet.com
Administrator	Administrator	admin

RTRIM Function

Description

RTRIM(<eventAttribute>,<chars>) returns the string valued <eventAttribute> after trimming characters in <chars> from the right side. If <chars> is not specified, then only spaces and tabs are removed.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• RTRIM(<eventAttribute >,<chars>)
  • <eventAttribute> must be STRING type.
  • <chars> is a list of characters.
• RTRIM(Function(<eventAttribute>), <chars>)
  • Function must return a STRING value.
  • <chars> is a list of characters.
• In Elasticsearch, no nested function categories are allowed.
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions

Example

user	RTRIM(user)	RTRIM(user, "@fortinet.com")
"user1 "	"user1"	"user1 "
"user2@fortinet.com"	"user2@fortinet.com"	"user2"

SUB_STR Function

Description

SUB_STR(<eventAttribute>,<start>,<num>) returns a substring of string valued <eventAttribute>, starting at the index specified by <start> with the number of characters specified by <num>. If <num> is not specified, then the string <eventAttribute> starting at <start> to the end is returned.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• SUB_STR(<eventAttribute>,<start>[,<num>])
  • <eventAttribute> must be STRING type.
  • <start> is an integer between 0 and the length of <eventAttribute>.
  • <num> is an integer and must be less than length of <eventAttribute> minus <start>.
• SUB_STR(Function(<eventAttribute>),<start>[,<num>])
  • Function must return a STRING value.
  • <start> is an integer between 0 and the length of <eventAttribute>.
  • <num> is an integer and must be less than length of <eventAttribute> minus <start>.
• In Elasticsearch, no nested function categories are allowed.
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions

Example

User	SUB_STR(User,2)	SUB_STR(User,2,2)
aabbcc	bbcc	bb
123456	3456	12

TO_LOWER Function

Description

TO_LOWER(<eventAttribute >) changes the case of a string valued <eventAttribute> to all lower case.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• TO_LOWER (<eventAttribute>) - eventAttribute must be a STRING type.
• TO_LOWER (Function(<eventAttribute>)) - The Function must return a STRING value.
• In Elasticsearch, no nested function categories are allowed.
• In ClickHouse, the following nested function categories are allowed:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions

Example

Event Type	User	TO_LOWER(User)
E1	Bob	bob
E2	ACME/John	acme/john
E3	ALICE	alice

TO_UPPER Function

Description

TO_UPPER(<eventAttribute >) changes the case of a string valued <eventAttribute> to all upper case.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• TO_UPPER (<eventAttribute>) - eventAttribute must be a STRING type.
• TO_UPPER (Function(<eventAttribute>)) - The Function must return a STRING value.
• In Elasticsearch, no nested function categories are allowed.
• In ClickHouse, these nested function categories are allowed:
  • Extraction Function, Evaluate and Set Function, String Manipulation Functions

Example

Event Type	User	TO_UPPER(User)
E1	Bob	BOB
E2	ACME/John	ACME/JOHN
E3	ALICE	ALICE

TRIM Function

Description

TRIM(<eventAttribute>,<chars>) returns the string valued <eventAttribute> after trimming characters in <chars> from both sides. If <chars> is not specified, then only spaces and tabs are removed.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Syntax

• TRIM(<eventAttribute >,<chars>)
  • <eventAttribute> must be a STRING type.
  • <chars> is a list of characters.
• TRIM(Function(<eventAttribute>), <chars>)
  • Function must return a STRING value.
  • <chars> is a list of characters.
• In Elasticsearch, no nested function categories are allowed.
• In ClickHouse, these nested function categories are allowed:
  • Extraction Functions, Evaluate and Set Function, String Manipulation Functions

Example

user	TRIM(user)	TRIM(user, "FORTINET\\")
" user1"	"user1"	" user1"
"user2 "	"user2"	"user2 "
"FORTINET\user3"	" "FORTINET\user3"	"user3"

Time Window Functions

• EMA Function
• SMA Function

EMA Function

Description

EMA(N,<eventAttribute>) returns Exponential Moving Average over N previous values of the <eventAttribute> in the query result.

Scope

Works for ClickHouse and Elasticsearch queries from release 7.0.0 onwards.

Syntax

• EMA(N, <eventAttribute>) - eventAttribute must be a numerical type.
• EMA(N,Function(<eventAttribute>))) – The Function must return a numerical value and cannot be an aggregate attribute.
• Any query using EMA needs to have one time aggregation field. Supported time aggregation fields are:
  • Hourly (Event Receive Hour)
  • Daily (Event receive Date)
  • Weekly (Event Receive Week)
  • Monthly(Event Receive Month)
• In Elasticsearch these nested function categories are allowed:
  • Extraction Function, String Manipulation Functions
• In ClickHouse, these nested function categories are allowed:
  • Conversion Functions, String Manipulation Functions
• Display Condition (i.e. HAVING in SQL Language) is not supported

Example

Raw Data

Event Receive Time	Host Name	CPU Util
Apr 19, 2023, 12:00:00 PM	Host1	3.00%
Apr 19, 2023, 12:15:00 PM	Host1	2.37%
Apr 19, 2023, 12:30:00 PM	Host1	2.68%
Apr 19, 2023, 12:45:00 PM	Host1	2.72%
Apr 19, 2023, 13:00:00 PM	Host1	2.55%
Apr 19, 2023, 13:15:00 PM	Host1	2.43%
Apr 19, 2023, 13:30:00 PM	Host1	2.50%
Apr 19, 2023, 13:45:00 PM	Host1	2.74%
Apr 19, 2023, 14:00:00 PM	Host1	2.64%
Apr 19, 2023, 14:15:00 PM	Host1	3.74%
Apr 19, 2023, 14:30:00 PM	Host1	2.12%
Apr 19, 2023, 14:45:00 PM	Host1	3.84%

EMA Query Result:

Event Receive Hour	Host Name	EMA(2,CPU Util)
Apr 19, 2023, 12:00:00 PM	Host1	2.69
Apr 19, 2023, 13:00:00 PM	Host1	2.60
Apr 19, 2023, 14:00:00 PM	Host1	2.92

SMA Function

Description

SMA(N,<eventAttribute>) returns Simple Moving Average over N previous values of the numerical valued <eventAttribute> among all events matching the query criteria.

Scope

Available for ClickHouse and Elasticsearch queries from release 7.0.0 onwards.

Syntax

• SMA(N, <eventAttribute>) - eventAttribute must be a numerical type.
• SMA(N,Function(<eventAttribute>))) – The Function must return a numerical value and cannot be an aggregate attribute.
• Any query using SMA must have one of the time aggregation field. Supported time aggregation fields are:
  • Hourly (Event Receive Hour)
  • Daily (Event receive Date)
  • Weekly (Event Receive Week)
  • Monthly(Event Receive Month)
• In Elasticsearch these nested function categories are allowed:
  • Extraction Function, String Manipulation Functions
• In ClickHouse, these nested function categories are allowed:
  • Conversion Functions, String Manipulation Functions
• Display Condition (i.e. HAVING in SQL Language) is not supported.

Example

Raw Data

Event Receive Time	Host Name	CPU Util
Apr 19, 2023, 12:00:00 PM	Host1	3.00%
Apr 19, 2023, 12:15:00 PM	Host1	2.37%
Apr 19, 2023, 12:30:00 PM	Host1	2.68%
Apr 19, 2023, 12:45:00 PM	Host1	2.72%
Apr 19, 2023, 13:00:00 PM	Host1	2.55%
Apr 19, 2023, 13:15:00 PM	Host1	2.43%
Apr 19, 2023, 13:30:00 PM	Host1	2.50%
Apr 19, 2023, 13:45:00 PM	Host1	2.74%
Apr 19, 2023, 14:00:00 PM	Host1	2.64%
Apr 19, 2023, 14:15:00 PM	Host1	3.74%
Apr 19, 2023, 14:30:00 PM	Host1	2.12%
Apr 19, 2023, 14:45:00 PM	Host1	3.84%

SMA Query Result

Event Receive Hour	Host Name	SMA(2, CPU Util)
Apr 19, 2023, 12:00:00 PM	Host1	2.69
Apr 19, 2023, 13:00:00 PM	Host1	2.62
Apr 19, 2023, 14:00:00 PM	Host1	2.85