topic Re: foreach with mvexpand to iterate over server fields and perform an expansion in Splunk Search

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

greggz — Tue, 19 Dec 2017 14:47:55 GMT

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

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

somesoni2 — Tue, 19 Dec 2017 15:13:57 GMT

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

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

greggz — Tue, 19 Dec 2017 15:15:41 GMT

Hello! Something like this

              Server1    Server2 

Property      false        true
Property      false       false
Property      true        true

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

somesoni2 — Tue, 19 Dec 2017 15:24:01 GMT

And the field names are dynamic or static?

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

greggz — Tue, 19 Dec 2017 15:29:24 GMT

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

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

somesoni2 — Tue, 19 Dec 2017 15:32:00 GMT

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

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

greggz — Tue, 19 Dec 2017 15:55:08 GMT

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

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

greggz — Tue, 19 Dec 2017 15:58:40 GMT

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

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

somesoni2 — Tue, 19 Dec 2017 16:21:38 GMT

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

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

greggz — Tue, 19 Dec 2017 16:32:53 GMT

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

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

greggz — Tue, 19 Dec 2017 16:36:01 GMT

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 )

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

somesoni2 — Tue, 19 Dec 2017 16:49:32 GMT

Does my full query works for you ?

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

greggz — Tue, 19 Dec 2017 16:56:35 GMT

Yep your sample works fine.

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

somesoni2 — Tue, 19 Dec 2017 17:04:58 GMT

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.

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

greggz — Tue, 19 Dec 2017 17:20:13 GMT

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 ?

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

somesoni2 — Tue, 19 Dec 2017 17:37:53 GMT

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

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

greggz — Tue, 19 Dec 2017 17:44:59 GMT

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

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

https://ibb.co/kgBXfR

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

somesoni2 — Tue, 19 Dec 2017 18:47:21 GMT

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

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

greggz — Tue, 19 Dec 2017 19:33:14 GMT

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

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

somesoni2 — Tue, 19 Dec 2017 20:54:10 GMT

I just added the working query to main answer.