https://community.splunk.com/t5/Splunk-Search/How-would-I-be-able-to-improve-the-following-joined-query/m-p/579399#M201899 <P>The first step in the query is to count all events and list the unique values of field 'd' for all usernames in old_index.&nbsp; The second step is to count all events for each username in new_index.&nbsp; Finally, the two sets of results are merged based on common username values.&nbsp; If there is no match, the count from new_index is retained.</P><P>Here's one alternative query that should be more efficient.</P><LI-CODE lang="markup">index=old_index | stats count as old_count values(d) as d by username | append [search index=new_index | stats count as new_count by username ] | stats values(*) as * by username</LI-CODE> Tue, 28 Dec 2021 15:32:11 GMT richgalloway 2021-12-28T15:32:11Z https://community.splunk.com/t5/Splunk-Search/How-would-I-be-able-to-improve-the-following-joined-query/m-p/579389#M201894 <P><SPAN>Learning about joins and sub searches. What's the following query executing and would there be a way to make it more efficient?<BR /></SPAN><SPAN><BR />index=old_indexstats count values(d) as d by username | join type=inner username [search index=new_index | stats count by username ]</SPAN></P><P>I believe it starts by searching and counting usernames in the new index however, am getting mixed up after that.</P> Tue, 28 Dec 2021 14:44:02 GMT https://community.splunk.com/t5/Splunk-Search/How-would-I-be-able-to-improve-the-following-joined-query/m-p/579389#M201894 brc55 2021-12-28T14:44:02Z https://community.splunk.com/t5/Splunk-Search/How-would-I-be-able-to-improve-the-following-joined-query/m-p/579399#M201899 <P>The first step in the query is to count all events and list the unique values of field 'd' for all usernames in old_index.&nbsp; The second step is to count all events for each username in new_index.&nbsp; Finally, the two sets of results are merged based on common username values.&nbsp; If there is no match, the count from new_index is retained.</P><P>Here's one alternative query that should be more efficient.</P><LI-CODE lang="markup">index=old_index | stats count as old_count values(d) as d by username | append [search index=new_index | stats count as new_count by username ] | stats values(*) as * by username</LI-CODE> Tue, 28 Dec 2021 15:32:11 GMT https://community.splunk.com/t5/Splunk-Search/How-would-I-be-able-to-improve-the-following-joined-query/m-p/579399#M201899 richgalloway 2021-12-28T15:32:11Z