How to achieve multivalues by using the for each command?

[akhil4mdev]

So,

    serverlist                                                              splunk_server
        A                                                                              A
         B                                                                              B
         C                                                                              C
          J                                                                              D
           I
           K                                                               

Here both are multivalued.

I need to write a query to get the results as:

    serverlist                         splunk_server                 result

        A                                          A                                  D
        B                                          B
        C                                          C
        J                                           D
        I
        K                                                                                                

I don't want these multi-values to be changed to non-multi values or mvexpanding , thank you!

[DalJeanis]

TL;DR. Okay, this method does the same thing, and will work even if one of the servers to be deleted is a substring of the servers to be kept, and will work unless the data contains the delimiter "!!!!". We've set the delimiter in the field mydelim on a separate line so that it can be changed as needed for any given use case.

| eval mydelim="!!!!"
| eval results1=split(replace(mydelim.mvjoin(splunk_server,mydelim.mydelim).mydelim, "(?:".mydelim.mvjoin(serverlist, mydelim."|".mydelim).mydelim.")", mydelim), mydelim) 
| table * result*

More description follows -

---

This run-anywhere sample of an efficient method, without mvexpand, was provided by @xpac in Slack -

| makeresults
| eval serverlist="foosplindh01,foosplinda02,boxsplinda01,winsplind01,badsplindsplins01,goodsplinda01,wowsplinda01"
| eval splunk_server="foosplindh01,foosplinda02,boxsplinda01,awesomesplind01"
| makemv delim="," serverlist
| makemv delim="," splunk_server
| fields - _time
| rename COMMENT as "The above just enters some sample data in two multivalue fields."

| rename COMMENT as "Now this does what you asked for."
| eval result=split(replace(mvjoin(splunk_server, "$"), "(?:" + mvjoin(serverlist, "|") + ")", ""), "$") | table * result

Giving this output

serverlist         splunk_server       result
foosplindh01       foosplindh01        awesomesplind01
foosplinda02       foosplinda02
boxsplinda01       boxsplinda01
winsplind01        wesomesplind01
badsplindsplins01
goodsplinda01
wowsplinda01

The method turns the first mv field into a flattened field with a delimiter, in this case $, which must never be present in the data. it then turns the second multivalue field into a valid regex, using the pipe symbol | to mean "OR" between each of the second multivalue field entries, and replaces any of those values with "" (nothing). Once the replacement is complete, the resulting field is split back into a multivalue field.

This method will fail if the delimiter is present in the data, or if any of the values that are being replaced are a substring of one of the non-matching values.

Here is the same thing, broken down into four steps that may be easier to read

| eval parm1=mvjoin(splunk_server, "$")
| eval parm2="(?:".mvjoin(serverlist, "|").")"
| eval results1=replace(parm1,parm2,"")
| eval results2=split(results1,"$")

After this,

parm1 has the value foosplindh01$foosplinda02$boxsplinda01$awesomesplind01
parm2 has the value (?:foosplindh01|foosplinda02|boxsplinda01|winsplind01|badsplindsplins01|goodsplinda01|wowsplinda01)
results1 has the value $$$awesomesplind01
results2 has the value awesomesplind01

Now, to fix the problem with this method accidentally blanking out half of a servername if the whole value in the second mv field matched part of a value in the first mv field, we need to double the delimiters. And since $ is a pretty common data value in user names and other things we might need to use this method on, we're going to use the much-less-common value "!!!!".

The steps then look like this...

| mydelim="!!!!'
| eval parm1=mvjoin(splunk_server,mydelim)
| eval parm2="(?:".mydelim.mvjoin(serverlist, mydelim."|".mydelim).mydelim.")"
| eval results1=replace(parm1,parm2,mydelim)
| eval results2=split(results1,mydelim)

...and all together it looks like this...

 | eval mydelim="!!!!"
 | eval results1=split(replace(mydelim.mvjoin(splunk_server,mydelim.mydelim).mydelim, "(?:".mydelim.mvjoin(serverlist, mydelim."|".mydelim).mydelim.")", mydelim), mydelim) 
 | table * result*

[adonio]

why dont you want them change?
you cant expand and stitch back to a multi-value
maybe something like this:

| makeresults count=1
| eval serverlist = "A,B,C,E,F,G,H"
| makemv delim="," serverlist
| mvexpand serverlist
| appendcols [ | makeresults 
| eval splunk_server = "A,B,C,D"
| makemv delim="," splunk_server
| mvexpand splunk_server]
| rename COMMENT as "the above generates data and expands its to multivalue below is the solution" 
| eval object = if(serverlist==splunk_server,null(),splunk_server)
| stats values(serverlist) as serverlist values(splunk_server) as splunk_server values(object) as object