case(<condition>,<value>, ...)

Example:

|where status!= ''
|calc result = case(status = "401", "Unauthorized", status = "502", "bad gateway", status= "302", "url temperarliy moved")
| fields status, result

Takes alternating conditions and values. Returns the first value for which the condition is true.

coalesce(<values>)

Example:

|where src != "" or src != "NULL" or src != "null" or src != "-"
|calc res = coalesce(src, dest, "10.1.1.1")
| fields src, dest, res

Takes one or more values. Returns the first non-null value.

if(<predicate>,<true_value>,<false_value>)

Example:

|calc result=if(status = '401', 'Unauthorized', 'not true')

If the <predicate> expression is true, returns the <true_value>. Otherwise returns the <false_value>.

insubnet (<cidr>,<ip>)

<ip> insubnet (<cidr>)

<ip> insubnet (<cidr1>,<cidr2>, ..., <cidrN>)

<ip> insubnet (*)

Examples:

Example 1:

|where dest_ip startswith "172.18"
|fields src_ip, dest_ip
|calc in_subnet=insubnet("172.18.0.0/16", dest_ip)

Example 2:

ocsf | where dst_endpoint.ip startswith '10.' | fields src_endpoint.ip, dst_endpoint.ip | calc in_subnet=insubnet('10.0.0.0/8', dst_endpoint.ip)

Returns TRUE if the specified CIDR address includes the value of the specified IP address field; otherwise, returns FALSE.

Use the asterisk (*) symbol as an equivalent for '0.0.0.0/0' for matching any IP address.

like(<field>, <pattern>)

not like(<field>, <pattern>)

Example:

|calc res = if(not like(user, 'test%'), 'found', 'not_found')

Evaluate whether the specified field matches the specified pattern. The pattern can specify an exact match or a wildcard match:

• Use the percent ( % ) symbol as a wildcard for matching multiple characters.

• Use the underscore ( _ ) character as a wildcard to match a single character.

match(<field>, <regex>)

Example:

|calc does_field_match=if(match(user, 'l{2,}'), "yes", "no")

Evaluate whether any substring of a specified field matches the specified regex.

null()

Example:

|calc null_action = null()

|calc nulled_action=if(action = '', null(), action)

Use this function to set the value of fields with no value to NULL. You might want to do this when using aggregation functions to calculate the average or median of a field value.

The null() function takes no arguments and returns NULL.

When used with if(), as shown in the example, the null() function is processed if the condition is true. In the example, if action is blank, the null() function is processed and the action field is populated with NULL.

[subsearch]

Example:

| aggr count by sourcetype
| appendtable [| where earliest=10d and latest=5d
| aggr count by sourcetype]

Use this function to embed a search within the primary search, using square brackets ( [] ), to create more powerful queries that dynamically adapt according to the results of the embedded query.

See Subsearch documentation for more information.

(<field or string> || <field or string>)

Example:

|calc res = ("Hello " || "there.") returns a field name res having the phrase "Hello there."

In the following example, assume the value for field1 is test. The function returns a field name res having the phrase "This is a test."

|calc res = ("This" || " is a " || field1)

Concatenates the specified fields or strings into a new field. You must specify at least two arguments, and there is no upper limit. Order is important. The first argument is the start of the new field and subsequent arguments are appended in order.

The || concatenation function is an alternative to the strcat function.

strcat(<field or string>, <field or string>[, ...])

Example:

|calc host_port = strcat(src, ":", src_port)

returns a field name host_port having values similar to "192.168.1.53:1813".

Concatenates the specified fields or strings into a new field. Enclose string values in single or double quotes. You must specify at least two arguments, and there is no upper limit.

The strcat function is an alternative to the || concatenation function.

len(<str>)

length(<str>)

Example:

|fields dest, dest_ip, user |calc dest_length = len(dest)

|fields url | calc url_length = length(url)

Returns the number of characters in the specified field value.

The <str> argument can be the name of a string field or a string literal.

lower(<str>)

Example:

|calc res_lower=LOWER(sourcetype)

Converts values for the specified field to lowercase.

ltrim(<str>,<trim_chars>)

Example:

