alter ↗

Use the alter stage to manipulate data by changing existing field values or creating new fields based on constants, other fields, or the results of various XQL functions.

Syntax

alter <field1> = <function value1> [, <field2> = <function_value2>, ...]

Parameters

Name Type Required Description
<field> string Yes The name of the field to create or modify.
<function_value> various Yes The constant, field reference, or function result to assign to the field.

Returns

The alter stage returns the dataset with the specified fields modified or added. New columns created by the alter stage are added as the last columns in the result set.

Usage notes

  • New fields and field values can only be created in the alter stage. No other stage allows for the creation of new fields and/or values.
  • After defining or modifying a field with alter, you can apply other stages, such as filter, to this new or modified field.
  • New columns created by the alter stage are added as the last columns in the result set. If a specific column order is desired, it can be adjusted using the fields stage later in the query.
  • XQL supports single ("<text>") and triple ("""<text>""") double quotes for defining string fields.
  • Single quotes treat values literally, while triple quotes process escape sequences (like \t for tab) and escaped backslashes. Choose the appropriate quoting based on your desired interpretation of special characters.

Examples

Example 1: Creating a new field with a constant value

Goal: Add a new column with a fixed value for all records.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter analysis_tag = "initial_review"
| fields event_id, analysis_tag

Explanation: A new field analysis_tag is created, and the value "initial_review" is assigned to every record.

Output:

EVENT_ID ANALYSIS_TAG
101 initial_review
102 initial_review
103 initial_review

Example 2: Creating a new field based on an existing field

Goal: Duplicate a field or create a new field that directly references an existing one.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter replicated_description = event_description
| fields event_id, replicated_description

Explanation: The replicated_description field is created, mirroring the event_description field.

Output:

EVENT_ID REPLICATED_DESCRIPTION
101 User login successful
102 File access attempt
103 Network connection established

Example 3: Modifying an existing field

Goal: Overwrite the current value of a known field.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter event_description = concat("Processed: ", event_description)
| fields event_id, event_description

Explanation: The original event_description is updated by prepending "Processed: " to its value.

Output:

EVENT_ID EVENT_DESCRIPTION
101 Processed: User login successful
102 Processed: File access attempt
103 Processed: Network connection established

Example 4: Using string manipulation functions (concat)

Goal: Join multiple strings together to reformat text data.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter event_summary = concat(event_description, " - Success: ", to_string(is_successful))
| fields event_id, event_summary

Explanation*: event_summary is created by concatenating the event description, a static string, and the boolean is_successful converted to a string.

Output:

EVENT_ID EVENT_SUMMARY
101 User login successful - Success: true
102 File access attempt - Success: false
103 Network connection established - Success: true

Example 5: Using string manipulation functions (lowercase)

Goal: Convert a string to all lowercase letters.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter lower_case_log = lowercase(raw_log_data)
| fields event_id, lower_case_log

Explanation: The raw_log_data field's content is converted to lowercase and stored in lower_case_log.

Output:

EVENT_ID LOWER_CASE_LOG
101 user alice logged in from 192.168.1.10
102 process cmd.exe attempted to access /etc/passwd

Example 6: Using string manipulation functions (split)

Goal: Divide a string into an array of substrings based on a delimiter.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter log_words = split(raw_log_data, " ")
| fields event_id, log_words

Explanation: The raw_log_data string is split into an array of words based on spaces, creating the log_words field.

Output:

EVENT_ID LOG_WORDS
101 ["User", "Alice", "logged", "in", "from", "192.168.1.10"]
102 ["Process", "cmd.exe", "attempted", "to", "access", "/etc/passwd"]

Example 7: Using JSON functions (json_extract_scalar)

Goal: Extract a single scalar value (for example, string, number, boolean) from a JSON object.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter json_status = json_extract_scalar(simple_json_data, "$.status")
| fields event_id, json_status

Explanation: Extracts the value of the "status" key from the simple_json_data JSON object into json_status.

Output:

EVENT_ID JSON_STATUS
101 ok
102 fail

Example 8: Using JSON functions (json_extract_array)

