- Find Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- i have a join query which i need to optimize using...
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
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??
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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"
What goes around comes around. If it helps, hit it with Karma 🙂
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
What goes around comes around. If it helps, hit it with Karma 🙂
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi renjith, just want to understand, my requirement is to perform last, but you are using latest, why so??
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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)
What goes around comes around. If it helps, hit it with Karma 🙂
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
yes. I figure it out later. it's working perfectly. Thanks a lot.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@m4sucess, if it resolved your question, please accept as answer to close this thread. Thanks!
What goes around comes around. If it helps, hit it with Karma 🙂
Enterprise Security Content Update (ESCU) | New Releases
Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?
Index This | What are the 12 Days of Splunk-mas?
