https://community.splunk.com/t5/Splunk-Search/How-to-optimize-the-given-query-without-using-join/m-p/468638#M191932 <P>Status is coming from index having values like completed, retain, progress l</P> Fri, 07 Feb 2020 12:59:24 GMT avni26 2020-02-07T12:59:24Z https://community.splunk.com/t5/Splunk-Search/How-to-optimize-the-given-query-without-using-join/m-p/468634#M191928 <P>Hi,</P> <P>I need to Optimize my query to improve the dashboard performance without using any type of join function.</P> <P>Below is my query<BR /> | inputlookup sample.csv<BR /><BR /> | search user IN ( <EM>) application_name IN (</EM>) "application id" IN (*) <BR /> |eval None="None"<BR /> | table "application id",application_name,user,"Status",Type,"Service Host",Platform,Jan,Feb,Mar,Apr,None,env<BR /> | rename "application_name" as Server_Name<BR /> | eval Server_Name=upper(Server_Name)<BR /> | join type=left Server_Name <BR /> [ search index=idx sourcetype=xyz <BR /> | eval Server_Name=upper(Server_Name) <BR /> | search Status!="Completed"<BR /> | table Server_Name Status] <BR /> | search Status!="Completed" | stats sum("Jan") as jan sum("Feb") as feb sum("Mar") as mar sum("Apr") as apr by env<BR /> | eval total = jan+feb + mar + apr <BR /> |table env total</P> <P>Please help me to optimize this query without using join</P> Wed, 30 Sep 2020 04:08:07 GMT https://community.splunk.com/t5/Splunk-Search/How-to-optimize-the-given-query-without-using-join/m-p/468634#M191928 avni26 2020-09-30T04:08:07Z https://community.splunk.com/t5/Splunk-Search/How-to-optimize-the-given-query-without-using-join/m-p/468635#M191929 <P>UPDATED:</P> <PRE><CODE>index=idx sourcetype=xyz Status="retain" OR Status="progress" Server_Name=* | eval Server_Name=upper(Server_Name) | table Server_Name Status | dedup Server_Name | lookup sample.csv "application_name" as Server_Name OUTPUTNEW | eval None="None" | where isnotnull(user) | search user IN ( ) application_name IN () "application id" IN (*) | stats sum("Jan") as jan sum("Feb") as feb sum("Mar") as mar sum("Apr") as apr by env | eval total = jan+feb + mar + apr | table env total </CODE></PRE> <P>Hi, <a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/187627">@avni26</a> <BR /> some code is disappear.<EM>Status</EM> of <EM>Server_Name</EM> is latest status.</P> Wed, 30 Sep 2020 04:08:12 GMT https://community.splunk.com/t5/Splunk-Search/How-to-optimize-the-given-query-without-using-join/m-p/468635#M191929 to4kawa 2020-09-30T04:08:12Z https://community.splunk.com/t5/Splunk-Search/How-to-optimize-the-given-query-without-using-join/m-p/468636#M191930 <P>@to4kawa Thank you for your response.<BR /> I tried to execute like above, but why lookup fields giving multiple values in same row. </P> <P>thanks,</P> Fri, 07 Feb 2020 11:33:36 GMT https://community.splunk.com/t5/Splunk-Search/How-to-optimize-the-given-query-without-using-join/m-p/468636#M191930 avni26 2020-02-07T11:33:36Z https://community.splunk.com/t5/Splunk-Search/How-to-optimize-the-given-query-without-using-join/m-p/468637#M191931 <PRE><CODE>| lookup sample.csv "application_name" as Server_Name OUTPUTNEW </CODE></PRE> <P>Maybe, there is same Server_name.<BR /><BR /> <CODE>| dedup Server_name</CODE><BR /> but, <EM>Status</EM> is unclear.</P> Wed, 30 Sep 2020 04:08:27 GMT https://community.splunk.com/t5/Splunk-Search/How-to-optimize-the-given-query-without-using-join/m-p/468637#M191931 to4kawa 2020-09-30T04:08:27Z https://community.splunk.com/t5/Splunk-Search/How-to-optimize-the-given-query-without-using-join/m-p/468638#M191932 <P>Status is coming from index having values like completed, retain, progress l</P> Fri, 07 Feb 2020 12:59:24 GMT https://community.splunk.com/t5/Splunk-Search/How-to-optimize-the-given-query-without-using-join/m-p/468638#M191932 avni26 2020-02-07T12:59:24Z