Splunk Search

foreach with mvexpand to iterate over server fields and perform an expansion

Communicator

I have various fields like "Server 1" "Server 2" ... And I want to perform an expansion of those fields like so:

                       Server 1 | Server 2

                         false  | true
  Property               false  | false
                         true   | true

Example: So the field Property for the Server1 has multiple values ( false, false, true )

foreach Server* [ mvexpand <<FIELD>> ] 

But this don't work. But single expansion works

mvexpand Server1

This is my idea for iterating every Server field and performing an expansion but I am open to other resolutions aswell! Thanks

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Give this workaround a try (runanywhere sample, replace everything before | eval temp="" with your search)

| gentimes start=-1 | eval Property="Test" | table Property | eval Server1=split("true false true"," ") | eval Server2=split("false false true"," ") 
| eval temp="" | foreach Server* [eval temp=if(temp="", '<<FIELD>>',mvzip(temp,'<<FIELD>>'))] | mvexpand temp | foreach Server* [eval "<<FIELD>>"=mvindex(split(temp,","),<<MATCHSTR>>-1)] | fields - temp

Updated - on that worked

| gentimes start=-1 | eval Property="Test" | table Property | eval UF1=split("true false true"," ") | eval UF2=split("false false true"," ") 
  | eval temp="" | foreach UF* [eval temp=if(temp="", '<<FIELD>>',mvzip(temp,'<<FIELD>>'))] | mvexpand temp | eval sno=0| foreach UF* [eval "<<FIELD>>"=mvindex(split(temp,","),sno) | eval sno=sno+1] | fields - temp

Explanation:

| eval temp="" | foreach UF* [eval temp=if(temp="", '<<FIELD>>',mvzip(temp,'<<FIELD>>'))] 

Creating a new field temp, which will be combined multivalued field for each of UF field values.

| eval sno=0| foreach UF* [eval "<<FIELD>>"=mvindex(split(temp,","),sno) | eval sno=sno+1] 

For each UF* field, I'm overwriting the current value (which can be a multivalued field, to single value from expanded temp values. The field sno is used to find specific values within temp field (temp field is comma separate combined values of UF* fields).

View solution in original post

SplunkTrust
SplunkTrust

Give this workaround a try (runanywhere sample, replace everything before | eval temp="" with your search)

| gentimes start=-1 | eval Property="Test" | table Property | eval Server1=split("true false true"," ") | eval Server2=split("false false true"," ") 
| eval temp="" | foreach Server* [eval temp=if(temp="", '<<FIELD>>',mvzip(temp,'<<FIELD>>'))] | mvexpand temp | foreach Server* [eval "<<FIELD>>"=mvindex(split(temp,","),<<MATCHSTR>>-1)] | fields - temp

Updated - on that worked

| gentimes start=-1 | eval Property="Test" | table Property | eval UF1=split("true false true"," ") | eval UF2=split("false false true"," ") 
  | eval temp="" | foreach UF* [eval temp=if(temp="", '<<FIELD>>',mvzip(temp,'<<FIELD>>'))] | mvexpand temp | eval sno=0| foreach UF* [eval "<<FIELD>>"=mvindex(split(temp,","),sno) | eval sno=sno+1] | fields - temp

Explanation:

| eval temp="" | foreach UF* [eval temp=if(temp="", '<<FIELD>>',mvzip(temp,'<<FIELD>>'))] 

Creating a new field temp, which will be combined multivalued field for each of UF field values.

| eval sno=0| foreach UF* [eval "<<FIELD>>"=mvindex(split(temp,","),sno) | eval sno=sno+1] 

For each UF* field, I'm overwriting the current value (which can be a multivalued field, to single value from expanded temp values. The field sno is used to find specific values within temp field (temp field is comma separate combined values of UF* fields).

View solution in original post

Communicator

Not working for me. It just sets my fields all blank..

0 Karma

Communicator

Does "Split" make the value be an array ? Cause in my sample I think they're represented as an array

0 Karma

SplunkTrust
SplunkTrust

Just run this and see if the format matches your data. Yes, it's assuming that both Server1 and Server2 (or any other servers) are multivalued field (kind of arrays in Splunk).

| gentimes start=-1 | eval Property="Test" | table Property | eval Server1=split("true false true"," ") | eval Server2=split("false false true"," ") 
0 Karma

Communicator

Yes they seem similar. The only difference is that my server names come like "Server-1" "Server-2"

0 Karma

Communicator

Oh and another difference is that my values also come as a single value sometimes. So it's a mix of arrays or a single value ( which I don't need to expand anymore ofc )

0 Karma

SplunkTrust
SplunkTrust

Does my full query works for you ?

0 Karma

Communicator

Yep your sample works fine.

0 Karma

SplunkTrust
SplunkTrust

Well, without seeing your current data, it'd be difficult to troubleshoot what causing it not to work. Can you provide some screenshot of your sample data (showing field Property and Servers). Mask/Blackout any sensitive data/actual server names.

0 Karma

Communicator

Something along these lines: https://ibb.co/hxfiLR

And if I try to foreach UF* [mvexpand <<FIELD>> ] this is the result:

https://ibb.co/cmiES6

My multi value fields are have tripled.. why ?

0 Karma

SplunkTrust
SplunkTrust

Since you're expanding one field at a time, the total number of rows will become N*N (say you've 3 items, first field will yield 3 rows after mvexpand, with second field still multivalued field in all. Second mvexpand will again yield 3 rows for each row).

What do you get when you just add following to your search??Specially in field temp

your search with field Property UF_* fields 
| eval temp="" | foreach Server* [eval temp=if(temp="", '<<FIELD>>',mvzip(temp,'<<FIELD>>'))] | mvexpand temp
0 Karma

Communicator

Oh jeez. So that's what's happening...

This is what happens in temp. The other UF* remain "Un-Expanded"

https://ibb.co/kgBXfR

0 Karma

SplunkTrust
SplunkTrust

Got the issue. I kinda assumed that all servernames are integer numbers. Try this

| gentimes start=-1 | eval Property="Test" | table Property | eval UF1=split("true false true"," ") | eval UF2=split("false false true"," ") 
 | eval temp="" | foreach UF* [eval temp=if(temp="", '<<FIELD>>',mvzip(temp,'<<FIELD>>'))] | mvexpand temp | eval sno=0| foreach UF* [eval "<<FIELD>>"=mvindex(split(temp,","),sno) | eval sno=sno+1] | fields - temp
0 Karma

Communicator

Yep it really works, but I've got no clue what's happening in there haha. Better go read the docs! Do you wish to post this as your answer or should I just accept the other one ? Thanks for your time man! Really apreciated it

0 Karma

SplunkTrust
SplunkTrust

I just added the working query to main answer.

0 Karma

SplunkTrust
SplunkTrust

What should be the final output after expansion? (based on your sample data in question)

0 Karma

Communicator

Hello! Something like this

              Server1    Server2 

Property      false        true
Property      false       false
Property      true        true
0 Karma

SplunkTrust
SplunkTrust

And the field names are dynamic or static?

0 Karma

Communicator

They migth change yes. Hence the "Server*". The Property field name is the same always

0 Karma