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

[mchuli934]

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!

[bowesmana]

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?

[mchuli934]

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!

[bowesmana]

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.