Learn more about the Cortex Query Language json_extract_scalar()
function.
Important
Before using this JSON function, it's important that you understand how Cortex XDR treats a JSON in the Cortex Query Language. For more information, see JSON functions.
Syntax
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.
Important
JSON field names are case sensitive, so the key to field pairing must be identical in an XQL query for results to be found. For example, if a field value is "TIMESTAMP"
and your query is defined to look for "timestamp", no results will be found.
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