Query Functions

New query functions have been added for advanced analytics. Refer to the tables to see which functions are available for Elasticsearch and ClickHouse, and whether they work with aggregate and/or non-aggregate functions.

Functions are categorized as:

• Aggregate Functions

• Conditional Functions

• Conversion Functions

• Extraction Functions

• String Manipulation Functions

• Time Window Functions

Aggregate Functions

The following aggregate functions are available.

• COUNT

• MEDIAN

• MODE

• PCTILE

• STDDEV

• VARIANCE

Aggregate Function	Works with Elasticsearch Aggregate Functions	Works with Elasticsearch Non-Aggregate Functions	Works with ClickHouse Aggregate Functions	Works with ClickHouse Non-Aggregate Functions
COUNT	Yes	Yes	Yes	Yes
MEDIAN	Yes	No	Yes	No
MODE	Yes	No	Yes	No
PCTILE	No	No	Yes	No
STDDEV	Yes	No	Yes	No
VARIANCE	Yes	No	Yes	No

COUNT

The aggregate COUNT function returns the number of occurrences of a field.

Syntax	Argument	Returns	Return Type
COUNT(X)	X - The field whose number of occurrences need to be determined	How many times X occurred.	Long

COUNT Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	No	Yes	Yes	Yes
Elasticsearch Non-Aggregate	Yes	No	No	No	No
ClickHouse Aggregate	Yes	No	Yes	Yes	Yes
ClickHouse Non-Aggregate	Yes	No	No	No	No

EMA (N, X)		Exponential moving average over period N of field X	trueVal/falseVal

Elasticsearch Aggregate		No	Yes	Yes	Yes
Elasticsearch Non-Aggregate		No	No	No	No
ClickHouse Aggregate		No	Yes	No	No
ClickHouse Non-Aggregate		No	Yes	No	No

MEDIAN

The aggregate MEDIAN function returns the middle-most value of the field X.

Syntax	Argument	Returns	Return Type
MEDIAN(X)	X - a numerical field	Middle value of X within the time range of a query.	Double

MEDIAN Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	No	Yes	Yes	Yes
Elasticsearch Non-Aggregate	No	No	No	No	No
ClickHouse Aggregate	Yes	No	Yes	Yes	Yes
ClickHouse Non-Aggregate	No	No	No	No	No

MODE

The aggregate MODE function returns the highest value of field X which has highest occurring frequency

Syntax	Argument	Returns	Return Type
MODE(X)	X - can be any field of type number,ip,string. Fields of type “text” are not allowed	Returns the most frequent value of the field X	The return type is similar to the type of field X

MODE Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested	With Trend
Elasticsearch Aggregate	Yes	No	Yes	Yes	Yes	No
Elasticsearch Non-Aggregate	No	No	No	No	No	No
ClickHouse Aggregate	Yes	No	Yes	Yes	Yes	No
ClickHouse Non-Aggregate	No	No	No	No	No	No

PCTILE

The aggregate PCTILE function

Syntax	Argument	Returns	Return Type
PCTILE(N,X)		Returns N percentile value of the numeric event attribute X.	N between 0 and 100.

PCTILE Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested	With Trend
Elasticsearch Aggregate	Yes	No	Yes	No	No	Yes
Elasticsearch Non-Aggregate	No	No	No	No	No	No
ClickHouse Aggregate	Yes	No	Yes	No	No	Yes
ClickHouse Non-Aggregate	No	No	No	No	No	No

SMA (N, X)		Simple moving average over period N of field X

Elasticsearch Aggregate		No	Yes	Yes	Yes
Elasticsearch Non-Aggregate		No	No	No	No
ClickHouse Aggregate		No	Yes	No	No
ClickHouse Non-Aggregate		No	Yes	No	No

STDDEV

The aggregate STDDEV function calculates the sample standard deviation of input field. Assumes data is normally distributed.

Syntax	Argument	Returns	Return Type
STDDEV(X)	X - a numerical field	Returns the sample standard deviation of the field X	Double

