Splunk Search

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

Kukkadapu
Path Finder

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.

1 Solution

somesoni2
SplunkTrust
SplunkTrust

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$")  

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Contributor

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$") 
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
Path Finder

Thanks for your time 🙂

0 Karma

sundareshr
Legend

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$
0 Karma

Kukkadapu
Path Finder

Thanks for your time guys:)

0 Karma

bruceclarke
Contributor

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"

0 Karma

javiergn
SplunkTrust
SplunkTrust

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.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...