|where file_path = 'C:\Windows\System32\WindowsPowerShell\v1.0' |fields file_path | calc res_ltrim=LTRIM(file_path, 'C:\Windows')

|calc x=ltrim(" ZZZZabcZZ ", " Z")

Removes characters from the left side of the specified field value.

The <str> argument can be the name of a string field or a string literal.

The <trim_chars> argument is optional. If not specified, spaces and tabs are removed from the left side of the string.

replace(<field>,<current_string>, <replacement_string>)

Example:

|where sourcetype = "Zscaler_leef" |fields sourcetype | calc res_replace=REPLACE(sourcetype, 'Zscaler_leef', 'Zleef')

Substitutes <current_string> with<replacement_string>for the specified field.

regex_replace(<field>,<regex>, <replacement_string>)

Example:

|where sourcetype="ms_defender" | calc test_regex_replace=regex_replace(src, "^qa-", "localTest-")

rtrim(<str>,<trim_chars>)

Example:

|calc res_rtrim=RTRIM(sourcetype)

Removes characters from the right side of the specified field value.

The <str> argument can be the name of a string field or a string literal.

The <trim_chars> argument is optional. If not specified, spaces and tabs are removed from the right side of the string.

substr(<str>,<start>,<length>)

substring(<str>,<start>,<length>)

Example:

|fields app_type |calc rest_sub=SUBSTR(app_type,5,25)

Returns a substring of the specified field value, beginning at the position specified by the <start> argument. The <length> argument specifies the number of characters to return.

trim(<str>,<trim_chars>)

Example:

|calc res_trim=TRIM(sourcetype)

Removes characters from both sides of the specified field value.

The <str> argument can be the name of a string field or a string literal.

The <trim_chars> argument is optional. If not specified, spaces and tabs are removed from both sides of the string.

upper(<str>)

Example:

|fields url |calc res_lower=UPPER(url)

Converts values for the specified field to uppercase.

urldecode(<str>)

Example:

|calc url_decoded=urldecode(url)

|calc url_decoded=urldecode('http%3A%2F%2Fi.just.love.urldecode')

Applies URL-decoding to convert specified URL fields or URL values to human-readable string values.

For field values that are unencoded, the function returns the field name.

tonumber(<field>,<base>)

Example:

|calc ds_id_number = tonumber(ds_id)

|calc ds_id_from_hex = tonumber(ds_id, 16)

Converts values for the specified string field to number values. The <base> option specifies the number base 2 to 36. The default if no base is specified is base 10.

tostring(<field>,<format>)

Example:

|calc dest_port_str = tostring(dest_port)

Converts values for the specified number or date field to a string value.

from_unixtime(<bigint>)

Example:

|calc formatted_time = from_unixtime(event_time)

Converts a Unix timestamp into a human-readable date and time format.

A Unix timestamp is a numeric representation of the number of milliseconds since January 1, 1970, 00:00:00 UTC.

to_unixtime(datetime_expression)

Example:

|calc unix_time = to_unixtime(from_unixtime(event_time))

Converts a date and time value into a Unix timestamp.

A Unix timestamp is a numeric representation of the number of milliseconds since January 1, 1970, 00:00:00 UTC.

strftime(<time>, <format>)

timeformat(<time>, <format>)

|calc formated_time=strftime(field_str, '%Y-%m-%dT%H:%M:%S.%Q')

|calc formated_time=timeformat(field_str, '%Y-%m-%dT%H:%M:%S.%Q')

|calc formated_time=strftime(field_num, '%Y-%m-%dT%H:%M:%S.%Q')

|calc formated_time=timeformat(field_num, '%Y-%m-%dT%H:%M:%S.%Q')

|calc formated_time=strftime(field_num + 60 * 60 * 24, '%Y-%m-%dT%H:%M:%S.%Q')

|calc formated_time=timeformat(field_num + 60 * 60 * 24, '%Y-%m-%dT%H:%M:%S.%Q')

Converts a Unix timestamp (in milliseconds) to a human-readable time in the specified format.

See Date Time Format Code for more information.

strptime(<str>, <format>)

Examples:

|calc strp_time = strptime(event_time, "%Y-%m-%d %H:%M:%S")

