topic Re: Relative Time based on last event in Splunk Search

Relative Time based on last event

JoshuaJohn — Thu, 15 Jun 2017 19:23:47 GMT

I want to be able to compare 48 hours from my last event date, thought this would work but I keep getting 0 as my result whenever I run it... Should be closer to 1500 results

Ideas?

Re: Relative Time based on last event

DalJeanis — Fri, 16 Jun 2017 03:48:46 GMT

Here's your problem, I believe...

| where Last_Policy_Request < relative_time(max(Last_Policy_Request),"-48h") |

max() is an aggregate function, so it needs to be attached to a verb like stats or eventstats, or a presentation function like chart or timechart. Also, since your amount of relative time is fixed, it's probably slightly more efficient to just tell splunk the number of seconds to subtract.

| eventstats max(Last_Policy_Request) as maxDate
| where (Last_Policy_Request < maxDate - 172800)

Re: Relative Time based on last event

woodcock — Mon, 26 Jun 2017 00:13:54 GMT

Try this:

|inputlookup report.csv
| rename "location.u_number" AS Store_Number wifi_mac_address AS Mac_Address
| search u_last_policyrequest_time=* "install_status" = "In Use"
| fields u_last_policyrequest_time Store_Number
| eval Last_Policy_Request = strptime(u_last_policyrequest_time, "%Y-%m-%d %H:%M") 
| evenstats max(Last_Policy_Request) AS max_Last_Policy_Request
| eval compare_time = relative_time(max_Last_Policy_Request, "-48h")
| where Last_Policy_Request < compare_time

The rest of the stuff doesn't really make sense to me but I think that above clears up what is supposed to happen up to here.

Re: Relative Time based on last event

DalJeanis — Mon, 26 Jun 2017 01:12:11 GMT

@woodcock - need to reverse the last condition. Where each event's Last Policy Request >= compare_time.

Re: Relative Time based on last event

woodcock — Mon, 26 Jun 2017 01:39:48 GMT

Maybe, I'm not sure. Let's let OP decide; what do you say @JoshuaJohn?