topic Re: rex and multi-value fields, assigning key value pairs in Splunk Search

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

Phil_S — Thu, 15 Sep 2022 14:52:17 GMT

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.

Re: rex and multi-value fields, assigning key value pairs

bowesmana — Thu, 15 Sep 2022 06:39:29 GMT

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

Re: rex and multi-value fields, assigning key value pairs

Phil_S — Thu, 15 Sep 2022 06:56:26 GMT

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!

Re: rex and multi-value fields, assigning key value pairs

bowesmana — Thu, 15 Sep 2022 07:46:34 GMT

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 😀

Re: rex and multi-value fields, assigning key value pairs

Phil_S — Thu, 15 Sep 2022 09:12:12 GMT

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!!