Splunk Search

Compare two indexes and report mismatching records

AshChakor
Path Finder

I have two indexes Index A and Index B and it has a common key “ID” and I want to compare two indexes and need to report which status code are not matching with each other and any missing record.

Index A

IDstatus_code
101A01
102A11
103B10
104M01
105D01
101A02

 

Index B

IDstatus_code
101A01
102B10
103B10
104M01
101Z01

 

Expected output –

Mismatched records:

IDIndex A codeIndex B Code
102A11B10
105D01 
101A02Z01

 

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

It is not completely clear what a matching record is given your example. What happens if the second 101 in index A id also A01 or if the first 101 in index B is A02 and the second one is A01? Or are the IDs unique and it is just a typo in the example?

Assuming second 101 should be 106 (that is, unique ids)

| makeresults count=1
| eval events="A,101,A01|A,102,A11|A,103,B10|A,104,M01|A,105,D01|A,106,A03|B,101,A01|B,102,B10|B,103,B10|B,104,M01|B,106,Z01"
| eval events=split(events,"|")
| mvexpand events
| rex field=events "(?<index>\w),(?<ID>\d+),(?<status_code>.*)"
| fields - events _time
/* set up data */
| eval statusA=if(index="A",status_code,NULL())
| eval statusB=if(index="B",status_code,NULL())
| stats list(statusA) as statusA list(statusB) as statusB by ID
| eval match=if(statusA = statusB, 1, 0)
| where match=0

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

It is not completely clear what a matching record is given your example. What happens if the second 101 in index A id also A01 or if the first 101 in index B is A02 and the second one is A01? Or are the IDs unique and it is just a typo in the example?

Assuming second 101 should be 106 (that is, unique ids)

| makeresults count=1
| eval events="A,101,A01|A,102,A11|A,103,B10|A,104,M01|A,105,D01|A,106,A03|B,101,A01|B,102,B10|B,103,B10|B,104,M01|B,106,Z01"
| eval events=split(events,"|")
| mvexpand events
| rex field=events "(?<index>\w),(?<ID>\d+),(?<status_code>.*)"
| fields - events _time
/* set up data */
| eval statusA=if(index="A",status_code,NULL())
| eval statusB=if(index="B",status_code,NULL())
| stats list(statusA) as statusA list(statusB) as statusB by ID
| eval match=if(statusA = statusB, 1, 0)
| where match=0

AshChakor
Path Finder

Sorry for not being clear, id and static code makes a unique row.  so there could be duplicate IDs as with different status code. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

What about order for example

Index A

101A01
101A02

Index B

101A02
101A01

Is this 2 mismatches or zero?

0 Karma

AshChakor
Path Finder

Since both values(ID and status_code) exist in both indexes it doesn't mismatch, it's 0. If it was a db query I would have joined two tables on ID columns and checked in the where clause if IDs are same but status_code are different to find mismatch. I am looking something like that kind of query in Splunk. when I join two indexes on common ID, I get all the matching records and I want exactly opposite to that. 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...