https://community.splunk.com/t5/Splunk-Search/Subsearch-question/m-p/47322#M11295 <P>I have a large search:</P> <P><CODE>search index="XXX"</CODE> which has host as field. This includes data for two locations.</P> <P>I need to filter search that for hosts that exist in each location (Z,Y). </P> <P>I setup an inputlookup for host.csv with 2 columns and did the following:</P> <PRE><CODE>|inputlookup host.csv | fields MAC,Location | where Location="Z" | rename MAC as host| </CODE></PRE> <P>I've added that to my larger search like this:</P> <PRE><CODE>search index="XXX" | join host [|inputlookup host.csv | fields MAC,Location | where Location="Z" | rename MAC as host] | dedup host </CODE></PRE> <P>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.</P> <P>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.</P> <P>Any help/advice is appreciated! </P> Fri, 22 Jul 2011 20:05:05 GMT lokispundit 2011-07-22T20:05:05Z https://community.splunk.com/t5/Splunk-Search/Subsearch-question/m-p/47322#M11295 <P>I have a large search:</P> <P><CODE>search index="XXX"</CODE> which has host as field. This includes data for two locations.</P> <P>I need to filter search that for hosts that exist in each location (Z,Y). </P> <P>I setup an inputlookup for host.csv with 2 columns and did the following:</P> <PRE><CODE>|inputlookup host.csv | fields MAC,Location | where Location="Z" | rename MAC as host| </CODE></PRE> <P>I've added that to my larger search like this:</P> <PRE><CODE>search index="XXX" | join host [|inputlookup host.csv | fields MAC,Location | where Location="Z" | rename MAC as host] | dedup host </CODE></PRE> <P>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.</P> <P>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.</P> <P>Any help/advice is appreciated! </P> Fri, 22 Jul 2011 20:05:05 GMT https://community.splunk.com/t5/Splunk-Search/Subsearch-question/m-p/47322#M11295 lokispundit 2011-07-22T20:05:05Z https://community.splunk.com/t5/Splunk-Search/Subsearch-question/m-p/47323#M11296 <P>Can you add a little more information about what you're trying to do? </P> <P>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: </P> <PRE><CODE>index="XXX" | lookup hosts MAC as host OUTPUT Location | where Location="Z" | stats count by host </CODE></PRE> Sat, 23 Jul 2011 01:15:07 GMT https://community.splunk.com/t5/Splunk-Search/Subsearch-question/m-p/47323#M11296 sideview 2011-07-23T01:15:07Z