How do I join an index with a lookup?

[Shashank_87]

Hi,

I need to join my query with a lookup which contains a field called username. I need to get the users who —

1. exist in both my main query index and the lookup

2. exist in lookup but do not exist in the main query index.

This is what my query looks like when i started writing this -

index="prod" sourcetype=prod_events 
| dedup username 
| eval type="MainIndex" 
| eval username = lower(username) 
| fields username type 
| appendpipe 
    [| inputlookup test.csv
    | eval type="lookup" 
    | eval username = lower(username) 
    | sort username
    | fields type username 
        ] 

Any help is appreciated. Many Thanks

[Vijeta · Accepted solution]

Add the below code to your search-

|stats dc(type) as dc ,values(type) as type by username | where NOT(dc=1 and type="MainIndex")