STDDEV Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested	With Trend
Elasticsearch Aggregate	Yes	No	Yes	Yes	Yes	Yes
Elasticsearch Non-Aggregate	No	No	No	No	No	No
ClickHouse Aggregate	Yes	No	Yes	Yes	Yes	No
ClickHouse Non-Aggregate	No	No	No	No	No	No

VARIANCE

The aggregate VARIANCE function returns the statistical sample variance of the input field.

Syntax	Argument	Returns	Return Type
VARIANCE(X)	X - a numerical field	Returns the variance of X	Double

VARIANCE Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested	With Trend
Elasticsearch Aggregate	Yes	No	Yes	Yes	Yes	Yes
Elasticsearch Non-Aggregate	No	No	No	No	No	No
ClickHouse Aggregate	Yes	No	Yes	Yes	Yes	No
ClickHouse Non-Aggregate	No	No	No	No	No	No

Conditional Functions

The following conditional function is available.

• IF

Conditional Function	Works with Elasticsearch Aggregate Functions	Works with Elasticsearch Non-Aggregate Functions	Works with ClickHouse Aggregate Functions	Works with ClickHouse Non-Aggregate Functions
IF	Yes	Yes	Yes	Yes

IF

The conditional function IF evaluates a Boolean expression and returns the user defined true/false values. Can run aggregations on top of IF function

Syntax	Argument	Returns	Return Type
IF(predicate, truVal, falseVal)	predicate – a valid boolean expression which evaluates to Boolean ‘true’/’false’ trueVal – value to be returned when predicate evaluates to ‘true’. Can be ‘string’, ‘int’ falseVal -- value to be returned when predicate evaluates to ‘true’. Can be ‘string’, ‘int’	trueVal/falseVal based on what the predicate evaluates to	trueVal & falseVal are ‘string’  string trueVal & falseVal are ‘int’  int

IF Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested	With Trend
Elasticsearch Aggregate	Yes	No	Yes	No	Yes	Yes
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No	No
ClickHouse Aggregate	Yes	Yes	Yes	No	Yes	No
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes	No

Conversion Functions

The following conversion manipulation functions are available.

• LOG

• TO_DOUBLE

• TO_INTEGER

• TO_STRING

Conversion Function	Works with Elasticsearch Aggregate Functions	Works with Elasticsearch Non-Aggregate Functions	Works with ClickHouse Aggregate Functions	Works with ClickHouse Non-Aggregate Functions
LOG	Yes	Yes	Yes	Yes
TO_DOUBLE	Yes	Yes	Yes	Yes
TO_INTEGER	Yes	Yes	Yes	Yes
TO_STRING	Yes	Yes	Yes	Yes

LOG

The conversion function LOG calculates the LOG of a number for its base.

Syntax	Argument	Returns	Return Type
LOG(X,<base>)	X - a number whose logarithmic value needs to be calculated. X should be positive number greater than 0 <base> - (optional) base which needs to be used. Default value is 10. Only base 2, ‘e’ and 10 is allowed.	A numerical value which is equivalent to logY(X)	Double

LOG Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	Yes	No	No
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	Yes	No	No
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	No

TO_DOUBLE

The conversion function TO_DOUBLE converts the input string to a float64 number which can be a field name or a value.

Syntax	Argument	Returns	Return Type
TO_DOUBLE(X)	X - the string which needs to be converted to a number.	A string value representation of X	String

TO_DOUBLE Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	No
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	Yes	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

TO_INTEGER

The conversion function TO_INTEGER converts the input string to a number which can be a field name or a value. BASE is optional. The default is 10.

Syntax	Argument	Returns	Return Type
TO_INTEGER(X,<base>)	X - the string which needs to be converted to a number. <base> - optional parameter. Only base 2, 10 and 16 is allowed. Base 10 is default.	A string value representation of X <base>	String

TO_INTEGER Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	No
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	Yes	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

TO_STRING

The conversion function TO_STRING converts the input value to a string. If the input value is a number, it reformats it as a string. If the input value is a Boolean value, it returns the corresponding string value, "true" or "false".

Syntax	Argument	Returns	Return Type
TO_STRING(X)	X - can be a number or valid Boolean expression which evaluates to "true" or "false". .	X is a number - a string representation of X X is a Boolean expression – “true”/”false”	String

