Cortex XQL Command Reference

Cortex XQL (Extended Query Language) is a powerful query language used in the Cortex platform for threat hunting, investigation, and analytics across your security data. This reference provides comprehensive documentation for all XQL functions and pipeline stages.

XQL queries are composed of stages connected in a pipeline, with functions used within those stages to transform, filter, and analyze data. This reference is organized into two main sections:

• Functions – Built-in functions, indexes, and detailed reference pages.
• Stages – Pipeline stages, indexes, and detailed reference pages.

Functions

Stages

Cortex XQL (Cortex Query Language) is the query language for threat hunting, investigation, and analytics across security data in Cortex.

XQL queries are built as pipelines. Stages connect with the pipe (|) operator. Functions run inside those stages to transform, filter, and analyze data.

This reference is organized into three main sections.

• Functions – Built-in functions for data manipulation, type conversion, string operations, math, bitwise operations, and more.
• XQL JSON Functions Reference – Reference for json_extract, json_extract_scalar, json_extract_array, and json_extract_scalar_array.
• Stages – Pipeline stages that define query structure and flow.
• Browse the reference – Quick links to the main indexes and section entry points.

Use this page for quick access to the main XQL reference sections.

Start here

• Cortex XQL Command Reference
• Reference overview

Functions

• Functions
• Functions overview
• Functions list
• XQL JSON Functions Reference

Stages

• Stages
• Stages overview
• Stages list

XQL functions run inside stages and return a value.

Use this section to:

• Start with the overview
• Scan the full function index
• Jump to JSON functions

XQL functions run inside stages to transform, compare, and aggregate data.

They cover data manipulation, type conversion, string operations, math, arrays, JSON parsing, and more.

• Browse the full function index
• Open the JSON functions guide

The following table lists all available XQL functions:

Use the acos() function to calculate the principal value of the inverse cosine (arccosine) of a numerical expression.

Syntax

acos(<numeric_expression>)

Parameters

Returns

Type: float

Description: The acos() function returns the principal value of the arccosine of the input in radians. The resulting value is within the range [0, π].

Usage notes

• Input type: XQL doesn't support NaN or infinite values as input and these value types also can not be returned.
• Valid Input Range: The input numeric_expression must fall within the closed interval [-1, 1].
• Out of Range Behavior: If the input is outside the range of -1 to 1, the function returns NaN (Not a Number) or null.
• Null Handling: If the input expression is null, the function returns null.
• Radians to Degrees: The result is provided in radians. To convert the result to degrees, multiply the return value by 180 / PI().
• Common Use Cases: This function is typically utilized in the alter stage for geometric calculations, spatial analysis, or normalizing data vectors.

Examples

Example 1: Calculate arccosine of a literal value

Goal: Calculate the inverse cosine for specific numerical literals to see the radian results.

XQL Code:

dataset = xdr_data
| limit 1
| alter result1 = acos(1), result2 = acos(-1), result3 = acos(0)
| fields result1, result2, result3

Explanation: You use acos() on three different literal values. The function calculates the principal value of the inverse cosine for each. The results are in radians.

Output:

Example 2: Calculate arccosine from a field value

Goal: Calculate the arccosine of values stored in a specific field, ensuring they are within the valid mathematical range.

XQL Code:

dataset = sample_xql_raw
| filter duration_seconds >= -1 and duration_seconds <= 1
| alter arc_cos_val = acos(duration_seconds)
| fields event_id, duration_seconds, arc_cos_val
| limit 3

Explanation: This query first filters the dataset to ensure duration_seconds contains only values between -1 and 1, then computes the arccosine for each and stores it in arc_cos_val.

Related Articles

• Stages: alter, fields, limit
• Functions: cos, asin, atan
• Datasets: xdr_data

Use the add() function to calculate the sum of two numbers.

Syntax

add (<value_1>, <value_2>)

Parameters

Returns

The add() function returns the mathematical sum of the two input parameters.

Usage notes

• The function operates on numbers, supporting integer literals and floating-point numbers.
• The function also accepts field values that represent numbers, even if those values are stored as a string data type (for example, an integer stored as text).
• The function is typically used within the alter stage to create or modify fields based on calculated values.

Examples

Example 1: Adding an integer field and a literal integer

Goal: Add a fixed numerical value to an existing integer field (event_id) to create a new calculated field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter new_event_id = add(event_id, 100) // Adds 100 to the 'event_id' field 
| fields event_id, new_event_id 
| limit 3

Explanation: This query adds 100 to the event_id of each record, storing the result in a new field called new_event_id. For event_id 101, new_event_id becomes 201.

Output:

Example 2: Adding two integer literal values

Goal: Perform addition directly on two static integer values to create a constant new field for each record.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter sum_of_literals = add(500, 25) // Adds two literal integer values 
| fields event_id, sum_of_literals 
| limit 3

Explanation: This query adds the literal integers 500 and 25, producing a constant sum_of_literals value of 525 for each record.

Output:

Example 3: Adding a floating-point field and a literal float

Goal: Operate on a floating-point number field and a literal floating-point value.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter increased_duration = add(duration_seconds, 0.5) // Adds 0.5 to 'duration_seconds' 
| fields event_id, duration_seconds, increased_duration 
| limit 3

