Transactional query refinements

Expressions

Use the expressions that are described in the following to refine your transactional AQL query:

Syntax

SESSION BY 
<TimeExpression> <ExpressionList> 
[EXPLICIT] BEGIN <booleanExpression>  
[EXPLICIT] END <booleanExpression> 
TIMEOUT <IntegerLiteral millieseconds> 
TIMEWINDOW <IntegerLiteral SECONDS|MINUTES|HOURS|DAYS>

The following examples show the examples of results that you get by using different combinations of the available query expressions:

BEGIN and END expressions

A BEGIN expression starts a session when an event matches the BEGIN expression or the previous event ends the session.

An END expression ends a session when the END expression is true for an event or the next event starts a new session.

By using the EXPLICIT expression with the BEGIN and END expressions, you apply a more precise filter that refines the result set.

See the following examples of queries and results.

The following query example uses BEGIN and END expressions.

Select sessionId, 
DATEFORMAT(starttime,'YYYY-MM-dd HH:mm:ss') 
start_time, username, sourceip, 
category from events into TR1 
where username = 'user_x' 
SESSION BY starttime username, sourceip 
BEGIN category=16001 
END category=16003 
start '2016-12-10 16:00' stop '2016-12-10 23:30'

The following table shows the results for the query that uses BEGIN and END.

EXPLICIT BEGIN and END expressions

Events are skipped when a session is not started and an event is not an EXPLICIT BEGIN event.

Select sessionId, 
DATEFORMAT(starttime,'YYYY-MM-dd HH:mm:ss') 
start_time, username, sourceip, 
category from events into TR2 
where username='user_x' 
SESSION BY starttime username, sourceip 
EXPLICIT BEGIN category=16001 
END category=16003 start '2016-12-10 16:00' 
stop '2016-12-10 23:30'

The following table shows the results for the query that uses EXPLICIT BEGIN and END.

Only events that satisfy the EXPLICIT BEGIN expression are returned.

Sessionid 2 and Sessionid 4 in the EXPLICIT BEGIN and END don't satisfy the EXPLICIT BEGIN expression.

BEGIN and EXPLICIT END

Close current session only if the EXPLICIT END expression is true. There are no more checks for BEGIN events in the session when the EXPLICIT END expression is true.

Multiple BEGIN events in a single session can be associated with one EXPLICIT END expression. For example, you might use the EXPLICIT END expression for counting multiple failed login attempts that are followed by a successful login during a specific time interval (session timeout).

The following query example uses BEGIN and EXPLICIT END expressions.

Select sessionId, 
DATEFORMAT(starttime,'YYYY-MM-dd HH:mm:ss') 
start_time, username, sourceip, 
category from events into TR3 
where username = 'user_x' 
SESSION BY starttime username, sourceip 
BEGIN category=16001 
EXPLICIT END category=16003 
start '2016-12-10 16:00' 
stop '2016-12-10 23:30'

The following table shows the results for the query that uses BEGIN and EXPLICIT END expressions.

EXPLICIT BEGIN and EXPLICIT END

Events are ignored when a session is not started and an event is not an EXPLICIT BEGIN event.

Close current session only if the EXPLICIT END expression is true. There are no more checks for BEGIN events in the session when the EXPLICIT END expression is true.

The following query example uses both EXPLICIT BEGIN and EXPLICIT END expressions.

Select sessionId, 
DATEFORMAT(starttime,'YYYY-MM-dd HH:mm:ss') 
start_time, username, sourceip, 
category from events into TR4 
where username = 'user_x' 
SESSION BY starttime username, sourceip 
EXPLICIT BEGIN category=16001 
EXPLICIT END category=16003 
start '2016-12-10 16:00' 
stop '2016-12-10 23:30'

The following table shows the results for the query that uses both EXPLICIT BEGIN and EXPLICIT END expressions.

TIMEOUT

Closes the session when the specified TIMEOUT period elapses from the time that the previous event occurred to the time that the current event happened. The current event becomes part of a new session. The TIMEOUT value is specified in milliseconds.

The following query example uses the TIMEOUT expression.

Select sessionId, 
DATEFORMAT(starttime,'YYYY-MM-dd HH:mm:ss.SSS') 
start_time, username, sourceip, 
category from events into TR5 
where username='user_x' 
SESSION BY starttime username, sourceip 
BEGIN category=16001 
EXPLICIT END category=16003 
TIMEOUT 3600 
start '2016-12-10 16:00' 
stop '2016-12-10 23:30'

The following table shows the results for the query that uses the TIMEOUT expression.

Sessionid 1 is ended and sessionid 2 is started because the TIMEOUT of 3600 is exceeded.

TIMEWINDOW

Tracks the session time. Closes the session when the specified TIMEWINDOW period elapses from the time that the first event occurred to the time that the current event happened. The current event becomes part of a new session. The TIMEWINDOW value can be specified in seconds, minutes, hours, or days.

The following query example uses the TIMEWINDOW expression.

Select sessionId, 
DATEFORMAT(starttime,'YYYY-MM-dd HH:mm:ss.SSS') 
start_time, username, sourceip, 
category from events into TR6 
where username='user_x' 
SESSION BY starttime username, sourceip 
BEGIN category=16001 
EXPLICIT END category=16003 
TIMEWINDOW 3000 
start '2016-12-10 16:00' 
stop '2016-12-10 23:30'

The following table shows the results for the query that uses the TIMEWINDOW expression.

Sessionid 1 is within the TIMEWINDOW expression time but sessionid 2 is ended because the TIMEWINDOW of 3600 is exceeded.