Can you help me compare two search results and generate mismatch events with all the fields?

[kmmanikandan]

i have two search results like search1 produce table with 15 columns and search2 produce table with the exactly same column(name). But, i'm not sure about the row values. So i want compare both of these search results and generate mismatched output with all the columns like below...
search1 output like

c1, c2, c3,.......c15  ---->column
r10, r20, r30,.......r150     ------>row1
r11, r21, r31,.......r151     ------>row2
r12, r22, r33,.......r152     ------>row3

search2 output like

c1, c2, c3,.......c15  ---->column
r10, r20, r30,.......r150     ------>row1
r11, r55, r31,.......r151     ------>row2
r12, r22, r20,.......r152     ------>row3

expected output like

r11, r21, r31,.......r151     ------>row2
r12, r22, r33,.......r152     ------>row3

I tried the below query but that didn't help me.

|set diff[search eventtype="e1" | fields f1,f2, ...][search eventtype="e2" | fields f1,f2, ...]

Any help would be much appreciated!

Thanks in Advance,
M

[DalJeanis]

From your answer to @Sukisen1981's question, it does not sound like there is an actual key in these files. From your description of your desired output, it looks like you might not need it anyway.

This will get you any records which are unique in each file. use the entire list of fields in each place. (c1, c2, c3...)

| multisearch 
    [search eventtype="e1" | fields eventtype c1,c2, ...]
    [search eventtype="e2" | fields eventtype c1,c2, ...]
 | stats values(eventtype) as eventtype by c1 c2 c3...
 | where mvcount(eventtype) = 1

What to do next is going to depend on how many differences there are between the records and whether any of the fields on the records is unique enough to serve as a pseudokey.

If you only want the new records, then just add one line

| where eventtype="e1"

If you want to compare the records, then you might try

| sort 0 c1 c2 c3 ... 

...and, with the exception of any records which differ by the value of c1, the records that are different should appear next to each other.

[kmmanikandan]

Thanks DalJeanis for quick response,

i'm completely new to splunk query i didn't understand the your spl query . could you please explain in detail.
actually i have two saved searches which will give table format result with 15 column like table1 and table2 then do the comparison with each other. also here there are two columns which are unique in both results.

| multisearch
[savedsearch seavedsearch_query1 | fields eventtype c1,c2, ...]
[savedsearch seavedsearch_query2 | fields eventtype c1,c2, ...]
| stats values(eventtype) as eventtype by c1 c2 c3...
| where mvcount(eventtype) = 1

can u pls explain how to use saved searches here instead of eventtype.

Thanks in Advance
M

[kmmanikandan]

Hi DalJeanis,

can u pls help me the above query?
awaiting for you response.

Regards,
M

[kmmanikandan]

Hi DalJeanis,

can u pls help me the above query?
awaiting for your response.

Regards,
M

[Sukisen1981]

2 questions - is column 1 value always consistent,meaning there will always be an exact match between the column values for each rows in both the searches? So, r11 for instance will always be in the second row in both searches?
and
any differences in values in other columns (keeping in mind the first column value match exactly) will lead to that respective row from search 1 as your expected output?

[kmmanikandan]

Thanks Sukisen for quick response.
1st question answer is column 1 value almost consistent let say 90% and there are 10 % chances of some of the rows in search 1 missing in search 2 and vice versa. so need to capture missed rows as well with respect to search 1.
2nd question answer : Yes and also expecting vice versa (any differences in values in other columns (keeping in mind the first column value match exactly) will lead to that respective row from search 1 as your expected output?)

Thanks in Advance.
M