How do you compare two results?

[btawiah]

I have a table(main table).csv with field location.

I have raw logs that includes field location

main table.csv
location_field
A
B
C

Raw logs 
location_field
A 
B

Please, I need help with a query that will check raw logs against main table and give a result of C missing

search result should return C

[btawiah · Accepted solution]

I got it done. Thanks
| inputlookup main_table.csv
| fields location
| eval count=0
| append
[ search index=someindex*
| fields location
| stats count BY location ]
| stats sum(count) AS missing by location
| where missing= 0

Whenever results return 0 that means those locations are not in main table

[Vijeta]

@btawiah-can you extract the filed that has location from your raw logs? If not then you will have to provide a log sample.
Once you extract the field say loc , the query can be something like this-

 |inputlookup maintable.csv| join type=outer location[search index=* | rename loc as location| fields source]| where ISNULL(source)

[btawiah]

I got it done. Thanks
| inputlookup main_table.csv
| fields location
| eval count=0
| append
[ search index=someindex*
| fields location
| stats count BY location ]
| stats sum(count) by location
| where count= 0

Whenever results return 0 that means those locations are not in main table

[btawiah]

@Vijeta i actually updated the question. I dont have to extract fields because that already exist. I only need to get the difference and output the one from raw logs since that is not in the main table location field.

[Vijeta]

@btawiah Try running this query, write the name of your index instead of "yourindexname"

  |inputlookup maintable.csv| join type=outer location_field[search index="yourindexname"| fields source]| where ISNULL(source)