Splunk Search

Any idea about the query optimization with using join or subsearch?

mchuli934
Loves-to-Learn Lots

Hi, 

I am trying to get all events with two different kinds of objectname(A or B vs C) but with the same username and their access time should be close.  The accessTime of events with Objectname C should be happen just after the events with  Objectname A or B. 

Here is my current query:

index=index1 host=host1 ObjectName=A OR ObjectName=B
|rename accessTime AS accTime1
| eval ptime=strptime(accTime1,"%Y-%m-%d %H:%M:%S")
| join userName
[ search index=index1 ObjectName=C
| rename accessTime AS accTime2
| eval itime=strptime(accTime2,"%Y-%m-%d %H:%M:%S") ]
| eval diff=abs(ptime-itime)/60
|appendpipe [|search diff<2]
| timechart span=1day dc(userName)

is there any way can help me optimize this query since when the search time window become to be 1 months or more, the subsearch limitations will influence the search result. Thanks!

Labels (3)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

The join can be replaced with stats

index=index1 ((host=host1 ObjectName=A OR ObjectName=B) OR ObjectName=C)
| rename accessTime AS accTime1 
| eval ptime=if(ObjectName=C, null(), strptime(accessTime,"%Y-%m-%d %H:%M:%S"))
| eval itime=if(ObjectName=C, strptime(accessTime,"%Y-%m-%d %H:%M:%S"), null())
| stats values(ptime) as ptime values(itime) as itime by userName
| eval diff=abs(ptime-itime)/60 

which will solve the data size issue with join, however, if you are going to have the same user several times in te month, then you will have multiple access times. You would then need to do stats by time also to join the events together, e.g.

...
| bin _time span=1d
| stats values(ptime) as ptime values(itime) as itime by _time userName
| eval diff=abs(ptime-itime)/60 
...

however, you still may get multiple values of each time - how do you want to handle that?

 

0 Karma

mchuli934
Loves-to-Learn Lots

Thanks for the reply. I will try the method you sent here first and I have a question how do you use this method to make sure the two events(one with objectname c and one with object a or b) they happen close.   And I am thinking about not using time difference to locate the most close event(with ObjectName C) and (with ObjectName A or B) . Thanks!

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

If you are looking to detect if they are close, then you can look at the diff value, as you do in your current search.

I would need more details of the use case and data to be able to suggest how to detect 'close' events for those objects.

 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...