Subsearch question

[lokispundit]

I have a large search:

search index="XXX" which has host as field. This includes data for two locations.

I need to filter search that for hosts that exist in each location (Z,Y).

I setup an inputlookup for host.csv with 2 columns and did the following:

|inputlookup host.csv | fields MAC,Location | where Location="Z" | rename MAC as host|

I've added that to my larger search like this:

search index="XXX" | join host [|inputlookup host.csv | fields MAC,Location | where Location="Z" | rename MAC as host] | dedup host

However the results are little inconsistent. Based on the CSV I see 584 hosts for Z and 250 hosts for Y. However my search only finds some of these -- i.e 420 for Z and 196 for Y.

I "think" this is a result of all hosts not appearing in the index, but I'm also a little unsure of the join I used.

Any help/advice is appreciated!

[sideview]

Can you add a little more information about what you're trying to do?

Reading your description I'm not sure you need to be using join at all. It seems almost like what you're trying to do is as simple as:

index="XXX" | lookup hosts MAC as host OUTPUT Location | where Location="Z" | stats count by host