json_extract_scalar

Cortex XDR XQL json_extract_scalar() function accepts a string representing a JSON object, and returns a field value from that object.

Synopsis

json_extract_scalar(<
json_object_formatted_string
>, <
field_path
>)
To make it easier for you to write your XQL queries, you can also use the following syntactic sugar format.
<json_object_formatted_string> -> <field_path>

Description

The
json_extract_scalar()
function accepts a string representing a JSON object, and it retrieves the value from the identified field as a string. This function always returns a string. If the JSON field is an object or array, it will return a null value. To retrieve an XQL-native datatype, use an appropriate function, such as
to_float
or
to_integer
. If the input string does not represent a JSON object, this function fails to parse. To convert a string field to a JSON object, use the to_json_string function.
See json_extract for information on
field_path
.

Examples

Return the
storage_device_drive_type
value from the
action_file_device_info
field, and return the record if it is 1.
There are two ways that you can build this query either with a filter using an XQL-native datatype or string.
Option A - Filter using an XQL-native datatype
dataset = xdr_data | fields action_file_device_info as afdi | alter sdn = to_integer(json_extract_scalar(to_json_string(afdi), "$.storage_device_drive_type")) | filter sdn = 1 | limit 10
Option B - Filter using a string
dataset = xdr_data | fields action_file_device_info as afdi | alter sdn = json_extract_scalar(to_json_string(afdi), "$.storage_device_drive_type") | filter sdn = "1" | limit 10
Using Syntactic Sugar Format
The same example above with a syntactic sugar format.
dataset = xdr_data | fields action_file_device_info as afdi | alter sdn = to_integer(to_json_string(afdi)->storage_device_drive_type) | filter sdn = 1 | limit 10

Recommended For You