Time criteria in AQL queries

Define time intervals in your AQL queries by using START and STOP clauses, or use the LAST clause for relative time references.

Define the time settings that are passed to the AQL query

The SELECT statement supports an arieltime option, which overrides the time settings.

You can limit the time period for which an AQL query is evaluated by using the following clauses and functions:

You can pass a time interval to START selecting data (from time), in the following formats:

yyyy-MM-dd HH:mm
yyyy-MM-dd HH:mm:ss
yyyy/MM/dd HH:mm:ss
yyyy/MM/dd-HH:mm:ss
yyyy:MM:dd-HH:mm:ss

The timezone is represented by 'z or Z' in the following formats:

yyyy-MM-dd HH:mm'Z'

yyyy-MM-dd HH:mm'z'

Use START in combination with STOP.

Examples

SELECT * 
FROM events WHERE userName IS NULL 
START '2014-04-25 15:51'
STOP '2014-04-25 17:00'

Returns results from: 2014-04-25 15:51:00 to 2014-04-25 16:59:59

SELECT *
FROM events WHERE userName IS NULL 
START '2014-04-25 15:51:20' 
STOP '2014-04-25 17:00:20'

Returns results from: 2014-04-25 15:51:00 to 2014-04-25 17:00:59

SELECT * from events 
START PARSEDATETIME('1 hour ago') 
STOP PARSEDATETIME('now')

STOP is optional. If you don't include it in the query, the STOP time is = now

You can pass a time interval to STOP selecting data (end time), in the following formats:

yyyy-MM-dd HH:mm
yyyy-MM-dd HH:mm:ss
yyyy/MM/dd HH:mm:ss
yyyy/MM/dd-HH:mm:ss
yyyy:MM:dd-HH:mm:ss

The timezone is represented by 'z or Z' in the following formats:

yyyy-MM-dd HH:mm'Z'

yyyy-MM-dd HH:mm'z'

Use STOP in combination with START.

Examples


SELECT * FROM events
WHERE username IS NULL 
START  '2016-04-25 14:00'
STOP '2016-04-25 16:00'


SELECT * FROM events
WHERE username IS NULL 
START '2016-04-25 15:00:30' 
STOP '2016-04-25 15:02:30'

Use any format with the PARSEDATETIME function, for example,

SELECT * 
FROM events 
START PARSEDATETIME('1 day ago')

Even though STOP is not included in this query, the STOP time is = now.

Select * FROM events 
START PARSEDATETIME('1 hour ago')
STOP PARSEDATETIME('now')

SELECT * FROM events 
START PARSEDATETIME('1 day ago')

Select * 
FROM events 
WHERE logsourceid = '69' 
START '2016-06-21 15:51:00' 
STOP '2016-06-22 15:56:00'

You can pass a time interval to the LAST clause to specify a specific time to select data from.

The valid intervals are MINUTES, HOURS, and DAYS

Examples

SELECT * FROM events 
LAST 15 MINUTES

SELECT * FROM events 
LAST 2 DAYS

SELECT * from events 
WHERE userName ILIKE '%dm%'
LIMIT 10 
LAST 1 HOURS

Note: If you use a LIMIT clause in your query, you must place it before START and STOP clauses, for example,

SELECT * 
FROM events 
LIMIT 100 
START '2016-06-28 10:00' 
STOP '2016-06-28 11:00'

Use the following time functions to specify the parse time for the query.

Purpose: Returns the current time that is expressed as milliseconds since the time 00:00:00 Coordinated Universal Time (UTC) on January 1, 1970.

Example

SELECT ASSETUSER(sourceip, NOW()) 
AS 'Asset user' FROM events

Find the user of the asset at this moment in time (NOW).

Purpose: Pass a time value to the parser, for example, PARSEDATETIME('time reference'). This 'time reference' is the parse time for the query.

Example

SELECT * FROM events 
START PARSEDATETIME('1 hour ago')