How to compare two different tables in two different indexes and show unique values?

[directtv999]

index A has table1 and Index B has table2

table1 table2.        table3

aaa.      zzz.             aaa

bbb.     aaa.           bbb

ccc.   ccc             

ddd     ddd         

I want do output new table with values doesn't exist when compare with table1 with table2 

[gcusello]

Hi @directtv999,

let me understand:

you want to list all the values in table 1 not present in table 2 and all values of table 2 not present in table 1, is it correct?

If this is your need, please try this:

[gcusello]

Hi @directtv999,

sorry, I clicked the wrong button!

index=indexA OR index=indexB
| eval table3=coalesce(table1, table2)
| stats 
   dc(index) AS index_count 
   values(table1) AS table1
   values(table2) AS table2
   BY table 3
| wjere index_count=1
| table table1 table2 table3

Ciao.

Giuseppe

[directtv999]

Thanks for the reply @gcusello , I didn't get the desired output it is displaying whole fields from table2 in table3

so here is table1 coming from a dynamic nested json and am using below query to extract it before your eval and stats query

foreach hosts.*.table1 [| eval cluster=isnotnull('<<FIELD>>'),'<<FIELD>>,table1)]