Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 
ON THIS PAGE
 

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:

START

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:59SELECT * 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

STOP

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'

LAST

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'

Time Functions

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

NOW

  • 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 eventsFind the user of the asset at this moment in time (NOW).

PARSEDATETIME

  • 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')

Related Documentation