topic compare values from 2 string fields and identify matches in Splunk Search

compare values from 2 string fields and identify matches

mrbeck02 — Thu, 08 Jun 2017 17:28:47 GMT

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

Re: compare values from 2 string fields and identify matches

DalJeanis — Thu, 08 Jun 2017 18:22:19 GMT

 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

Re: compare values from 2 string fields and identify matches

mrbeck02 — Thu, 08 Jun 2017 20:32:40 GMT

Thank you,

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

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

Re: compare values from 2 string fields and identify matches

DalJeanis — Fri, 09 Jun 2017 14:22:32 GMT

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.