|calc strp_time = to_unixtime(strptime(event_time, "%Y-%m-%d %H:%M:%S"))

relative_time(<time>,<expression>)

Example:

|calc relative_time_minus_1_day = relative_time(event_time, -1d)

Adjusts a Unix <time> value according to the specified relative time expression.

A Unix timestamp is a numeric representation of the number of milliseconds since January 1, 1970, 00:00:00 UTC.

date_format(date_expression, format_string)

Example:

|calc formatted_time = date_format(event_time, '%Y-%m-%d')

Formats a date expression according to a specified format.

See Date Time Format Code for more information.

This function allows you to customize the format to be consistent with your organization's conventions. For example, you can use this function to change the order of day, month, and year components, including time, specifying the separator character, and more.

now()

Example:

|calc unit_time = now()

Returns the time that the search was started (in Unix time).

A Unix timestamp is a numeric representation of the number of milliseconds since January 1, 1970, 00:00:00 UTC.

ctime()

Example:

|calc ctime_field = ctime(time_as_bigint)

mktime()

Example:

|calc mktime_field = mktime(timestamp)

Takes a timestamp field as an argument and returns the epoch time.

array_contains(<field array>, <value>)

Example:

|calc n = array_contains(split(url, "/"), '1') | fields url,n

split(<field>, <separator>)[num]

Example:

|calc url_res = split(url, '/')

regex_split(<field>,<regex>)

Example:

|where isnotnull(dest) | aggr count by dest | calc f1 = regex_split(dest, '\.') | where array_len(f1) > 1

Splits all occurrences of the regular expression matches in the field. In this example, all occurrences of the '.' symbol in the dest field will be separated.

array_len(array)

array_count(array)

Example:

|calc n = array_len(split(url, "/")) | fields url,n

|calc n = array_count(split(url, "/"))

Example:

|calc one_array=array_concat(dcid, ds_id)

Example:

