Separate Multivalued fields in XQL

[WilliamValente]

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.

[zarnous]

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

[WilliamValente]

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

[zarnous]

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