AQL Data Calculation and Formatting Functions

Purpose--Returns a Base64 encoded string that represents binary data.

Example--SELECT BASE64(payload) FROM events

Returns the payloads for events in BASE64 format.

Purpose--Concatenates all passed strings into one string.

Example--SELECT CONCAT(username, ':', sourceip, ':', destinationip) FROM events LIMIT 5

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

AQL Date and Time Formats

Purpose--Converts a value that represents a number into a double.

Example--DOUBLE('1234')

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 JSA 7.3.0, you can use the LONG function to convert IP addresses into a long integer. JSA 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>.

Bitwise Operators in AQL Queries

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

Time Criteria in AQL Queries

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

For example, parse the following text date format:

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.

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

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".

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--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.

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

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

Purpose--Converts any parameter to a string.

Example--STR(sourceIP)

Purpose--Returns the length of this string.

Example--SELECT STRLEN(sourceIP), STRLEN(username) from events

Returns the string length for sourceip and username.

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)

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

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.

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