|calc distinct_emails = array_distinct(split(receiver, {","))

Example:

|calc emails = array_join(split(receiver, ","), ";")

Example:

|where sender = "ck3@daisuke83.drkoop.site" or sender = "au@sralia.com" | calc first_batch = array_slice(split(sender,"@"), 1, 2)

JSON Extraction functions

|where sourcetype = "ULink: aws_s3_dns" |calc host_name=json_extract(host, 'name')

Creates a new field and populates values for the new field with content extracted from an existing field having JSON values.

In the example:

• host_name is the new field.

• json_extract is the name of the function.

• host specifies the event log field that contains the JSON.

• key is the key part of the JSON key-value pair. Use single quotes to specify the JSON key.

json_extract(data, 'resource.instanceDetails.iamInstanceProfile.arn')

Extract nested keys using dot notation to specify the path to the desired key.

Examples:

json_extract(data, 'previous-year')

json_extract(data, 'monitoring."start-date"')

json_extract(data, 'log."number.alerts"')

If the JSON contains keys with special characters, the operator automatically skips the following characters:

• - (dash)

• \ (backslash).

If there are other special characters, avoid them by using double quotes (" "). Only the most nested key must be enclosed by double quotes. See examples.

Examples:

json_extract(data, 'nonexistent')

json_extract(data, 'key.nonexistent')

If the key does not exist in the JSON, null is returned.

json_extract(data, 'groups', ARRAY)

json_extract(data, 'groups', BOOLEAN)

json_extract(data, 'groups', NUMBER)

Examples:

|calc groups=json_extract(data, 'groups', ARRAY) |calc in_sg=array_contains(groups, 'sg-0abd9612be5dbaa33')

|calc reason=json_extract(data, 'Log.labels.authorization', BOOLEAN)

|calc reason=json_extract(data, 'Log.labels.authorization.value', NUMBER)

By default, the value extracted from the JSON is a string, even if the JSON value is an array. To retrieve results in a specific datatype, specify the datatype in the json_extract expression.

For example:

• To return an array, specify the ARRAY type in the function. This notation helps handle the extracted value as an array, which can be used with functions like array_contains.

• To return a boolean status (true or false), specify the BOOLEAN type in the function.

• To return a numeral, specify the NUMBER type in the function.

Examples:

json_extract(data, 'resource.instance.interfaces{0}.ipAddress')

json_extract(data, 'resource.instance.interfaces{}.ipAddress')

json_extract(data, 'resource.instance.interfaces{}.ipAddress', ARRAY)

To extract nested keys from a specific element of an array, use the {index} notation. The index starts from 0.

To extract all nested keys in the array of elements, the index can be omitted. This will extract the key for each element in the array. By default, this will return an array of IP addresses but as a string.

In the example, the ARRAY type cFian be added to get all the IP addresses for the instance of the array type.

Examples:

json_extract(object_extract, 'resource.instance.interfaces{}.groups{}.groupId')

json_extract(object_extract, 'resource.instance.interfaces{}.groups{}.groupId', ARRAY)

This allows complex scenarios where keys of nested arrays need to be extracted.

In the examples, the group IDs for all the groups for all the interfaces in the instance are extracted.

Example of a JSON field with a nested JSON:

{"event": "{\"userIdentity\": {\"accountId\": \"3a99123\"}}"}

Full AQL:

|calc event = json_extract (message, 'event')

|calc account_id = json_extract (event, 'userIdentity.accountId')

If the JSON field contains a nested JSON in string format, you must first extract the string key to be able to extract any key from the nested JSON.

For example: if the message field contains a nested JSON called event in string format:

1. Use json_extract and extract the event key.

2. Use the event field toextract any nested key from the string.

Example of a JSON field with a nested JSON:

{"resource":{"privateIpAddresses":[{"privateDnsName":"ip-88-88-888-88.internal","ipAddress":"88.88.888.88"}]}}

Full AQL Example 1:

| calc ip=json_extract(message, "resource.privateIpAddresses{}(privateDnsName='ip-88-88-888-88.internal').ipAddress")

Here,

• JSON Path to the array: resource.privateIpAddresses{}

• Condition: privateDnsName='ip-88-88-888-88.internal'

• JSON attribute to extract: ipAddress

Result: 88.88.888.88

Full AQL Example 2:

| calc ip=json_extract(message, "resource.privateIpAddresses{}(privateDnsName='ip-88-88-888-88.internal')")

Here,

• JSON Path to the array: resource.privateIpAddresses{}

• Condition: privateDnsName='ip-88-88-888-88.internal'

• JSON attribute to extract: No specific attribute to extract, therefore, the entire object is returned.

Result:

{"privateDnsName":"ip-88-88-888-88.internal","ipAddress":"88.88.888.88"}

You can use a JSON filter if:

1. There is a JSON array containing multiple objects

2. One of those objects contains important information to extract

3. The object position is unknown in the array.

Here is the syntax for the JSON filter:

1. Path to the JSON array.

2. Condition enclosed in parenthesis, including the JSON key and value.

3. (Optional) Path of the JSON key to extract. If the entire JSON object needs to be extracted, this can be omitted.

Syntax:

<json-path>(<key>=<value>).<json-key>

md5(<field> | '<string>')

Example:

|calc string_input = md5('hello')

Convert string fields or string values into a hash using the MD5 cryptographic hashing algorithm.

sha1(<field> | '<string>')

Example:

|calc summary_raw = concat(src, src_port, dest, dest_port, sourcetype, aggr_col1_, totalbytes, earliest_time, latest_time, country, city, totalbytesMB)
|calc d3_uuid = sha1(summary_raw)
|fields src, src_port, dest, dest_port, sourcetype, aggr_col1_, totalbytes, earliest_time, latest_time, action, bytes_transferred_values, user, country, city, totalbytesMB, d3_uuid

Convert string fields or string values into a hash using the SHA1 cryptographic hashing algorithm.

sha256(<field> | '<string>')

Example:

|calc field_hash = sha256(sender)

Convert string fields or string values into a hash using the SHA256 cryptographic hashing algorithm.

sha512(<field> | '<string>')

Example:

|calc field_hash = sha512(sender)

Convert string fields or string values into a hash using the SHA512 cryptographic hashing algorithm.

isnull(<field>, "yes", "no")

isnotnull(<field>, "yes", "no")

Example:

|calc is_field_null=if(isnull(bytes_in), "yes", "no")

|calc is_field_null=if(isnotnull(bytes_in), "yes", "no")

isnum(<field>, "yes", "no")

Example:

|calc is_field_num=if(isnum(field), "yes", "no")

isoutlier(<field>, [param], [uselower])

Example:

|calc is_field_outlier=isoutlier(bytes_in, 1.5)

|calc is_field_outlier=isoutlier(bytes_in, 1.5, true)

Use this function to determine whether value for a specified field is an outlier.

Optional:

• param: Specify a floating point value. This parameter multiplies the interquartile range (IQR) in the evaluation. Increasing the value yields fewer outliers; decreasing the value yields a larger number of outliers. If this parameter is not specified, the implicit default is 2.5.

• uselower: Specify true to detect outliers below the median. If this parameter is not specified, the implicit default is false.

abs(<number>)

Example:

|calc absnum=abs(-2)

ceil(<field> | <number>)

ceiling(<field> | <number>)

Example:

|calc upper_bound_val = ceil(decimal_value)

|calc high_val_test = ceiling(2.1)

Converts numeric fields or numeric values to an upper bound numerical value that is the lowest integer above the given value. In other words, 2.1 is converted to 3.

floor(<field> | <number>)

Example:

|calc lower_bound_val = floor(decimal_value)

|calc low_val_test = floor(2.1)

Converts numeric fields or numeric values to a lower bound numerical value that is the greatest integer below the given value. In other words, 2.1 is converted to 2.

power(<field> | <number)>

pow(<field> | <number>)

Example:

|calc second_power = power(input_field, 2)

|calc third_power = pow(input_field, 3)

round(<field> | <number> [, precision])

Example:

|calc round_to_int = round(decimal_value)

|calc round_to_2_places = round(decimal_value, 2)

|calc rounding_number = round(1.234)

Converts numeric fields or numeric values to a number having the decimal places specified by the given precision. If precision is not specified, it will round to the nearest integer.

|calc <new field> = sqrt(<field> | <number>)

Example:

|calc sqrt_numbers=sqrt(numbers)

|calc <new field> = (<field> | <number> * <field> | <number>)

Example:

|calc myfield=(src_port * 80)|fields src_port,myfield

|calc <new field> = (<field> | <number> / <field> | <number>)

Example:

|calc myfield=(src_port / 80)|fields src_port,myfield

|calc <new field> = (<field> | <number> + <field> | <number>)

Example:

|calc myfield=(src_port + 80)|fields src_port,myfield

|calc <new field> = (<field> | <number> - <field> | <number>)

Example:

|calc myfield=(src_port - 80)|fields src_port,myfield

<new field>=haversine(lat, lon, prev_lat, prev_lon, <distance_unit>)

Example:

|calc distance = haversine(tonumber(lat), tonumber(lon), tonumber(prev_lat), tonumber(prev_lon), 'mi')

Calculates the distance between sequential login events for a user, based on distances between latitude-longitude pairs within queries.

Helps you determine impossible travel, that is, you can automatically identify geographically improbable logins compared to plausible travel speeds, indicating compromised logins.

Supports string-based geolocation fields and integration with other AQL operators such as iplocation (converts IP addresses to geographical location lat-lon) and streamstats (combines coordinates on one line, with the previous coordinates for a user and the previous event time).

Supported units of distance measurement: "km" (kilometers) and "mi" (miles).

mvdedup(field)

Examples:

|calc a = mvdedup(ipaddresses)

Remove duplicate values in multivalue fields.

mvappend(<values>)

Examples:

|calc res=mvappend(src_ip, dest_ip)

|calc res=mvappend(src_ip, dest_ip, 'localhost', '127.0.0.1')

|calc res=mvappend('0.0.0.0', 'localhost', '127.0.0.1')

|calc res=mvappend(['0.0.0.0', 'localhost', '127.0.0.1'], src_ip, dest_ip)

|calc res=mvappend(1, 2, 3, 4)

|calc res=mvappend([1, 2, 3, 4], 5, 6)

|calc res=mvappend(1, array_number_field, 4)

|calc res=mvappend('0.0.0.0', mvappend('localhost', '127.0.0.1'))

Returns a single multivalue result from a specified list of values.

Specify any combination of string or number fields or values, including multivalue fields. Put multivalue arrays in brackets [ ].

You can also use nested mvappend() functions. The examples show required formatting.

mvcount(field)

Examples:

|fields event_time, url |makemv delim="/" url | calc total_count = mvcount(url_mv)

mvfind(field, regex)

Examples:

|calc res=mvfind(ipaddresses, "^127.*$")

|calc res=mvfind(commands, "login")

|calc res=mvfind(ports, "443")

|calc res = if(mvfind(ports, "8080") >= 0, 'found', 'not_found')

|calc res=mvfind(ipaddresses, "^127.*$"), 'found', 'not_found'

mvindex(<field>, <start>, <end>)

array_element(<field>, <start>, <end>)

array_elem(<field>, <start>, <end>)

Examples:

|calc res=mvindex(name_list_mv, 0, 2)

• <field> must be a multivalue field.

• <start>: Index of the first value to include. The first value in a multivalue field is index 0, the second value index 1, and so on. -1 specifies "the last value in the list".

• <end>: Index of the last value to include. -1 specifies "the last value in the list". <end> is optional.

mvindex(), array_element(), and array_elem() are equivalent.

mvjoin(<field>, <delimiter>)

Examples:

|fields src |makemv delim = '.' src |calc ips = mvjoin(src_mv, '!!') |fields joined

|fields src |makemv delim = '.' src |calc ips = mvjoin(src_mv, '-') |fields joined

Concatenates values within the multivalue field.

• <field> must be a multivalue field.

• <delimiter>: The string character in the multivalue field that separates the multiple values.

mvsort(field)

Example:

|calc res=mvsort(ipaddresses)

Sort the multivalue field lexicographically:

• Numbers before letters.

• Numbers are sorted by first digit. Example: 10, 8, 60, 100 are sorted lexicographically as 10, 100, 60, 8.

• Uppercase before lowercase letters.

mvzip(<field1>, <field2>, <delim>)

Example:

|calc suspicios_nodes = mvzip(suspicious_ips_mv, hostnames_mv)

|calc suspicios_users = mvzip(usernames, anomalous_activities, ' -> ') | fields suspicious_users

Creates a new multivalue field with concatenated values from two multivalue fields.

• <field1> and <field2> must be multivalue fields.

• <delim>: The string character in the multivalue field that separates the multiple values.

The values in the first field are zipped with the values from the second field. If the number of values ​​in the two fields does not match, the resulting multivalue has the length of the smaller list of values.

... | calc <entropy_score> = shannon_entropy (<string_to_evaluate>)

Examples:

eventlog | limit 1000 | calc entropy = shannon_entropy(url)

eventlog | limit 1000 | calc entropy = shannon_entropy(url) | where entropy > 7.5

eventlog | calc column_1 = shannon_entropy(sourcetype) | calc column_2 = shannon_entropy(user)

Calculates an entropy score for a string field. The score measures the randomness or unpredictability of the characters in the field value.

Parameters

• entropy_score: The name of the new column that stores the entropy score. The score is a decimal value between zero and eight.

• string_to_evaluate: An existing column with a string type to evaluate. Use specific fields such as url, filename, or process for best results. There are no option parameters or default values for this operator.

Entropy Score

1. The function scans each character in the input string.

2. It calculates how often each character appears relative to the total length of the string.

3. It assigns a score based on how evenly distributed, and therefore unpredictable, the characters are.

Strings with many different characters appearing in roughly equal proportions produce a high score. Strings that repeat the same characters frequently produce a low score.

The score is a decimal value from zero to eight:

• High Entropy (7.5–8.0): Likely encrypted, compressed, or packed files.

• Low Entropy (below 5.0): Likely plain text or structured data.

Use cases

• Malware Detection: Packed or encrypted malware has high entropy (entropy score > 7.5).

• Data Exfiltration: Detecting encrypted archives in network traffic.

• Ransomware Detection: Encrypted files have uniform high entropy.

• SIEM Integration: Entropy is useful as a detection feature for file activity logs.

<new field>=uuid()

Example:

|calc id=uuid()

Generates a new column with UUIDs.