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

[greggz]

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

[somesoni2 · Accepted solution]

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]

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

[greggz]

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

[somesoni2]

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"," ") 

[greggz]

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

[greggz]

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 )

[somesoni2]

Does my full query works for you ?

[greggz]

Yep your sample works fine.

[somesoni2]

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.

[greggz]

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 ?

[somesoni2]

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

[greggz]

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

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

https://ibb.co/kgBXfR

[somesoni2]

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

[greggz]

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

[somesoni2]

I just added the working query to main answer.

[somesoni2]

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

[greggz]

Hello! Something like this

              Server1    Server2 

Property      false        true
Property      false       false
Property      true        true

[somesoni2]

And the field names are dynamic or static?

[greggz]

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