i have a join query which i need to optimize using OR and Stats, Since i am dealing with timestamp and fetching last(field), i am struggling, Can any one help?

[m4sucess]

index="index1 sourcetype="sourcetype1"
| join deviceId [ search index="index2" sourcetype="sourcetype2" productFamily="xyz" ]
| where productFamily="xyz"
| sort _time
| stats last(p2mp) as p2mp by deviceName

index1 has fields like deviceId,p2mp,deviceName
index2 has fields like productFamily,deviceId

I want to remove join, want to rebuild the optimized query which doesn't have job, append,sub search etc. Just OR, stats, eval ..
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??

[renjith_nair]

@m4sucess,
Based on few assumptions, does this work in your case,

(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"

[m4sucess]

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)

[renjith_nair]

what about adding below before stats?Does that bring us closer?

 |eval index1_time=if(index=="index1",_time,null())
 |eventstats latest(p2mp) as  p2mp by deviceId,index1_time

[m4sucess]

Hi renjith, just want to understand, my requirement is to perform last, but you are using latest, why so??

[renjith_nair]

@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

index=_internal earliest=-15m|sort _time|stats last(_time) as last_time,latest(_time) as latest_time)

[m4sucess]

yes. I figure it out later. it's working perfectly. Thanks a lot.

[renjith_nair]

@m4sucess, if it resolved your question, please accept as answer to close this thread. Thanks!