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 |
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
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
Sorry for not being clear, id and static code makes a unique row. so there could be duplicate IDs as with different status code.
What about order for example
Index A
101 | A01 |
101 | A02 |
Index B
101 | A02 |
101 | A01 |
Is this 2 mismatches or zero?
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.