Explanation: The duration_seconds field (a float) is increased by 0.5 for each record. For event_id 101 (duration 1.5), increased_duration becomes 2.0.

Output:

Example 4: Adding an integer field and a negative literal

Goal: Handle a negative literal to effectively perform subtraction.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter adjusted_id = add(event_id, -10) // Subtracts 10 from 'event_id' 
| fields event_id, adjusted_id 
| limit 3

Explanation: This query subtracts 10 from the event_id of each record. For event_id 101, adjusted_id becomes 91.

Output:

Example 5: Adding a number extracted as string and a literal

Goal: Use add() with a numeric value extracted from a JSON string field, which is first converted to a number.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter status_code_str = simple_json_data -> code // Extract 'code' as string 
| alter status_code_num = to_number(status_code_str) // Convert string to number 
| alter new_code_value = add(status_code_num, 50) // Add 50 to the numeric code 
| fields event_id, simple_json_data, new_code_value 
| limit 3

Explanation: For event_id 101, the code "200" is extracted as a string, converted to a number, and then 50 is added, resulting in new_code_value of 250. For event_id 102, $.code is NULL, so new_code_value will also be NULL.

Output:

Example 6: Adding an element from an array field and a literal

Goal: Access an element from an array field and add a literal integer to it.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter first_numeric_code = arrayindex(numeric_codes, 0) // Get the first element of the array 
| alter increased_first_code = add(first_numeric_code, 10) // Add 10 to the first element 
| fields event_id, numeric_codes, increased_first_code 
| limit 3

Explanation: For event_id 101, the first element of numeric_codes (13) is extracted, and 10 is added to it, resulting in increased_first_code of 23.

Output:

Related articles

• Stages: alter, config, timeframe, fields, limit
• Functions: divide, multiply, subtract
• Datasets: xdr_data

Use the approx_count() function to calculate an approximate count of distinct values in a specified field.

Syntax

approx_count(<field>)

Parameters

Returns

The approx_count() function returns a single approximate integer value representing the number of distinct values found in the specified field.

Usage notes

• The approx_count() function is an approximate aggregate function. The function is designed to be more scalable in terms of memory usage and processing time compared to exact aggregate functions like count_distinct(), especially for large datasets.
• This function must be used with the comp stage.
• You can use the by clause in the comp stage to partition the data into groups, allowing approx_count() to compute the approximate distinct count independently for each group.
• You can use the addrawdata = true option in the comp stage to include a column listing the raw data events that contributed to the aggregate result.

Examples

Example 1: Calculate approximate distinct count across the entire dataset

Goal: Calculate the approximate number of unique event_description values for all records in the dataset.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_count(event_description) as unique_event_descriptions

Explanation: This query computes the approximate number of unique event_description values present in the sample_xql_raw dataset and names the resulting field unique_event_descriptions.

Output:

Example 2: Calculate approximate distinct count grouped by another field

Goal: Calculate the approximate number of unique dst_domain values separately for successful and unsuccessful events.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_count(dst_domain) as unique_domains_by_status by is_successful

Explanation: The query groups records by their is_successful status and then calculates the approximate number of unique dst_domain values for each group, presenting the counts in the unique_domains_by_status field.

Output:

Example 3: Calculate approximate distinct count with raw data inclusion

Goal: Calculate the approximate distinct count of event_description grouped by is_successful and include the raw events that contributed to the calculation.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_count(event_description) as unique_descriptions_by_status by is_successful addrawdata = true as raw_events_for_distinct_count

Explanation: This query calculates the approximate distinct count of event_description grouped by is_successful. Additionally, addrawdata = true generates a new column named raw_events_for_distinct_count, which contains a JSON representation of the raw events that contributed to each computed approximate distinct count.

Output:

Related articles

• Stages: comp, config, limit
• Functions: approx_quantiles, approx_top, count_distinct

Use the approx_quantiles() function to calculate and return approximate boundaries for a specified field, producing an array of values that define the quantiles.

Syntax

approx_quantiles(<field>, <number>, <distinct>)

Parameters

Returns

The approx_quantiles() function returns a single array containing <number> + 1 approximate values representing the boundaries of the quantiles. The first element represents the approximate minimum, and the last element represents the approximate maximum.

Usage notes

• The approx_quantiles() function is an approximate aggregate function designed to produce approximate results, instead of exact results used with regular aggregate functions, which is more scalable in terms of memory usage and time.
• This function must always be used with the comp stage.
• You can use the by clause with comp to calculate quantiles independently for different groups.
• You can use the addrawdata = true option to include a column listing the raw data events that contributed to the calculation. When you include raw data events, the query runs for up to 50 fields that you define and displays up to 100 events.

Examples

Example 1: Calculate approximate quantiles across the entire dataset

Goal: Calculate 3 approximate quantiles (4 boundaries) for the duration_seconds field across all records, considering all values (non-distinct).

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_quantiles(duration_seconds, 3) as approx_duration_quantiles

Explanation: This query calculates 3 quantiles for the duration_seconds field. The output is an array containing the approximate minimum, the 25th percentile, the 50th percentile (median), the 75th percentile, and the approximate maximum.

Output:

Example 2: Calculate approximate quantiles grouped by field

Goal: Calculate 2 approximate quantiles (3 boundaries: min, median, max) for duration_seconds, grouped by the is_successful status.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_quantiles(duration_seconds, 2) as approx_duration_quantiles by is_successful

