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

View solution in original post

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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!