Learn more about the Cortex Query Language json_extract_scalar_array()
function.
Important
Before using this JSON function, it's important that you understand how Cortex XDR treats a JSON in the Cortex Query Language. This function doesn't have a syntatic sugar format. For more information, see JSON functions.
Syntax
json_extract_scalar_array(<json_array_string>, <json_path>)
When a field in the <json_path>
contains characters, such as a dot (.) or colon (:), use the syntax:
json_extract_scalar_array(<json_array_string>, "['<json_field>']")
Description
The json_extract_scalar_array()
function accepts a string representing a JSON array, and returns an XQL-native array. This function is equivalent to the json_extract_array except that the final output isn't displayed in double quotes ("..."). 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.
Example
Extract the first IPV4 address found in the first element of the agent_interface_map
array. The values of the IPv4 addresses in the array will not contain any double quotes.
dataset = xdr_data | fields agent_interface_map as aim | alter ipv4 = json_extract_scalar_array(to_json_string(arrayindex(aim, 0)) , "$.ipv4") | filter aim != null | limit 10
Final output with 1 row from the results table. Notice that the IPV4 column doesn't contain any double quotes (" "
) around the IP address 172.16.15.42
:
_TIME | AIM | _PRODUCT | _VENDOR | INSERT_TIMESTAMP | IPV4 |
---|---|---|---|---|---|
Aug 9th 2023 10:04:39 |
| XDR agent | PANW | Aug 17th 2023 19:25:48 | 172.16.15.42 |
In contrast, compare the above results to the same query using the json_extract_array()
function. The final output with the same row from the results table has in the IPV4 column the IP address in double quotes "172.16.15.42"
.
_TIME | AIM | _PRODUCT | _VENDOR | INSERT_TIMESTAMP | IPV4 |
---|---|---|---|---|---|
Aug 9th 2023 10:04:39 |
| XDR agent | PANW | Aug 17th 2023 19:25:48 | "172.16.15.42" |