TO_STRING Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	No
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	Yes	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

Extraction Functions

The following extraction function is available.

• EXTRACT

Extraction Function	Works with Elasticsearch Aggregate Functions	Works with Elasticsearch Non-Aggregate Functions	Works with ClickHouse Aggregate Functions	Works with ClickHouse Non-Aggregate Functions
EXTRACT	Yes	Yes	Yes	Yes

EXTRACT

The extraction function EXTRACT retrieves a match for a regex from the source string. Only one capture group should be present in the regex.

Syntax	Argument	Returns	Return Type
EXTRACT(X,regex)	X - a string on which to search regex - regular expression .	match found: substring matched in the capture group match not found: null	Return type is ‘string’ by default

If EXTRACT is used to retrieve a value which is then enclosed in some aggregation function such as AVG, SUM, etc., then the return type will be ‘double’. Extracting a non-numerical value and enclosing it an aggregation function will result in query runtime error.

EXTRACT Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	Yes
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	Yes
ClickHouse Aggregate	Yes	Yes	Yes	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

String Manipulation Functions

The following string manipulation functions are available.

• LEN

• LTRIM

• REPLACE

• RTRIM

• SUB_STR

• TRIM

• TO_LOWER

• TO_UPPER

String Manipulation Function	Works with Elasticsearch Aggregate Functions	Works with Elasticsearch Non-Aggregate Functions	Works with ClickHouse Aggregate Functions	Works with ClickHouse Non-Aggregate Functions
LEN	Yes	Yes	Yes	Yes
LTRIM	Yes	Yes	Yes	Yes
REPLACE	Yes	Yes	Yes	Yes
RTRIM	Yes	Yes	Yes	Yes
SUB_STR	Yes	Yes	Yes	Yes
TRIM	Yes	Yes	Yes	Yes
TO_LOWER	Yes	Yes	Yes	Yes
TO_UPPER	Yes	Yes	Yes	Yes

LEN

The string manipulation function LEN returns the length, in characters, of the input string.

Syntax	Argument	Returns	Return Type
LEN(X)	X - The source string that will be measured for string length.	Returns the length, in characters, of the input string	Long

LEN Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	Yes
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	Yes	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

LTRIM

The string manipulation function LTRIM removes the leading match of the specified regex. If no regex is provided, then it removes white spaces and tabs by default.

Syntax	Argument	Returns	Return Type
LTRIM(X,<regex>)	X – any string <regex> – String or regular expression to be trimmed from the beginning of source (optional)	Source after removing all leading matches of regex	String

LTRIM Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	No
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	Yes	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

REPLACE

The string manipulation function REPLACE replaces all string matches with another string.

Syntax	Argument	Returns	Return Type
REPLACE(X,Y,Z)	X - original string which needs to be modified Y - string to be replaced / regex Z - replacement string (optional)	Returns a string formed by substituting string Z for every occurrence of regex string Y in string X. If Z is not provided, then it is treated equal to empty string	String

REPLACE Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	No
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	Yes	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

RTRIM

The string manipulation function RTRIM removes trailing matches of the specified regex. If no regex is provided, then it removes white spaces and tabs by default.

Syntax	Argument	Returns	Return Type
RTRIM(X,<regex>)	X – any string <regex> – String or regular expression to be trimmed from the end of source (optional)	source after removing all trailing matches of regex	String

RTRIM Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	No
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	Yes	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

SUB_STR

The string manipulation function SUB_STR extracts a substring of certain length from a source string starting from some index.

Note: ClickHouse index starts from 1. Elasticsearch starts from 0.

Syntax	Argument	Returns	Return Type
SUB_STR(X,Y,<Z>)	X - source string Y - start index. (0 <= Y <= len(X)) <Z> - number of characters to be retrieved from Y (optional)	substring of X, starting at the index specified by Y with the number of characters specified by Z. If Z is not provided, the function returns the rest of the string	String

LEN Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	No
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	Yes	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

TRIM

The string manipulation function TRIM removes all leading and trailing matches of the specified regex. If no regex is provided, then it removes white spaces and tabs by default.

