https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403373#M116724 <P>yes. I figure it out later. it's working perfectly. Thanks a lot.</P> Tue, 02 Oct 2018 17:01:49 GMT m4sucess 2018-10-02T17:01:49Z https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403367#M116718 <P>index="index1 sourcetype="sourcetype1"<BR /> | join deviceId [ search index="index2" sourcetype="sourcetype2" productFamily="xyz" ] <BR /> | where productFamily="xyz"<BR /> | sort _time<BR /> | stats last(p2mp) as p2mp by deviceName</P> <P><STRONG>index1 has fields like deviceId,p2mp,deviceName<BR /> index2 has fields like productFamily,deviceId</STRONG></P> <P>I want to remove join, want to rebuild the optimized query which doesn't have job, append,sub search etc. Just OR, stats, eval ..<BR /> I am trying with maintaining timestamp because I am using last keyword here and after doing OR and stats, all the data mixed up and I am not getting the result which I want. Can anyone help me??</P> Sat, 29 Sep 2018 05:42:41 GMT https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403367#M116718 m4sucess 2018-09-29T05:42:41Z https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403368#M116719 <P>@m4sucess,<BR /> Based on few assumptions, does this work in your case,</P> <PRE><CODE>(index=index1 OR index=index2) (sourcetype="sourcetype1" OR sourcetype="sourcetype2") |stats latest(p2mp) as p2mp,latest(deviceName) as deviceName,latest(productFamily) as productFamily by deviceId |where productFamily="xyz" </CODE></PRE> Sat, 29 Sep 2018 06:18:31 GMT https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403368#M116719 renjith_nair 2018-09-29T06:18:31Z https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403369#M116720 <P>It's not working, i want last value of p2mp based on index1's timestamp. problem is once i got stats, everything is messed up, all the multivalues are combined in every field based on deviceid and then it's giving wrong output for last(field)</P> Sat, 29 Sep 2018 06:20:05 GMT https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403369#M116720 m4sucess 2018-09-29T06:20:05Z https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403370#M116721 <P>what about adding below before stats?Does that bring us closer?</P> <PRE><CODE> |eval index1_time=if(index=="index1",_time,null()) |eventstats latest(p2mp) as p2mp by deviceId,index1_time </CODE></PRE> Sat, 29 Sep 2018 07:25:17 GMT https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403370#M116721 renjith_nair 2018-09-29T07:25:17Z https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403371#M116722 <P>Hi renjith, just want to understand, my requirement is to perform last, but you are using latest, why so??</P> Sat, 29 Sep 2018 07:31:16 GMT https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403371#M116722 m4sucess 2018-09-29T07:31:16Z https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403372#M116723 <P>@m4sucess, you are ordering the time using sort _time which will order the events in and display the oldest event as first event. Then you are using last which brings you the last event which is actually the latest event. Instead of that I am using latest to bring back the latest event. You shall run a sample search to confirm</P> <PRE><CODE>index=_internal earliest=-15m|sort _time|stats last(_time) as last_time,latest(_time) as latest_time) </CODE></PRE> Mon, 01 Oct 2018 16:20:47 GMT https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403372#M116723 renjith_nair 2018-10-01T16:20:47Z https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403373#M116724 <P>yes. I figure it out later. it's working perfectly. Thanks a lot.</P> Tue, 02 Oct 2018 17:01:49 GMT https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403373#M116724 m4sucess 2018-10-02T17:01:49Z https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403374#M116725 <P>@m4sucess, if it resolved your question, please accept as answer to close this thread. Thanks!</P> Wed, 03 Oct 2018 17:37:02 GMT https://community.splunk.com/t5/Splunk-Search/i-have-a-join-query-which-i-need-to-optimize-using-OR-and-Stats/m-p/403374#M116725 renjith_nair 2018-10-03T17:37:02Z