How to search based on a time field that is not _time?

[Kukkadapu]

Hi,

I have two time fields.

1. _time (This is the splunk time stamp)
2. abctime (format YYYY-MM-DD)

How do I search the events for all time (_time), and then search abctime based on the time selected in the dashboard (earliest and latest time). Can you help me with the search?

Thanks.

[somesoni2 · Accepted solution]

This will make the base search to run for all time and then filter events based on the time range picker values.

index=foo sourcetype=bar earliest=0  | where strptime(abctime, "%Y-%m-%d") >=if(replace("$time.earliest$","\d","")!="",relative_time(now(),"$time.earliest$"),"$time.earliest$")  AND  strptime(abctime, "%Y-%m-%d") <if(replace("$time.latest$","\d","")!="",relative_time(now(),"$time.latest$"),"$time.latest$")  

[harshpatel]

Hi, I think this solution needs one enhancement:
In the case if latest time is now it passes "now" which in relative_time() functions gives empty result so it can be fixed by adding if("$time.latest$"="now", "-0","$time.latest$" ) condition as follows:

index=foo sourcetype=bar earliest=0  | where strptime(abctime, "%Y-%m-%d") >=if(replace("$time.earliest$","\d","")!="",relative_time(now(),"$time.earliest$"),"$time.earliest$")  AND  strptime(abctime, "%Y-%m-%d") <if(replace("$time.latest$","\d","")!="",relative_time(now(),if("$time.latest$"="now", "-0","$time.latest$" ),"$time.latest$") 

[somesoni2]

The addinfo will give the same time range as the time range of base search, so it will represent all time. We can't use the time range token directly as it can show relative time value ( for 'Last 15 min' it'll be '-15m')

[Kukkadapu]

Thanks for your time 🙂

[sundareshr]

Assume t is the name of your timerange field on your dashboard. The following search will filter based on selected time values

... | eval abctime=strptime(abctime, "%Y-%m-%d") | search abctime>=$t.earliest$ AND abctime=$t.latest$

[Kukkadapu]

Thanks for your time guys:)

[bruceclarke]

This won't work if the time range picker is set to "yesterday" or "last 7 days" since the earliest and latest will be strings like "@d" or "-7d@d"

[javiergn]

Hi @bruceclarke.

That's fine. I know downvotes are not personal 🙂

I could have downvoted dozens of answers in the past but never did mainly because I haven't noticed that type of behaviour from the "senior" guys. Instead of that people tend to reply saying it won't work, this is wrong or something like that.

I don't know, I guess it's a matter of preference but in this forum I don't see downvotes very often. Or maybe I didn't look hard enough 🙂

I have removed my answer anyway because it wasn't a good one as somesoni and you pointed out.

See this long answer for instance if you are curious about the etiquette and voting preferences.