Splunk Search

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

Phil_S
Engager

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.

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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 😀

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma

Phil_S
Engager

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! 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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 😀

0 Karma

Phil_S
Engager

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

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...