Splunk Search
Highlighted

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?

New Member

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??

0 Karma
Highlighted

Re: 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?

SplunkTrust
SplunkTrust

@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"
0 Karma
Highlighted

Re: 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?

New Member

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)

0 Karma
Highlighted

Re: 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?

SplunkTrust
SplunkTrust

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
0 Karma
Highlighted

Re: 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?

New Member

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

0 Karma
Highlighted

Re: 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?

SplunkTrust
SplunkTrust

@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)
0 Karma
Highlighted

Re: 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?

New Member

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

0 Karma
Highlighted

Re: 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?

SplunkTrust
SplunkTrust

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

0 Karma