Splunk Search

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

greggz
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

somesoni2
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

somesoni2
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).

greggz
Communicator

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

0 Karma

greggz
Communicator

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

0 Karma

somesoni2
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

greggz
Communicator

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

0 Karma

greggz
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

somesoni2
SplunkTrust
SplunkTrust

Does my full query works for you ?

0 Karma

greggz
Communicator

Yep your sample works fine.

0 Karma

somesoni2
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

greggz
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

somesoni2
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

greggz
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

somesoni2
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

greggz
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

somesoni2
SplunkTrust
SplunkTrust

I just added the working query to main answer.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

greggz
Communicator

Hello! Something like this

              Server1    Server2 

Property      false        true
Property      false       false
Property      true        true
0 Karma

somesoni2
SplunkTrust
SplunkTrust

And the field names are dynamic or static?

0 Karma

greggz
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...