Goal: Convert a JSON array string to an XQL-native array.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter parsed_actions = json_extract_array(array_of_json_objects, "$")
| fields event_id, parsed_actions

Explanation: Converts the array_of_json_objects string (which contains a JSON array) into a native XQL array stored in parsed_actions.

Output:

EVENT_ID PARSED_ACTIONS
101 [{"action": "read", "file": "doc1.txt"}, {"action": "write", "file": "report.log"}]
102 [{"event": "file_open", "path": "/etc/passwd"}]

Example 9: Using conditional and null handling functions (if)

Goal: Apply logic to assign values based on conditions.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter outcome_category = if(is_successful, "Operational", "Alertable")
| fields event_id, outcome_category

Explanation: If is_successful is true, creates outcome_category as "Operational". If is_successful is false, creates outcome_category as "Alertable".

Output:

EVENT_ID OUTCOME_CATEGORY
101 Operational
102 Alertable
103 Operational

Example 10: Using conditional and null handling functions (coalesce)

Goal: Return the first non-NULL expression among its arguments.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter resolved_ip = coalesce(ipv4_address, ipv6_address)
| fields event_id, resolved_ip

Explanation: Assigns the value of ipv4_address to resolved_ip. If ipv4_address is NULL, the field is assigned ipv6_address instead.

Output:

EVENT_ID RESOLVED_IP
101 192.168.1.10
103 2001:0db8::1

Example 11: Using time functions (format_timestamp)

Goal: Format a timestamp into a specified string format.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter formatted_timestamp = format_timestamp("%Y-%m-%d %H:%M:%S", _time)
| fields event_id, formatted_timestamp

Explanation: Converts the _time field into a readable string format YYYY-MM-DD HH:MM:SS.

Output:

EVENT_ID FORMATTED_TIMESTAMP
101 2023-10-26 10:00:00
102 2023-10-26 10:05:30

Example 12: Using mathematical functions (add)

Goal: Add two numerical values.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter adjusted_duration = add(duration_seconds, 5.0)
| fields event_id, adjusted_duration

Explanation: Adds 5.0 to duration_seconds for each record, storing the result in adjusted_duration.

Output:

EVENT_ID ADJUSTED_DURATION
101 6.5
102 5.8

Example 13: Using mathematical functions (multiply)

Goal: Multiply two numerical values.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter doubled_duration = multiply(duration_seconds, 2)
| fields event_id, doubled_duration

Explanation: Multiplies duration_seconds by 2, storing the result in doubled_duration.

Output:

EVENT_ID DOUBLED_DURATION
101 3.0
102 1.6

Example 14: Using mathematical functions (divide)

Goal: Perform division on numerical values.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter duration_in_minutes = divide(duration_seconds, 60)
| fields event_id, duration_in_minutes

Explanation: Converts duration_seconds to minutes by dividing by 60, storing the result in duration_in_minutes.

Output:

EVENT_ID DURATION_IN_MINUTES
101 0.025
102 0.0133

Example 15: Using array functions (arraycreate)

Goal: Form a new array from specified values.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter default_tags = arraycreate("default_tag", "processed")
| fields event_id, default_tags

Explanation: Creates a new array default_tags containing the specified string values for each record.

Output:

EVENT_ID DEFAULT_TAGS
101 ["default_tag", "processed"]
102 ["default_tag", "processed"]

Example 16: Using array functions (arraymap)

Goal: Apply a function to each element of an array.

XQL code:

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

Explanation: Creates positive_codes by going through the array numeric_codes and replacing any negative values with 0.

Output:

EVENT_ID POSITIVE_CODES
101 [13, 0, 29, 82, 0]
102 [0, 56, 13, 0, 42]

Example 17: Using array functions (arrayrange)

Goal: Generate a portion of an array based on specified indices.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter first_two_tags = arrayrange(string_tags, 0, 2)
| fields event_id, first_two_tags

Explanation: Extracts the first two elements (indices 0 and 1) from the string_tags array into first_two_tags.

Output:

EVENT_ID FIRST_TWO_TAGS
101 ["security", "login"]
102 ["filesystem", "critical"]