Using relative time with where command

[orion44]

I'm able to find all the previous day's events by hard coding in date ranges as such:

where mytime > "2018-03-01" AND mytime < "2018-03-02"

How do I use the where command to search for the relative time of yesterday?

Here's the snippet from my search:

| eval mytime=strftime(_time,"%Y-%m-%dT%H:%M:%S%z") 
| stats count(SRC) as "Source IP" by SRC mytime 
| dedup SRC sortby mytime 
| rename SRC as "Source IP" 
| where mytime > "2018-03-01" AND mytime < "2018-03-02"

[elliotproebstel · Accepted solution]

Instead of converting times to human-readable formats before comparing them, the most straightforward way to write your search to always look for values from "yesterday" (regardless of what today is) would be to use the relative_time function, as you alluded to. Integrating this directly into your current search structure would look like this:

| stats count(SRC) as "Source IP" by SRC _time 
| dedup SRC sortby _time 
| rename SRC as "Source IP" 
| where _time>=relative_time(now(), "-1d@d") AND _time<=relative_time(now(), "@d")

This will allow Splunk to do all comparisons using epoch time strings and still display the time value in human-readable format, something Splunk will do by default with only the _time field.

[kamlesh_vaghela]

Hi @orion44,

Have you try to compare it with epochtime?
Can you please try this? I have keep same logic just change in time format.

| eval mytime=_time
 | stats count(SRC) as "Source IP" by SRC mytime 
 | dedup SRC sortby mytime 
 | rename SRC as "Source IP" | eval T1=strptime("2018-03-01","%Y-%m-%d"),T2=strptime("2018-03-02","%Y-%m-%d")
 | where mytime > T1 AND mytime < T2

Thanks