Splunk Search

compare values from 2 string fields and identify matches

mrbeck02
Explorer

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

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust
 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

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust
 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
Explorer

Thank you,

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

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...