Syntax	Argument	Returns	Return Type
TRIM(X,<regex>)	X - any string <regex> - String or regular expression to be trimmed from the beginning and end of source (optional) <regex> with ‘,’ not supported as of now	source after removing all leading and trailing matches of regex	String

TRIM Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	No
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	Yes	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

TO_LOWER

The string manipulation function TO_LOWER converts input into lowercase.

Note: TO_LOWER(User) can return empty strings, hence it will look like that it is returning NULL. For this reason, it is recommended to use attributes directly with IS NULL and IS NOT NULL.

Syntax	Argument	Returns	Return Type
TO_LOWER(X)	X - The source string that will be converted to lowercase	Returns the input string in lowercase	String

TO_LOWER Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	No
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	No	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

TO_UPPER

The string manipulation function TO_UPPER converts input into uppercase.

Note: TO_UPPER(User) can return empty strings, hence it will look like that it is returning NULL. For this reason, it is recommended to use attributes directly with IS NULL and IS NOT NULL.

Syntax	Argument	Returns	Return Type
TO_UPPER(X)	X - The source string that will be converted to uppercase	Returns the input string in uppercase	String

TO_UPPER Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested
Elasticsearch Aggregate	Yes	Yes	No	No	No
Elasticsearch Non-Aggregate	Yes	Yes	Yes	No	No
ClickHouse Aggregate	Yes	Yes	No	No	Yes
ClickHouse Non-Aggregate	Yes	Yes	Yes	No	Yes

Time Window Functions

The following Time Window functions are available.

• EMA

• SMA

Time Window Function	Works with Elasticsearch Aggregate Functions	Works with Elasticsearch Non-Aggregate Functions	Works with ClickHouse Aggregate Functions	Works with ClickHouse Non-Aggregate Functions
EMA	Yes	No	Yes	Yes
SMA	Yes	No	Yes	Yes

EMA

The time window EMA function computes the exponential moving average over the values of the input field for the determined time.

For EMA, older data points become exponentially less important. The speed at which the importance decays is controlled with a decay parameter represented with ∝. The value of ∝ is internally calculated using the window size:

∝ =1-(1/2)〖N 〗^( ) where ∝ is decay parameter and N is look back window size.

Syntax	Argument	Returns	Return Type
EMA(N,X)	N - the size of the look back window. (Integer) o If the time aggregate field included is phRecvHour, then window refers for last ‘N’ hours o If the time aggregate field included is phRecvDate, then window refers for last ‘N’ days, etc... X - The input field (Numerical attribute)	Exponential moving average over period N of field X	Double

EMA Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested	With Trend
Elasticsearch Aggregate	Yes	No	Yes	Yes	Yes	Yes
Elasticsearch Non-Aggregate	No	No	No	No	No	No
ClickHouse Aggregate	Yes	No	Yes	Yes	No	No
ClickHouse Non-Aggregate	Yes	No	Yes	No	No	No

SMA

The time window SMA function computes the simple moving average over the values of the input field for the determined time. It calculates the sum of all values in the window, then divides by the size of the window. It is effectively a simple arithmetic mean of the window.

Any query using SMA needs to have at least one of the time aggregation fields included. Supported time aggregation fields include

• phRecvHour

• phRecvDate

• phRecvWeek

• phRecvMonth

Syntax	Argument	Returns	Return Type
SMA(N,X)	N - the size of the window. (Integer) If the time aggregate field included is phRecvHour, then window refers for last ‘N’ hours If the time aggregate field included is phRecvDate, then window refers for last ‘N’ days etc X - The input field (Numerical attribute)	Simple moving average over period N of field X	Double

SMA Function	Available	With Filter	With Orderby	With GroupEvtConstr	With Nested	With Trend
Elasticsearch Aggregate	Yes	No	Yes	Yes	Yes	Yes
Elasticsearch Non-Aggregate	No	No	No	No	No	No
ClickHouse Aggregate	Yes	No	Yes	Yes	No	No
ClickHouse Non-Aggregate	Yes	No	Yes	No	No	No