How to check if each value in multivalue field exists in another multivalue field?

[moaf13]

I have two multi value fields with delim "," (comma)

field1                            field2
\value\random\end,                \value3\random3\end3,
\value2\random2\end2,             \value\random\end, 
\value3\random3\end3,             \value2\random2\end2,
\value\that\does\not\exist        \diff\value\anything

I can make them a multivalue using makemv delim="," field1 and the same thing for field2. I want each value from field 1 and check if it exists in field 2, and if it does, then add it to a new field called "field 3"

i wanted to use foreach but i'm not really familiar with it.

[maciep · Accepted solution]

As usual, there is probably a better way. And if any of your events are exactly the same, I don't think this will work. My sample events are all the same, so i added a streamstats count up front so they would all have at least one different field, which is kind of necessary for the last step to actually work the right way.

[your base search]
| eval tmpField1=field1 
| eval tmpField2=field2
| mvexpand tmpField1
| mvexpand tmpField2
| eval field3 = case(tmpField1=tmpField2,tmpField1) 
| fields - tmp* 
| mvcombine field3

this will essentially break out the multi-valued fields each on their own line. So if you have one event with 2 mv fields each with 4 items, then this will create 16 events after expanding those fields.

At that point you can check to see if field1=field2, and if so create field3 with the value from field1. Once that is done across all 16 events, then you can use mvjoin on field3 to collapse all of those events back into a single one.

Hope it helps or at least points you in the right direction.

[moaf13]

Thank you!