How to compare multiple fields in 2 indexes and return the differences

[leonheart78]

I'm currently trying to compare 3 fields (ID, Start_time, Log_time) from 2 different indexes, and to get the differences when any of the 3 attributes are unmatched.

How can I go about doing this?

Thank you.

[acharlieh · Accepted solution]

This seems to be a broad question without data, so I'm making the assumption that ID, Start_time and Log_time appear in the same event, in each index, and that ID is a unique value that will appear in each index only once or not at all. With these conditions I would start with a search like:

| multisearch
    [ search index=A ID=* Start_time=* Log_time=*
      | fields ID Start_time Log_time ] 
    [ search index=B ID=* Start_time=* Log_time=*
      | fields ID Start_time Log_time
      | rename Start_time as B_Start_time Log_time as B_Log_time ]
| stats first(*) as * by ID
| where NOT ( Start_time = B_Start_time AND Log_time = B_Log_time )

Using multisearch we pull back the events from index A and index B separately, but we rename the Start_time and Log_time fields that come back from index B
We use a stats command to join the row from A with the corresponding row from B by ID. Using where we keep only those rows where the Start_time or Log_time from index A does not match that from index B. (If ID did not match, one of these sets of fields would be missing, and thus should also qualify but as I don't have data and am not trying this out you may need to also OR in some isnull functions in the where command as well).

EDIT: I updated the query above to include isnull checks after I sat down with a splunk instance, and figured out that you do actually need to add in OR isnull(field) for each field into the where clause. I then edited it again after @martin_mueller pointed out that NOT ( a=b ) was different than a!=b. Check out this example search below, where we generate 5 cases (both null, a null, b null, both not null and equal, both not null but different) and compare the results when we include the null checks and when we don't and when we use NOT (equal) checks.

| noop | stats count | eval count=mvrange(0,5) | mvexpand count
| eval a = case(count > 1, count)
| eval b = case( count % 2 = 1, count, count=4,5)
| multireport
    [eval method="base data set" ]
    [eval method="with null checks" | where isnull(a) OR isnull(b) OR a != b ]
    [eval method="without null checks" | where a != b]
    [eval method="with NOT equal checks" | where NOT (a = b) ]