Compare two indexes and report mismatching records

[AshChakor]

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

ID	status_code
101	A01
102	A11
103	B10
104	M01
105	D01
101	A02

Index B

ID	status_code
101	A01
102	B10
103	B10
104	M01
101	Z01

Expected output –

Mismatched records:

ID	Index A code	Index B Code
102	A11	B10
105	D01
101	A02	Z01

[ITWhisperer · Accepted solution]

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]

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

[ITWhisperer]

What about order for example

Index A

101	A01
101	A02

Index B

101	A02
101	A01

Is this 2 mismatches or zero?

[AshChakor]

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.