What is the most efficient way of filtering on two timestamps?

[andrewtrobec]

Hello all,

I keep facing a common theme and I wanted some input. We all know that the first filter should be on the time range, which filters on each event's _time field. If we would like to filter on a second timestamp, indexed as a String, through a second dashboard input then what are the most efficient ways of doing so?

What I've found is that dealing with a second timestamp requires painful logic that can deal with both presets and custom inputs coming from the dashboard's time picker. Example, assuming i'm filtering on a field called TS_Start_Date, the code that works is:

| where (if("$tok_start_date.earliest$"!="0" AND "$tok_start_date.earliest$"!="",strptime(TS_Start_Date,"%d/%m/%Y %H:%M")>=if(replace("$tok_start_date.earliest$","\d","")!="",relative_time(now(),if("$tok_start_date.earliest$"="now","-0m","$tok_start_date.earliest$")),"$tok_start_date.earliest$"),0=0) AND if("$tok_start_date.latest$"!="0" AND "$tok_start_date.latest$"!="",strptime(TS_Start_Date,"%d/%m/%Y %H:%M")<if(replace("$tok_start_date.latest$","\d","")!="",relative_time(now(),if("$tok_start_date.latest$"="now","-0m","$tok_start_date.latest$")),"$tok_start_date.latest$"),0=0))

If I were to only filter on that field and not on the event _time field, then to do so I would first need to extract all data, which is a very inefficient way of doing things. Are there any more efficient ways of approaching this problem?

Any inputs would be appreciated because I've seen this problem a lot and don't know how best to address it...

Best regards,

Andrew

[yuanliu]

Very common need indeed. But it still depends on your use case. In the past, I had the need to really search on a second time marker that already existed in the event. In that case, I'd create a field extraction, then use that extraction in the first search. There are other use cases where a saved field extraction also works in the first search. Complex combined search can be more complex as the search command does not use any of eval functions.