Explanation: The query groups records by is_successful and calculates the approximate boundaries (min, median, max) of the duration_seconds values for each group.

Output:

Example 3: Calculate approximate quantiles for distinct values

Goal: Calculate 1 approximate quantile (2 boundaries: min, max) for event_description, considering only distinct values.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| comp approx_quantiles(event_description, 1, true) as approx_event_description_range

Explanation: This query sets the third parameter to true, instructing the function to consider only unique event_description values. The query finds the alphabetically first and last descriptions.

Output:

Related articles

• Stages: comp, config, limit
• Functions: approx_count, approx_top

Use the approx_top() function to return the most frequently occurring elements or highest-sum elements for a specified field. This approximate aggregate function produces results that are highly scalable in terms of memory usage and time compared to exact results from regular aggregate functions.

Syntax

comp approx_top(<string field>, <number>) [as <alias>[] [by <field1>[,<field2>...[][] [addrawdata = true|false [as <target field>[][]

comp approx_top(<string field>, <number>, <weight string field>) [as <alias>[] [by <field1>[,<field2>...[][][addrawdata = true|false [as <target field>[][]

Parameters

Returns

The approx_top() function returns a single array containing up to the specified number of JSON objects (structs). For the count variant, each struct will have "value" (the unique field value) and "count" (its approximate number of occurrences) keys. For the sum variant, each struct will have "value" and "sum" keys, representing the approximate sum calculated using the weight string field.

Usage Notes

• The comp stage must always precede an approximate aggregate function like approx_top().
• You can use the optional by clause to group rows based on one or more specified fields, which allows approx_top() to compute the approximate top elements independently for each group.
• When you set the optional addrawdata parameter to true, the query processes up to 50 defined fields and includes a raw_data column displaying up to 100 raw data events that contributed to the aggregate result.
• New columns created by the comp stage are typically added as the last columns in the result set, and any other fields not explicitly included in the by clause or as part of a calculated column is removed from the result set, including the _time system field.

Examples

Example 1: Calculating approximate top counts (no weight field)

Goal: Calculate the top 2 approximate counts of is_successful statuses across the entire dataset.

XQL Code:

config timeframe = 1d   
| dataset = sample_xql_raw   
| comp approx_top(is_successful, 2) as top_successful_statuses 

Explanation: The code identifies the top 2 most frequent values in the is_successful field. The result is an array of JSON objects, each with a "value" and a "count".

Output:

Example 2: Calculating approximate top sums (with weight field)

Goal: Calculate the top 2 dst_domain values by the sum of their duration_seconds across the entire dataset.

XQL Code:

config timeframe = 1d   
| dataset = sample_xql_raw   
| comp approx_top(dst_domain, 2, duration_seconds) as top_domains_by_duration_sum

Explanation: The code computes the sum of duration_seconds for each unique dst_domain and then returns the top 2 dst_domain values based on these sums.

Output:

Example 3: Calculating approximate top sums grouped by another field

Goal: Calculate the top 1 dst_domain by the sum of duration_seconds separately for is_successful = true and is_successful = false events.

XQL Code:

config timeframe = 1d   
| dataset = sample_xql_raw   
| comp approx_top(dst_domain, 1, duration_seconds) as top_domain_durations_by_status by is_successful

Explanation: Groups records by their is_successful status and calculates the single dst_domain with the highest sum of duration_seconds for each group.

Output:

Example 4: Calculating approximate top counts with raw data inclusion

Goal: Include the raw events that contribute to the approximate top count calculation by using the addrawdata = true option.

XQL Code:

config timeframe = 1d   
| dataset = sample_xql_raw   
| comp approx_top(is_successful, 1) addrawdata = true as raw_data_for_top_is_successful

Explanation: The code computes the top 1 most frequent is_successful value and automatically generates a new column named raw_data_for_top_is_successful containing a JSON representation of the raw events that contributed to this result.

Output:

Related Articles**

• Stages: comp, alter, config, timeframe
• Functions: approx_count, approx_quantiles

Use the array_all() function to determine if all elements within a specified array satisfy a defined boolean condition.

Syntax

array_all (<array>, "@element"<operator>"<array_element>")

Parameters

Returns

The array_all() function returns a boolean value (true or false). The function returns true only if every single element in the array meets the condition; otherwise, the function returns false.

Usage notes

• The function implements a strict "AND" operation across all elements. If even one element fails the condition, the entire function returns false.
• The array_all() function will return false if run against an empty array.
• The function is typically used within the alter or filter stages for data transformation or narrowing down results.

Examples

Example 1: Checking if all string tags are a specific value

Goal: Verify if every tag in the string_tags array is exactly "security".

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter all_tags_are_security = array_all(string_tags, "@element" = "security") 
| fields event_id, string_tags, all_tags_are_security 
| limit 5

Explanation: For event_id 101, the array ["security", "login"] returns false because "login" is not "security". For event_id 104, [] (an empty array) returns false.

Output:

Example 2: Checking if all numeric codes are greater than zero

Goal: Evaluate if every number in the numeric_codes array is greater than zero.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter all_codes_positive = array_all(numeric_codes, "@element" > 0) 
| fields event_id, numeric_codes, all_codes_positive 
| limit 5

Explanation: For event_id 101, the array [13, -47, 29, 82, -15] returns false because elements like -47 and -15 are not greater than zero. For event_id 104, [] (an empty array) returns false.

Output:

Example 3: Checking if all string tags are NOT a specific value

Goal: Check if all tags in string_tags are not equal to "security" using the != operator.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter none_are_security = array_all(string_tags, "@element" != "security") 
| fields event_id, string_tags, none_are_security 
| limit 5

Explanation: For event_id 101, the array ["security", "login"] returns false because "security" is present in the array, failing the "not equal to security" condition. For event_id 102, the array ["filesystem", "critical"] returns true because neither "filesystem" nor "critical" are equal to "security".

Output:

Related articles

• Stages: alter, config, fields, limit
• Functions: array_any

Use the array_any() function to determine if at least one element within a specified array satisfies a defined boolean condition. If at least one element meets the condition, the function returns true.

Syntax

array_any (<array>, "@element"<operator>"<array_element>")

Parameters

Returns

The array_any() function returns a boolean value (true or false).

Usage notes

• The function iterates through the array and applies the condition to each element individually.
• The function implements a logical "OR" operation across the elements. If any single element satisfies the condition, the function returns true.
• If the input array is empty, the function returns false.
• Supported operators within the condition include standard comparison operators such as =, !=, >, <, >=, and <=.
• This function is typically used within the alter or filter stages to categorize data or narrow down results based on array contents.

Examples

Example 1: Check for specific string value

Goal: Check if the string_tags array contains the specific value "security".

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter any_tag_is_security = array_any(string_tags, "@element" = "security") 
| fields event_id, string_tags, any_tag_is_security 
| limit 6 

Explanation: The query evaluates the string_tags array for each event. If the string "security" is present as any element in the array, any_tag_is_security is set to true.

Output:

Example 2: Check numeric threshold

Goal: Determine if any number within the numeric_codes array is greater than 50.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter any_code_gt_50 = array_any(numeric_codes, "@element" > 50) 
| fields event_id, numeric_codes, any_code_gt_50 
| limit 5 

Explanation: The query checks the numeric_codes array. If at least one number in the array is greater than 50, the result is true. Empty arrays return false.

Output:

Example 3: Check inequality

Goal: Determine if at least one tag in the string_tags array is not equal to "security".

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter any_tag_is_not_security = array_any(string_tags, "@element" != "security") 
| fields event_id, string_tags, any_tag_is_not_security 
| limit 6 

Explanation: The query returns true if it finds any element in the array that is not "security". For example, in event 101, even though "security" is present, the presence of "login" (which is != "security") makes the result true.

Output:

Related articles

• Stages: alter, filter
• Functions: array_all, arrayfilter
• Datasets: xdr_data

Use the array_length() function to return the number of elements in an array.

Syntax

array_length (<array>)

Parameters

Returns

The array_length() function returns an integer representing the count of elements in the specified array.

Usage notes

• If the input array is empty, the function returns 0.
• The function provides a direct count of how many items are present within an array field.
• This function is typically used within the alter stage to create new fields or modify existing ones by calculating array sizes.
• The function can also be used in filter stages to narrow down results based on string length.

Examples

Example 1: Calculating the length of a string array

Goal: Determine the number of string elements in an array field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter num_string_tags = array_length(string_tags) 
| fields event_id, string_tags, num_string_tags 
| limit 4

Explanation: For event_id 101, 102, and 103, string_tags contains two elements, so num_string_tags is 2. For event_id 104, string_tags contains one element, so num_string_tags is 1.

Output:

Example 2: Calculating the length of a numeric array

Goal: Determine the number of numeric elements in an array field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter num_numeric_codes = array_length(numeric_codes) 
| fields event_id, numeric_codes, num_numeric_codes 
| limit 6

Explanation: For event_id 101, 102, and 105, numeric_codes contains five elements. For event_id 103 and 106, numeric_codes contains six elements. For event_id 104, numeric_codes is an empty array, so num_numeric_codes is 0.

Output:

Example 3: Calculating the length of an array of JSON objects

Goal: Determine the number of JSON objects contained within an array field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter num_json_objects = array_length(array_of_json_objects) 
| fields event_id, array_of_json_objects, num_json_objects 
| limit 4

Explanation: For event_id 101 and 103, array_of_json_objects contains two elements. For event_id 102, array_of_json_objects contains one element. For event_id 104, array_of_json_objects is an empty array, so num_json_objects is 0.

Output:

Example 4: Using array_length() in a filter to check for non-empty arrays

Goal: Filter events to include only those where a specific array field is not empty.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw
| alter tags_count = array_length(string_tags)
| filter tags_count > 0
| fields event_id, string_tags, tags_count 
| limit 5

Explanation: This query returns all events where the string_tags array has at least one element. No events with an empty string_tags array are present in this filtered result set.

Output:

Example 5: Using array_length() in a filter to check for a specific array size

Goal: Filter events to include only those where an array field contains a precise number of elements.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw
| alter codes_count = array_length(numeric_codes)
| filter codes_count = 5 
| fields event_id, numeric_codes, codes_count 
| limit 5

Explanation: Only events with exactly five elements in their numeric_codes array are returned, such as event_id 101, 102, and 105. Events like 103 (length 6) or 104 (length 0) are excluded by the filter.

Output:

Related articles

• Stages: alter, filter, fields
• Functions: array_all, array_any

Use the arrayconcat() function to combine the elements of two or more specified arrays into a single, new array.

Syntax

arrayconcat (<array1>, <array2>[, <array3>...])

Parameters

Returns

The arrayconcat() function returns a single, new array that contains all elements from the input arrays.

Usage notes

• All the elements within the input array fields must be of the same data type.
• The function joins the input arrays sequentially. The elements from array1 come first, followed by elements from array2, and so on, preserving their original order within each array.
• arrayconcat() simply joins the elements and does not inherently remove duplicate values. If you need a result array with only unique elements, apply the arraydistinct() function after arrayconcat().
• This function is typically employed within the alter stage to create new fields or modify existing ones by combining array data.

Examples

Example 1: Concatenating two newly created arrays

Goal: Define two new arrays using arraycreate() and then concatenate them to form a single combined array.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter first_part = arraycreate("componentA", "statusX") // Creates a new array 
| alter second_part = arraycreate("typeB", "severityY")   // Creates another new array 
| alter combined_info = arrayconcat(first_part, second_part) // Concatenates the two new arrays 
| fields event_id, first_part, second_part, combined_info 
| limit 2 

Explanation: For each record, two literal arrays, first_part and second_part, are created. The function then combines the elements of first_part followed by second_part into the combined_info array.

Output:

Example 2: Concatenating an existing array field with a newly created array

Goal: Combine the string_tags array field from the dataset with a new array created using arraycreate().

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter new_event_tags = arraycreate("audit", "compliance") // Creates a new array of tags 
| alter extended_string_tags = arrayconcat(string_tags, new_event_tags) // Concatenates existing string_tags with the new tags 
| fields event_id, string_tags, new_event_tags, extended_string_tags 
| limit 3 

Explanation: For each event, new_event_tags is created. The existing string_tags are then concatenated with the elements from new_event_tags to form extended_string_tags. For example, event_id 101's tags are expanded to include "audit" and "compliance".

Output:

Example 3: Concatenating multiple derived arrays

Goal: Combine portions of the numeric_codes array using arrayrange() and a newly created array, demonstrating concatenation with more than two inputs.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter first_two_codes = arrayrange(numeric_codes, 0, 2) // Extracts elements at index 0 and 1 
| alter next_two_codes = arrayrange(numeric_codes, 2, 4)   // Extracts elements at index 2 and 3 
| alter additional_codes = arraycreate(999, 1000)        // Creates an array with two new numbers 
| alter all_combined_codes = arrayconcat(first_two_codes, next_two_codes, additional_codes) // Concatenates three arrays 
| fields event_id, numeric_codes, first_two_codes, next_two_codes, additional_codes, all_combined_codes 
| limit 3 

Explanation: arrayrange() extracts specific slices (sub-arrays) from the numeric_codes array. arraycreate() is used to define an additional array. The function then takes these two derived arrays and the additional_codes array, joining all their elements into all_combined_codes.

Output:

Related articles

• Stages: alter, config, fields, limit
• Functions: arraycreate, arraydistinct, arrayrange

Use the arraycreate() function to return a new array based on the given parameters defined for its elements.

Syntax

arraycreate ("<array_element1>", "<array_element2>",...)

Parameters

Returns

The arraycreate() function returns a single, new array containing the specified elements.

Usage notes

• All elements within the array created by arraycreate() must be of the same data type.
• The function returns the data types exactly as entered and doesn't implicitly convert string representations of numeric or boolean values. For example, "123" remains a string "123" within the array, and isn't converted to a number.
• If all elements are NULL, the output is an empty array with no elements.

Examples

Example 1: Creating an array of string literals

Goal: Define a new array containing only direct string values.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter custom_tags = arraycreate("alert", "investigation_needed") 
| fields event_id, custom_tags 
| limit 3

Explanation: For each record in sample_xql_raw, a new field named custom_tags is created. This field contains an array with two string elements: "alert" and "investigation_needed".

Output:

Example 2: Creating an array with literals representing different data types (as strings)

Goal: Handle different types of literal values, specifically strings, numbers, and booleans, by treating them as string parameters.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter mixed_info = arraycreate("version_1.0", "123", "true", "45.67") 
| fields event_id, mixed_info 
| limit 3

Explanation: A mixed_info array is generated for each record. The query demonstrates that arraycreate() can incorporate various literal values into a single array, with all elements being treated as strings, adhering to the rule that all elements must be of the same data type.

Output:

Example 3: Creating an array by combining literals with existing field values

Goal: Dynamically build an array by including both static literal values and values from existing fields in each record.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter event_summary = arraycreate(to_string(event_id), event_description, to_string(is_successful)) 
| fields event_id, event_description, is_successful, event_summary 
| limit 3

Explanation: The to_string() function is used to convert the event_id (numeric) and is_successful (boolean) fields into strings, allowing them to be combined with the event_description (string) into a new array called event_summary. Each row will have a unique event_summary array reflecting its own event_id, event_description, and is_successful status.

Output:

Example 4: Creating an array with derived values from JSON extraction

Goal: Incorporate values that are the result of other XQL functions, specifically json_extract_scalar() for extracting data from JSON fields.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter status_code = json_extract_scalar(simple_json_data, "$.code") 
| alter user_name = json_extract_scalar(nested_json_data, "$.user.name") 
| alter extracted_details = arraycreate(status_code, user_name) 
| fields event_id, simple_json_data, nested_json_data, extracted_details 
| limit 3

Explanation: status_code is created by extracting the code field from simple_json_data. user_name is created by extracting the user.name field from nested_json_data. extracted_details then combines these two (potentially NULL) string values into a new array. If all the input elements are NULL, then the output is an empty array.

Output:

Related articles

• Stages: alter
• Functions: arrayconcat, to_string, json_extract_scalar
• Datasets: xdr_data

Use the arraydistinct() function to return a new array containing only the unique values found in the original input array.

Syntax

arraydistinc (<array>)

Parameters

Returns

The arraydistinct() function returns a new array, where all elements are unique.

Usage notes

• Only one instance of each value is retained in the resulting array.
• The function operates on elements as they are typed. For example, if "100" (string) and 100 (number) were distinct elements in the input array, they would be considered distinct in the output.
• If the input array is empty, arraydistinct() will return an empty array.

Examples

Example 1: Removing duplicate string literals from a newly created array

Goal: Create an array with intentional string duplicates and then process it to retain only unique string values.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter raw_array_with_duplicates = arraycreate("alert", "security", "alert", "investigation_needed", "security") 
| alter distinct_tags = arraydistinct(raw_array_with_duplicates) 
| fields event_id, raw_array_with_duplicates, distinct_tags 
| limit 3 

Explanation: A new field raw_array_with_duplicates is created for each record using arraycreate(), containing repeated string values "alert" and "security". arraydistinct() is then applied to produce distinct_tags, which contains only the unique string values.

Output:

Example 2: Removing duplicate numeric literals from a newly created array

Goal: Process an array composed of numeric literals, including duplicates, and yield an array with only unique numeric values.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter raw_numbers_with_duplicates = arraycreate(100, 50, 100, 50, 25, 100) 
| alter distinct_numbers = arraydistinct(raw_numbers_with_duplicates) 
| fields event_id, raw_numbers_with_duplicates, distinct_numbers 
| limit 3 

Explanation: raw_numbers_with_duplicates is created as an array of integers with repeated values. arraydistinct() effectively identifies and removes these numerical duplicates, resulting in distinct_numbers containing only the unique integer values.

Output:

Example 3: Applying arraydistinct() to an existing array field

Goal: Demonstrate how arraydistinct() handles an existing array field that does not contain duplicates within its individual elements.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter original_tags = string_tags 
| alter processed_tags = arraydistinct(original_tags) 
| fields event_id, original_tags, processed_tags 
| limit 3 

Explanation: The original_tags field holds the existing string_tags array. arraydistinct() processes this array to produce processed_tags. In cases where the original array contains no duplicates, the output array will be identical to the input.

Output:

Example 4: arraydistinct() on an array constructed from mixed field values with duplicates

Goal: Build an array by combining elements from different fields, introducing an explicit duplicate, and then use arraydistinct() to resolve it.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter first_tag = arrayindex(string_tags, 0) 
| alter combined_values_raw = arraycreate(first_tag, first_tag, to_string(event_id)) 
| alter unique_combined_values = arraydistinct(combined_values_raw) 
| fields event_id, combined_values_raw, unique_combined_values 
| limit 3 

Explanation: combined_values_raw is constructed using string elements (first_tag repeated, and event_id converted to a string), creating a duplicate. arraydistinct() processes this array, removing the repeated first_tag to yield unique_combined_values.

Output:

Example 5: arraydistinct() on a concatenated array with duplicates

Goal: Use arraydistinct() on an array formed by concatenating an existing array with itself, which inherently introduces duplicates.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter duplicate_numeric_codes = numeric_codes 
| alter duplicated_numeric_array = arrayconcat(numeric_codes, duplicate_numeric_codes) 
| alter distinct_concatenated_numeric = arraydistinct(duplicated_numeric_array) 
| fields event_id, duplicate_numeric_codes, duplicated_numeric_array, distinct_concatenated_numeric 
| limit 3 

Explanation: arrayconcat() is used to join the numeric_codes array with itself, creating duplicated_numeric_array. arraydistinct() is then applied, effectively reducing it back to the original set of unique elements in distinct_concatenated_numeric.

Output:

Related articles

• Stages: alter, config, fields, limit
• Functions: arraycreate, arrayindex, arrayconcat, to_string

Use the arrayfilter() function to create a new array containing only the elements from the original input array that satisfy a specified boolean condition.

Syntax

arrayfilter (<array>, <condition>)

or

arrayfilter (<array>, "@element"<operator>"<array_element>")

Parameters

Returns

The arrayfilter() function returns a new array containing only the elements that meet the specified condition.

Usage notes

• The specified condition is applied individually to every element in the input array.
• If the input array is empty, the function will return an empty array.
• This function is commonly used within the alter or filter stages to perform data transformations or to narrow down query results.

Examples

Example 1: Filtering a string array for specific elements

Goal: Filter the string_tags array to include only tags that match the specific value "security".

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter security_tags = arrayfilter(string_tags, "@element" = "security") 
| fields event_id, string_tags, security_tags 
| limit 3 

Explanation: For event_id 101, "security" matches the condition, so it is included in the new array, while "login" is excluded. For events 102 and 103, no elements match, resulting in an empty array.

Output:

Example 2: Filtering a numeric array for values greater than a threshold

Goal: Filter the numeric_codes array to include only numbers greater than 50.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter large_numeric_codes = arrayfilter(numeric_codes, "@element" > 50) 
| fields event_id, numeric_codes, large_numeric_codes 
| limit 3 

Explanation: The function iterates through the numeric_codes array. For event_id 101, only 82 is greater than 50. For 102, only 56 is greater. For 103, both 90 and 51 are retained.

Output:

Example 3: Filtering a string array for elements not equal to a value

Goal: Filter the string_tags array to include only tags that are not equal to "login".

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter non_login_tags = arrayfilter(string_tags, "@element" != "login") 
| fields event_id, string_tags, non_login_tags 
| limit 3 

Explanation: For event_id 101, "security" is not "login", so it is included, while "login" is excluded. For events 102 and 103, all elements satisfy the condition (are not "login"), so the entire arrays are retained.

Output:

Example 4: Filtering an array and checking its length in a filter stage

Goal: Create a new array by filtering string_tags for elements containing the substring "crit", and then filter the dataset to only show records where this new array is not empty.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter critical_event_tags = arrayfilter(string_tags, "@element" contains "crit") 
| filter array_length(critical_event_tags) > 0 
| fields event_id, string_tags, critical_event_tags 
| limit 3 

Explanation: First, critical_event_tags is created containing only tags that include "crit". For event_id 102, this results in ["critical"]. The filter stage then uses array_length() to retain only records where the filtered array has elements.

Output:

Related articles

• Stages: alter, filter, fields
• Functions: array_length
• Datasets: xdr_data

Use the arrayindex() function to retrieve a single element from a specified array by its numerical position, or index.

Syntax

arrayindex (<array>, <index>)

Parameters

Returns

The arrayindex() function returns a single value, which is the element located at the specified index within the array.

Usage notes

• Array elements are accessed using a zero-based index, meaning the first element is at index 0, the second at index 1, etc.
• If the specified index is beyond the array's boundaries or if the array itself is empty, arrayindex() will return NULL for the field and not an empty array.
• This function supports negative indices (for example, -1 for the last element, -2 for the second to last element).

Examples

Example 1: Extracting the first element (Index 0) from a string array

Goal: Retrieve the first string tag from the string_tags array.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter first_tag = arrayindex(string_tags, 0) // Extracts the first element 
| fields event_id, string_tags, first_tag 
| limit 3 

Explanation: For each event_id, the function extracts the first string element (index 0) from the string_tags array.

Output:

Example 2: Extracting a middle element (Index 2) from a numeric array

Goal: Retrieve the third numeric code (at index 2) from the numeric_codes array.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter third_numeric_code = arrayindex(numeric_codes, 2) // Extracts the element at index 2 
| fields event_id, numeric_codes, third_numeric_code 
| limit 3 

Explanation: For each event_id, the function extracts the element at index 2 (the third element) from the numeric_codes array.

Output:

Example 3: Handling an empty array

Goal: Demonstrate the behavior when attempting to extract an element from an empty array.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 104 // Focus on the event with an empty array 
| alter first_numeric_code_empty = arrayindex(numeric_codes, 0) // Attempt to extract from empty array 
| fields event_id, numeric_codes, first_numeric_code_empty 
| limit 1 

Explanation: For event_id 104, the numeric_codes array is empty. As expected, the function returns NULL when no element exists at the specified index due to an empty array.

Output:

Example 4: Handling an out-of-bounds index

Goal: Demonstrate the behavior when the specified index is beyond the actual size of the array.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter out_of_bounds_code = arrayindex(numeric_codes, 100) // Index 100 is far beyond array size 
| fields event_id, numeric_codes, out_of_bounds_code 
| limit 3 

Explanation: For all event_ids, numeric_codes does not have an element at index 100. The function correctly returns NULL when the index is out of bounds.

Output:

Example 5: Retrieving the last element (-1 Index)

Goal: Extract the last element from an array using the negative index -1.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter last_numeric_code = arrayindex(numeric_codes, -1) // Extracts the last element using negative index 
| fields event_id, numeric_codes, last_numeric_code 
| limit 3 

Explanation: Using index -1 retrieves the final element of the numeric_codes array for each record.

Output:

Example 6: Retrieving the second to last element (-2 Index)

Goal: Retrieve the second to last element from an array using the negative index -2.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter second_to_last_numeric_code = arrayindex(numeric_codes, -2) // Extracts the second to last element 
| fields event_id, numeric_codes, second_to_last_numeric_code 
| limit 3 

Explanation: Using index -2 retrieves the element immediately preceding the last element in the numeric_codes array.

Output:

Related articles

• Stages: alter
• Functions: arraycreate, array_length

Use the arrayindexof() function to return the index of the first occurrence of a specified element in an array that satisfies a condition, or 0 if a general boolean condition matches.

Syntax

arrayindexof (<array>, <condition>)
arrayindexof (<array>, "@element"<operator>"<array element>")

Parameters

Returns

The arrayindexof() function returns an integer (0 or a 0-based index) or NULL.

Usage notes

• If the condition is a general boolean expression not using @element, the function returns 0 if the array is not empty and the condition is true.
• If the condition uses @element to check individual array elements, the function returns the 0-based index of the first array element that satisfies the condition.
• If the input array is empty, arrayindexof() returns NULL.
• If the condition (general or @element-based) is not met by any element, the function returns NULL.

Examples

Example 1: General condition - checking array length

Goal: Check if the string_tags array contains more than one element using a general condition.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter has_multiple_tags = arrayindexof(string_tags, array_length(string_tags) > 1) 
| fields event_id, string_tags, has_multiple_tags 
| limit 4 

Explanation: The query uses the arrayindexof(<array>, <condition>) variant. For event_id 101, 102, and 103, the length is 2, so the condition matches and returns 0. For event_id 104, the length is 1, so the condition fails and returns NULL.

Output:

Example 2: Element-specific condition - checking for a specific string value

Goal: Find the index of the first occurrence of the tag "login" in the string_tags array.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter first_index_of_login_tag = arrayindexof(string_tags, "@element" = "login") 
| fields event_id, string_tags, first_index_of_login_tag 
| limit 5 

Explanation: The query uses the @element keyword to check each item. For event_id 101, "login" is found at index 1, so 1 is returned. For other events where "login" is missing, NULL is returned.

Output:

Example 3: Element-specific condition - checking for a numeric value greater than a threshold

Goal: Find the index of the first numeric code in the numeric_codes array that is greater than 50.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter first_index_of_large_code = arrayindexof(numeric_codes, "@element" > 50) 
| fields event_id, numeric_codes, first_index_of_large_code 
| limit 6 

Explanation: This query searches the entire array and returns the index of the first match. For event_id 101, 82 is the first value > 50 (at index 3). For event_id 102, 56 is the first match (at index 1).

Output:

Example 4: Element-specific condition - checking for a value not equal to a specific string

Goal: Find the index of the first element in string_tags that is not "security".

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter first_index_of_non_security = arrayindexof(string_tags, "@element" != "security") 
| fields event_id, string_tags, first_index_of_non_security 
| limit 5 

Explanation: For event_id 101, "security" is at index 0, but "login" is at index 1 and matches the condition != "security", so 1 is returned. For event_id 102, "filesystem" at index 0 matches, so 0 is returned.

Output:

Example 5: Handling an empty array explicitly

Goal: Demonstrate that arrayindexof() returns NULL when applied to an empty array.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 104 // Focus on the event with an empty array 
| alter index_in_empty_array = arrayindexof(numeric_codes, "@element" = 0) 
| fields event_id, numeric_codes, index_in_empty_array 
| limit 1 

Explanation: For event_id 104, the numeric_codes array is empty. As per the function's definition, if the array is empty, NULL is returned.

Output:

Related articles

• Stages: alter, config, fields, filter, limit
• Functions: array_length

Use the arraymap() function to apply a specified callable function to every element of an input array, returning a new array containing the transformed elements.

Syntax

arraymap (<array>, <function()>)

Parameters

Returns

The arraymap() function returns a new array, where each element is the result of applying the specified function to the corresponding element of the original array.

Usage notes

• The function iterates through each element, applies the defined transformation, and collects the results into a new array.
• When the function applied within arraymap() needs to reference the current array element being processed, it uses "@element" as a placeholder for that element.
• arraymap() is typically used within the alter or filter stages for data transformations.

Examples

Example 1: Applying a simple mathematical transformation to a numeric array

Goal: Multiply each element in a numeric array by a specific constant.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter multiplied_codes = arraymap(numeric_codes, multiply("@element", 10))
| fields event_id, numeric_codes, multiplied_codes
| limit 4

Explanation: For each record, arraymap() iterates over the numeric_codes array. The multiply() function takes each "@element" (for example, 13) and multiplies it by 10. The results form the new multiplied_codes array. An empty input array (event_id 104) results in an empty output array.

Output:

Example 2: Applying a string transformation (uppercase) to a string array

Goal: Convert all string elements within an array to uppercase.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter uppercase_tags = arraymap(string_tags, uppercase("@element"))
| fields event_id, string_tags, uppercase_tags
| limit 4

Explanation: The arraymap() function processes each element in string_tags (for example, "security"). The uppercase() function converts "@element" to its uppercase equivalent. The transformed elements form the uppercase_tags array.

Output:

Example 3: Applying a conditional transformation (replacing negative values) to a numeric array

Goal: Replace any negative numbers in an array with 0, while keeping positive numbers unchanged.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter positive_codes = arraymap(numeric_codes, if("@element" < 0, 0, "@element"))
| fields event_id, numeric_codes, positive_codes
| limit 4

Explanation: For each number in numeric_codes, the if() function checks if "@element" is less than 0. If true, it is replaced with 0; otherwise, the original "@element" value is retained.

Output:

Example 4: Extracting scalar values from JSON Objects within an array

Goal: Iterate through an array of JSON objects and extract a specific scalar value (for example, action or event) from each object into a new array.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter extracted_actions = arraymap(array_of_json_objects, coalesce(
        json_extract_scalar(to_json_string("@element"), "$.action"),
        json_extract_scalar(to_json_string("@element"), "$.event")
    ))
| fields event_id, array_of_json_objects, extracted_actions
| limit 4