topic Re: Separate Multivalued fields in XQL in Cortex XDR Discussions

Separate Multivalued fields in XQL

WilliamValente — Mon, 30 Oct 2023 14:31:58 GMT

Hi Guys,

I need some help.

I have two fields with multiple values and I need to separate there values into separates lines.

My fields are like these:

My goal is to have 1 line with each of the results:

field = value

I tried using the arrayexpand command, but it ends up duplicating all rows of all values.

Re: Separate Multivalued fields in XQL

zarnous — Mon, 30 Oct 2023 15:46:48 GMT

Hi @WilliamValente

We may need the full XQL to understand the cascading stages more, but you can try the below options and let me know if any works for you:

1- If the values are duplicate in the field, you can limit the output of the arrayexpand stage by using the limit switch as to one output for example , something like this

| arrayexpand Object_Values limit 1

2- The second option here (but will show the values separated in one line)is to use the “arraystring” function, so something like this (where we use comma for example to separate the values):

| alter hostname = arraystring (Object_Values, ", ")

I hope that helps and shed some light!

Please feel free to mark this as a solution if it helps resolve the request

Thanks
Z

Re: Separate Multivalued fields in XQL

WilliamValente — Mon, 30 Oct 2023 16:21:20 GMT

Hi Zarnous,

Thanks for your help.

I tried your query, but It is not the result that I want.

The command arrayexpand with limit 1, return only the first value.

The command arraystring just concatenate the values with ",". I need each line with field = value.

I tried the command arryexpand without limit, they duplicate the value, like this:

My XQL is:

dataset = trend
| alter alert_name = json_extract(_alert_data, "$.alert_name"),
priority_score = json_extract(_alert_data, "$.trendmicrovisiononexdrpriorityscore"),
alert_description = json_extract(_alert_data, "$.alert_description"),
investigation_status = json_extract(_alert_data, "$.trendmicrovisiononexdrinvestigationstatus"),
impact_scope = regextract(impactScope, "\"entityValue\\":([^,]+)"),
impact_scope_name = regextract(impactScope, "\"name\\":([^,]+)"),
ips = regextract(impactScope, "\"ips\\":([^,]+)"),
mitretechniqueid = regextract(matchedrules, "\"mitreTechniqueIds\\":([^,]+)"),
object_field = regextract(indicators, "\"field\\":([^,]+)"),
object_value = regextract(indicators, "\"value\\":([^,]+)")
| arrayexpand object_field
| arrayexpand object_value
| fields _time, severity, alert_name, priority_score, investigation_status, mitretechniqueid, impact_scope_name, description, ips, object_field, object_value, id, workbenchLink

Re: Separate Multivalued fields in XQL

zarnous — Mon, 30 Oct 2023 17:29:32 GMT

Thank you for sharing the query, unfortunately i cant replicate it in my tenant since you are using a custom dataset that i dont have, but looking at the query logic, and specially the fields

object_field = regextract(indicators, "\"field\\":([^,]+)"),
object_value = regextract(indicators, "\"value\\":([^,]+)")

I see they are extracted in JSON Like format and i also see that it is possible to have the same object field as associated with one or more object value, i can see that as an expected query result , hence seeing multiple values matching same field, otherwise if you try to dedup the filed you will loose lots of the values associated with it.

Please correct me if i misunderstood the use case here!

Thanks
Z