Time Window Functions

Details of the following time window functions are available.

• EMA
• SMA

EMA Function

EMA(N,<field>) returns Exponential Moving Average over N previous values of the numerical valued <field> among all events matching the query criteria.

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 SMA needs to have at least one of the time aggregation fields included. Supported time aggregation fields include

• Hourly (phRecvHour)
• Daily (phRecvDate)
• Weekly (phRecvWeek)
• Monthly(phRecvMonth)

In Elasticsearch, these nested function categories are allowed:

• Extraction Function, String Manipulation Functions
  Note: 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

Works for ClickHouse and Elasticsearch queries from release 7.0.0 onwards.

Example

Raw Data

eventRecvTime	hostName	cpuUtil
Apr 19, 2023, 12:00:00 PM	H1	3.00%
Apr 19, 2023, 12:15:00 PM	H1	2.37%
Apr 19, 2023, 12:30:00 PM	H1	2.68%
Apr 19, 2023, 12:45:00 PM	H1	2.72%
Apr 19, 2023, 13:00:00 PM	H1	2.55%
Apr 19, 2023, 13:15:00 PM	H1	2.43%
Apr 19, 2023, 13:30:00 PM	H1	2.50%
Apr 19, 2023, 13:45:00 PM	H1	2.74%
Apr 19, 2023, 14:00:00 PM	H1	2.64%
Apr 19, 2023, 14:15:00 PM	H1	3.74%
Apr 19, 2023, 14:30:00 PM	H1	2.12%
Apr 19, 2023, 14:45:00 PM	H1	3.84%

EMA query response

eventRecvHour	hostName	EMA(2,cpuUtil)
Apr 19, 2023, 12:00:00 PM	H1	2.69
Apr 19, 2023, 13:00:00 PM	H1	2.60
Apr 19, 2023, 14:00:00 PM	H1	2.92

alpha = 2/(window size + 1) = 2/(2 + 1) = 0.667

AVG(cpuUtil) [12:00-12:59:59]  = (3.00 + 2.37+ 2.68+ 2.72)/4 = 2.6925
AVG(cpuUtil) [13:00-13:59:59] = (2.55+2.43+2.50+2.74)/4 = 2.555
AVG(cpuUtil) [14:00- 4:59:59] = (2.64+3.74+2.12+3.84)/4 = 3.085

EMA(2,cpuUtil) [12:00:00] = AVG(cpuUtil) [12:00-12:59:59]
   		  	   = 2.69
EMA(2,cpuUtil) [13:00:00] = alpha * AVG(cpuUtil) [13:00- 13:59:59] + 
     (1 - alpha) * AVG(cpuUtil) [12:00-12:59:59]
  			   = 0.66 * 2.555 + (0.37)*2.6925 
                          = 2.60
EMA(2,cpuUtil) [14:00:00] = alpha * AVG(cpuUtil) [14:00- 14:59:59] + 
                            (1 - alpha) * AVG(cpuUtil) [13:00- 13:59:59]
                          = 0.66 * 3.085 + 0.37*2.555
 			   = 2.92 

Restrictions

1. HAVING clause is not supported.

SMA Function

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

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.

In Elasticsearch, these nested function categories are allowed:

• Extraction Function, String Manipulation Functions
  Note: 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

Raw Data

eventRecvTime	hostName	cpuUtil
Apr 19, 2023, 12:00:00 PM	H1	3.00%
Apr 19, 2023, 12:15:00 PM	H1	2.37%
Apr 19, 2023, 12:30:00 PM	H1	2.68%
Apr 19, 2023, 12:45:00 PM	H1	2.72%
Apr 19, 2023, 13:00:00 PM	H1	2.55%
Apr 19, 2023, 13:15:00 PM	H1	2.43%
Apr 19, 2023, 13:30:00 PM	H1	2.50%
Apr 19, 2023, 13:45:00 PM	H1	2.74%
Apr 19, 2023, 14:00:00 PM	H1	2.64%
Apr 19, 2023, 14:15:00 PM	H1	3.74%
Apr 19, 2023, 14:30:00 PM	H1	2.12%
Apr 19, 2023, 14:45:00 PM	H1	3.84%

SMA Query response

eventRecvHour	hostName	SMA(2,cpuUtil)
Apr 19, 2023, 12:00:00 PM	H1	2.69
Apr 19, 2023, 13:00:00 PM	H1	2.62
Apr 19, 2023, 14:00:00 PM	H1	2.85

SMA(2,cpuUtil) [12:00:00] = AVG(cpuUtil) [12:00-12:59:59]
 			   = (3.00 + 2.37+ 2.68+ 2.72)/4
   		  	   = 2.6925
SMA(2,cpuUtil) [13:00:00] = (AVG(cpuUtil) [12:00-12:59:59] + AVG(cpuUtil) [13:00- 13:59:59] ) /2
  			   = [2.6925 + (2.55+2.43+2.50+2.74)/4] / 2 
  			   = [ 2.6925 + 2.555 ] / 2 
                          = 2.62
SMA(2,cpuUtil) [14:00:00] = (AVG(cpuUtil) [13:00-13:59:59] + AVG(cpuUtil) [14:00- 14:59:59] ) /2
                          = [ 2.555 + (2.64+3.74+2.12+3.84)/4] / 2
                          = [ 2.555 + 3.085 ] / 2
                          = 2.85

Restrictions

1. HAVING clause is not supported.