How to compare values in col1 to col2, and return all values existing in col1, but also NOT in col 2?

[michael_wong]

Here is my situation. I can use subsearch to get two column data, just like below.

Data row is not aligned, so I can't simply use eval if to compare. Some of the value is identical, but some is not. I want to output the value existing in col1, but not exist in col 2

column1 column 2

AA            BB

CC           AA

DD           FF

EE            ZZ

FF            XX

VV          MM

[michael_wong · Accepted solution]

I've solved it out.

First add a "+" to each column to mark that value is existing in col1 OR col2

And then I can combine the lines using command stats first(*) as * by host.

search 1 | | eval col1="+" | append [ search2 | col2 ="+"]
| stats first(*) as * by HOST
| where isnotnull(col1) AND isnull(col2 )