AQL data calculation and formatting functions

Use Ariel Query Language (AQL) calculation and formatting functions on search results that are retrieved from the Ariel databases.

This list describes the AQL functions that are used for calculations and data formatting:

BASE64

Purpose
Returns a Base64 encoded string that represents binary data.
Example
SELECT BASE64(payload)
FROM events
Returns the payloads for events in BASE64 format.


CONCAT

Purpose
Concatenates all passed strings into one string.
Example
SELECT CONCAT(username, ':', sourceip,  ':', destinationip) 
FROM events LIMIT 5


DATEFORMAT

Purpose
Formats time in milliseconds since 00:00:00 Coordinated Universal Time (UTC) on January 1, 1970 to a user-readable form.
Examples
SELECT 
DATEFORMAT(startTime, 'yyyy-MM-dd hh:mm:ss')
AS StartTime
FROM events
SELECT DATEFORMAT(starttime,'yyyy-MM-dd hh:mm') 
AS 'Start Time', 
DATEFORMAT(endtime, 'yyyy-MM-dd hh:mm') 
AS Storage_time, 
QIDDESCRIPTION(qid) 
AS 'Event Name' 
FROM events

See more examples



DOUBLE

Purpose
Converts a value that represents a number into a double.
Example
DOUBLE('1234')


LONG

Purpose
Converts a value that represents a number into a long integer.
Examples 
SELECT destinationip, 
LONG(SUM(sourcebytes+destinationbytes)) 
AS TotalBytes 
FROM flows 
GROUP BY sourceip

The example returns the destination IP address, and the sum of the source and destination bytes in the TotalBytes column.

SELECT
LONG(sourceip)
AS long_ip
FROM events
INTO <cursor_name>
WHERE (long_ip & 0x<ff>000000) = 0x<hexadecimal value of IP address>000000
GROUP BY long_ip
LIMIT 20

In QRadar®7.3.1, you can use the LONG function to convert IP addresses into a long integer. QRadar uses long integers with bitwise operators to do IP address arithmetic and filtering in AQL queries. In the example, the source IP is returned as an integer, which is used by the bitwise AND operator.

In the example, the <ff> corresponds with <hexadecimal value of IP address>, which is in the first octet position for an IP address. The <cursor_name> can be any name that you want to use.

For example, if you want to return all source IP addresses with the number 9 in the first octet, then substitute the hexadecimal value 9, which is the same as the decimal value, in <hexadecimal value of IP address>.

See more examples of the long function that are used with bitwise operators


PARSEDATETIME

Purpose
Pass a time value to the parser, for example, PARSEDATETIME('time reference'). The time reference indicates the parse time for the query.
Example
SELECT * FROM events 
START PARSEDATETIME('1 hour ago')

See more examples of time functions



PARSETIMESTAMP

Purpose
Parse the text representation of date and time and convert it to UNIX epoch time.

For example, parse the following text date format:

Thursday, August 24, 2017 3:30:32 PM GMT +01:00 and convert it to the following epoch timestamp: 1503588632.

This function makes it easier to issue calls from the API that are based on scripts.
Example of how the time format conversion works

The following example demonstrates how the DATEFORMAT function converts epoch time to a text timestamp by using the specified date format, and then the PARSETIMESTAMP function is used to convert the text timestamp to an epoch time format. 

SELECT starttime, DATEFORMAT(starttime,'EEE, MMM d, "yyyy"') 
AS "text time format", 
PARSETIMESTAMP('EEE, MMM d, "yyyy"', "text time format") 
AS 'epoch time returned' from events limit 5

The following example displays an extract of the output from the query:


starttime       text time format      epoch time returned
1503920389888   Mon, M08 28, "2017"   1503920389888
Example of how PARSETIMESTAMP might be used to convert times to epoch time so that time calculations can be made.

In the following example, events are returned when the time difference between logout and login times is less that 1 hour.

The EEE, d MMM yyyy HH:mm:ss.SSSZ time format is just one example of a time format that you might use, and my_login and my_logout are custom properties in a known time format, for example, EEE, MMM d, "yy". 

SELECT * from events 
WHERE 
PARSETIMESTAMP('EEE, d MMM yyyy HH:mm:ss.SSSZ', my_logout)
- PARSETIMESTAMP('EEE, d MMM yyyy HH:mm:ss.SSSZ', my_login) 
< 3600000 last 10 days

See more examples of time functions



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


LOWER

Purpose

Returns an all lowercase representation of a string.

Example
SELECT 
LOWER(username), 
LOWER(LOGSOURCENAME(logsourceid)) 
FROM events
Returns user names and log source names in lowercase.


REPLACEALL

Purpose
Match a regex and replace all matches with text.

Replaces every subsequence (arg2) of the input sequence that matches the pattern (arg1) with the replacement string (arg3).

Example
REPLACEALL('\d{16}',
username, 'censored')


REPLACEFIRST

Purpose
Match a regex and replace the first match with text.

Replaces the first subsequence (arg2) of the input sequence that matches the pattern (arg1) with the replacement string (arg3).

Example
REPLACEFIRST('\d{16}',
 username, 'censored')


STR

Purpose
Converts any parameter to a string.
Example
STR(sourceIP)


STRLEN

Purpose
Returns the length of this string.
Example
SELECT STRLEN(sourceIP), 
STRLEN(username) from events
Returns the string length for sourceip and username.


STRPOS

Purpose
Returns the position (index - starts at zero) of a string in another string. Searches in string for the index of the specified substring. You can optionally specify an extra parameter to indicate at what position (index) to start looking for the specified pattern.

The search for the string starts at the specified offset and moves towards the end of string.

STRPOS(string, substring, index)

Returns -1 if the substring isn't found.
Examples
SELECT STRPOS(username, 'name') FROM events
 

SELECT STRPOS(sourceip, '180', 2) FROM events)


SUBSTRING

Purpose
Copies a range of characters into a new string.
Examples
SELECT SUBSTRING(userName, 0, 3) FROM events
 
SELECT SUBSTRING(sourceip, 3, 5) FROM events


UPPER

Purpose
Returns an all uppercase representation of a string.
Example
SELECT 
UPPER(username), 
UPPER(LOGSOURCENAME(logsourceid)) 
FROM events
Returns user names and log source names in uppercase.


UTF8

Purpose
Returns the UTF8 string of a byte array.
Example
SELECT UTF8(payload)
FROM events
WHERE sourceip='192.0.2.0'
Returns the UTF8 payload for events where the source IP address is 192.0.2.0