How to add a 3rd column to my table to display "match" next to a value in column 2 that matches any value in column 1?

[dineshp]

My current search looks like this:

index=myfood | table Sunday, Monday

Which results in:

Sunday      Monday 
Egg         Rice
Apple       Chicken
Banana      Milk
Carrot      Vegetable
Rice        Noodles

Now, my objective is to add another column named "Matches" and identify if any of the food on Sunday matches with the food on Monday.

I did something like this:

| eval matches=if(Sunday==Monday, "match", "no match")

but I get following result:

Sunday      Monday       Matches 
Egg         Rice         no match
Apple       Chicken      no match
Banana      Milk         no match
Carrot      Vegetable    no match
Rice        Apple        no match

which is not right, because Rice and Apple is a match to column Monday.

Any help is really appreciated. Thanks

[ramdaspr · Accepted solution]

When you are doing Sunday==Monday it compares only the values for that one event.
What you need to do is to have a subsearch which returns every record for Monday to compare against each event for Sunday

so hopefully the below would work

| eval Match = if([search index=whatever | table Monday | dedup Monday| rename Monday as Sunday],"matches","doesnt match") | table Sunday,Monday,Match

however, do consider the limitations of subsearches wrt the output limits but it should work for both the fruit example or the IP matching.

[dineshp]

@ramdaspr
Is it possible to make a partial match as ooposed to exact match. The search can be within the same event.

For Example:
0.10.12.10, -, -, -, 3/13/2015, 0:23:55, -, myproxy, -, www.facebook.com, 31.13.77.6, 443, 0, 3948, 2672, SSL-tunnel, -, -, www.facebook.com:443, -, Inet, 0, -, Allow, Req ID: 0eaf3338; Compression: client=Noserver=No compress rate=0% decompress rate=0%, Internal, Internal, 0x80, Allowed, -, -

I have extracted following:

1. www.facebook.com as URL1
2. www.facebook.com:443 as URL2

| eval matches=if(URL1==URL2, "match", "no match") == Gives me no match.

[ramdaspr]

the Like command might be of use.