compare values from 2 string fields and identify matches

[mrbeck02]

We are attempting to compare the string values from 2 different fields, and report on the values which are found in both fields.

Example:
Date,Field1,Field2,Foo,Bar
4.3.17,123456,ffgghh,sfuff,stuff
4.4.17,000000,123456,stuff,stuff

Report: value 123456 is found in field1 and field2

I have been able to build a table to show the values of the fields and visually find the matches, I need to be able to do this over large periods of time, and then build alerts when there is a value in Field1 which appears in Field2.

Thanks

[DalJeanis · Accepted solution]

 your search that returns | table Date,Field1,Field2,Foo,Bar
| eval fan=mvappend("A","B")
| mvexpand fan
| eval matchfield=if(fan="A",Field1,Field2)
| stats dc(fan) as matchcount, values(*) as * by matchfield
| where matchcount> 1
| table matchfield

[mrbeck02]

Thank you,

I made a few modifications, but this is working. Much appreciated.

...
1. table Field1,Field2,matchfield,stuff,stuff
2. sort - matchcount

[DalJeanis]

Yep, I thought about deleting line 7 or adding wording at the end, but your wording "Report: value 123456 is found in field1 and field2" didn't seem to require feeding out the foos and bars.

There is a more complicated coding strategy, if you need to know which values came from which record, where after the fan you concatenate everything into a single field, with a different name for A or B, table just the matchfield and those two fields, then stats them, then use a rex to put them back into a bunch of separate fields again.

Didn't figure you needed that one, but now that you've seen the basic method, you can get there if you want.