Hi,
Let's say we have 2 multivalue fields
Field1={a,b,c,d}
Field2={a,b,c,d,e}
Is it possible to evaluate the difference between these fields and display the additional value of Field2? So that Field3={e}
Best regards,
Heinz
Let me know what you think of the strategy:
| makeresults
| eval F1="a b c"
| makemv F1
| append
[| makeresults
| eval F2="a b c d"
| makemv F2 ]
| append [ | makeresults
| eval F2_Original="a b c d"
| makemv F2_Original ]
| stats values(F1) as F1, values(F2) as F2, values(F2_Original) as F2_Original
| mvexpand F2
| eval F3=if(F2 IN(F1),"",F2)
| fields F1, F2_Original, F3
| where len(F3)>0
The given information works, but I would consider something that is more direct:
Hi @HeinzWaescher
Try this and let me know
| makeresults
| eval Field1="a,b,c,e", Field2="a,b,c,d"
| eval Field3 =mvzip(Field1,Field2)
| makemv delim="," Field3
| stats list(Field1) as Field1,list(Field2) as Field2, count by Field3
| mvcombine Field3
| eval Field3=mvjoin(Field3,",")
| where count=1
| table Field1,Field2,Field3
Let me know what you think of the strategy:
| makeresults
| eval F1="a b c"
| makemv F1
| append
[| makeresults
| eval F2="a b c d"
| makemv F2 ]
| append [ | makeresults
| eval F2_Original="a b c d"
| makemv F2_Original ]
| stats values(F1) as F1, values(F2) as F2, values(F2_Original) as F2_Original
| mvexpand F2
| eval F3=if(F2 IN(F1),"",F2)
| fields F1, F2_Original, F3
| where len(F3)>0
Really nice solution @tiago. 6 years later I could additionally mention that you can now use the foreach command to loop through the mv-fields to avoid the mvexpand.
Here is my example in case you want to compare the mv-fields bi-directional:
| makeresults
| eval F1=split("a,b,c,z",","),F2=split("a,b,c,d",",")
| foreach mode=multivalue F1
[ eval only_in_F1 = mvsort(if(isnull(mvfind(F2,<<ITEM>>)),mvappend(<<ITEM>>,only_in_F1),only_in_F1))
]
| foreach mode=multivalue F2
[ eval only_in_F2 = mvsort(if(isnull(mvfind(F1,<<ITEM>>)),mvappend(<<ITEM>>,only_in_F2),only_in_F2))
]
You can also use the "append and count" approach without the need for doing mvsort only once and not doing mvfind at all.
| makeresults
| eval F1=split("x,y,z,whatever,a,b,c",","),F2=split("b,c,d,whenever,wherever,whatever",",")
| eval combined=mvappend(F1,F2,F2)
| eval combined=mvsort(combined)
| eval count=0
| eval prevcount=-1
| foreach mode=multivalue combined
[ eval state_changed=if(isnotnull(current) and current!=<<ITEM>>,1,0),
left=if(state_changed=1 and count=1,mvappend(left,current),left),
right=if(state_changed=1 and count=2,mvappend(right,current),right),
both=if(state_changed=1 and count=3,mvappend(both,current),both),
current=<<ITEM>>,
count=if(state_changed=1,1,count+1) ]
| eval left=if(count=1,mvappend(left,current),left),right=if(count=2,mvappend(right,current),right),both=if(count=3,mvappend(both,current),both)
| fields - combined count state_changed current prevcount
(the state_changed field is only to make the syntax easier to read)
EDIT: 1. The caveat to this method is that the multivalue fields must have values which occur only once. If you have multiple occurrences of the same value in your multivalued fields this approach will fail since it relies on counting identical values.
2. The "prevcount" field is a leftover from some earlier attempt. It can be safely dropped from the search.
oh wow, that took me some time to understand it entirely. I like it!
You can use the same logic for comparing results from two searches. Just append (with any method, not necessarily using the "append" command of course) two sets of data, one of them twice, and count the values (or sets of values).
If you only want the values themselves, you can just do | stats count by, if you need to preserve some additional fields, you might need to go with some streamstats-based setup doing similar thing as this foreach above does.
The obvious caveat (to both the multivalue solution from the post above and to the general results one) is that the results must be unique (I might have added a mvdedup() for clarity). Otherwise it destroys your counting.
That's a beauty, glad to know foreach has been brought to another level! Thanks🫡
Works pretty fine! Thanks