https://community.splunk.com/t5/Splunk-Search/Optimizing-subquery-with-inputlookup/m-p/648564#M224372 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/224785">@krbalaji77</a>,</P><P>the error is to use the join command that must be used only when there isn't any other solution.</P><P>Splunk isn't a database!</P><P>In addition you used the search as subquery and you have the limit of 50,000 results.</P><P>You should use a different approach using your lookup:</P><LI-CODE lang="markup">index=os sourcetype=ps "abc.pid" OR "abc.bin" | eval host=lower(host) | stats count BY host | append [ | inputlookup lookup.csv | eval host=lower(host), count=0 | fields host count ] | join type=outer [search ] | stats sum(count) AS total BY host | where total=0</LI-CODE><P>This is a more performant search.</P><P>Ciao.</P><P>Giuseppe</P> Wed, 28 Jun 2023 15:35:48 GMT gcusello 2023-06-28T15:35:48Z https://community.splunk.com/t5/Splunk-Search/Optimizing-subquery-with-inputlookup/m-p/648563#M224371 <P>I have this query to find hosts from a lookup that have zero events. There are about a 100 hosts and I can see that the query performance is slow with the use of subquery this way.&nbsp;</P><P>Any ideas to improve this?</P><P>| inputlookup lookup.csv<BR />| join type=outer [search index=os sourcetype=ps "abc.pid" OR "abc.bin"<BR />| stats count as heartbeat by host ]<BR />| fillnull heartbeat value=0<BR />| where heartbeat=0<BR />| stats values(host) as failed_hosts</P> Wed, 28 Jun 2023 15:28:00 GMT https://community.splunk.com/t5/Splunk-Search/Optimizing-subquery-with-inputlookup/m-p/648563#M224371 krbalaji77 2023-06-28T15:28:00Z https://community.splunk.com/t5/Splunk-Search/Optimizing-subquery-with-inputlookup/m-p/648564#M224372 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/224785">@krbalaji77</a>,</P><P>the error is to use the join command that must be used only when there isn't any other solution.</P><P>Splunk isn't a database!</P><P>In addition you used the search as subquery and you have the limit of 50,000 results.</P><P>You should use a different approach using your lookup:</P><LI-CODE lang="markup">index=os sourcetype=ps "abc.pid" OR "abc.bin" | eval host=lower(host) | stats count BY host | append [ | inputlookup lookup.csv | eval host=lower(host), count=0 | fields host count ] | join type=outer [search ] | stats sum(count) AS total BY host | where total=0</LI-CODE><P>This is a more performant search.</P><P>Ciao.</P><P>Giuseppe</P> Wed, 28 Jun 2023 15:35:48 GMT https://community.splunk.com/t5/Splunk-Search/Optimizing-subquery-with-inputlookup/m-p/648564#M224372 gcusello 2023-06-28T15:35:48Z https://community.splunk.com/t5/Splunk-Search/Optimizing-subquery-with-inputlookup/m-p/648571#M224373 <P>Thank you for the quick response&nbsp;<SPAN><a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/161352">@gcusello</a>&nbsp;</SPAN></P><P><SPAN>so, we do still need to do a join in this case right meaning the append itself wouldnt do it?</SPAN></P><P>also - what does this do ?</P><PRE>| join type=outer [search ]</PRE><P>&nbsp;</P> Wed, 28 Jun 2023 16:38:18 GMT https://community.splunk.com/t5/Splunk-Search/Optimizing-subquery-with-inputlookup/m-p/648571#M224373 krbalaji77 2023-06-28T16:38:18Z https://community.splunk.com/t5/Splunk-Search/Optimizing-subquery-with-inputlookup/m-p/648622#M224384 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/224785">@krbalaji77</a>,</P><P>sorry! it was a typo:</P><LI-CODE lang="markup">index=os sourcetype=ps "abc.pid" OR "abc.bin" | eval host=lower(host) | stats count BY host | append [ | inputlookup lookup.csv | eval host=lower(host), count=0 | fields host count ] | stats sum(count) AS total BY host | where total=0</LI-CODE><P>Ciao.</P><P>Giuseppe</P> Thu, 29 Jun 2023 07:35:09 GMT https://community.splunk.com/t5/Splunk-Search/Optimizing-subquery-with-inputlookup/m-p/648622#M224384 gcusello 2023-06-29T07:35:09Z