Help with rex and multi-value fields, assigning key value pairs?

[Phil_S]

Hi All,

I have a search which parses key/value pairs out of a strangely-formatted XML field.  

rex field=xml "<N>(?<field_name>(.*?))</N><V>(?<field_value>(.*?))<" | eval {field_name}=field_value 

Above, when there is a single match, this works as expected.  I have the field name and the field value available as a field in my results.   What I don't know how to do, is make this work for multiple matches.   When I run:

rex field=xml max_match=0 "<N>(?<field_name>(.*?))</N><V>(?<field_value>(.*?))<" | eval {field_name}=field_value 

Then both field_name and field_value are multi-value fields.  I would like to make each key=value available in the results as I did above.    Can anyone give me a pointer on how to accomplish this?   

Thanks.

[bowesmana]

The {xx}=yy syntax will not work well with MV fields, so without using zip/mvexpand, the way to do this is with foreach

| makeresults 
| eval xml="<top><N>N1</N><V>V1</V><N>N2</N><V>V2</V></top>" 
| rex field=xml max_match=0 "<N>(?<field_name>(.*?))</N><V>(?<field_value>(.*?))<"
``` Data setup above ```

``` foreach with a fixed set of numbers will cause a 'loop' ```
| foreach 0 1 2 3 4 5 [ eval n=mvindex(field_name, <<FIELD>>), v=mvindex(field_value, <<FIELD>>), {n}=v ]
| fields - n v field_name field_value

The 0 1 2 3 4... will define the maximum number of possible values, so if you have an unbounded number, this is not a good solution, but if you know your maximum, then this will work

[Phil_S]

Thanks!  This is a huge step forward.   Unfortunately, the number of items is variable.   Is there a way I can rebuild the loop using mvcount() or somethink like that?  

foreach 0...mvcount(field_name) 

Something like the above?   Appreciate your help so far! 

[bowesmana · Accepted solution]

I've tried this some times in the past to see if it's possible to create macros to do this, for example you can build the loop string easily enough, e.g.

| eval n=mvcount(field_name)
| eval f=mvjoin(mvrange(0, n, 1), " ")

which creates the string 0 1 2 etc

but it seems to be impossible to get this string to be used in foreach, either directly or in the macro, as macros are expanded before the search.

However, Splunk 9 has some enhancements to foreach that specifically handle MV fields, but I've not got a v9 instance.

It doesn't matter if the number of values is variable, in that as long as you have enough 1 2 3 4 etc to handle the max you will ever see, that's fine.

Here's another approach to it using mvzip/mvexpand/stats, which will be potentially expensive and requires a common ID to stats back together again

| makeresults count=2
| streamstats c as ID
| eval xml="<top><N>N1</N><V>V".(random() % 100)."</V><N>N2</N><V>V".(random() % 100)."</V></top>" 
| rex field=xml max_match=0 "<N>(?<field_name>(.*?))</N><V>(?<field_value>(.*?))<"
| eval c=mvzip(field_name, field_value, "####")
| mvexpand c
| rex field=c "(?<field_name>(.*))####(?<field_value>(.*))"
| eval {field_name}=field_value
| stats values(N*) as N* by ID

 @ITWhisperer is normally good at these quirky questions and will probably have a simple one liner to do it 😀

[Phil_S]

---

It doesn't matter if the number of values is variable, in that as long as you have enough 1 2 3 4 etc to handle the max you will ever see, that's fine.

---

Perfect, that works for me.  Thanks!!