json_extract_scalar_array - Reference Guide - Cortex XDR - Cortex - Security Operations

Cortex XDR XQL Language Reference

Product
Cortex XDR
Creation date
2024-02-26
Last date published
2024-05-21
Category
Reference Guide
Abstract

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.

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

[{"ipv4":["172.16.15.42"], "ipv6": [], "mac": "00:50:56:9f:30:a9"}]

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

[{"ipv4":["172.16.15.42"], "ipv6": [], "mac": "00:50:56:9f:30:a9"}]

XDR agent

PANW

Aug 17th 2023 19:25:48

"172.16.15.42"