Relative Time based on last event

[JoshuaJohn]

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

Here is my query
|inputlookup report.csv
| rename "location.u_number" as Store_Number
| rename u_wifi_mac_address as Mac_Address
| search u_last_policyrequest_time=*
| search "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")
| where Last_Policy_Request < relative_time(max(Last_Policy_Request),"-48h")
| stats count by Store_Number
| stats count
| appendpipe [ stats count | where count==0]

Ideas?

[woodcock]

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.

[DalJeanis]

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

[woodcock]

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

[DalJeanis · Accepted solution]

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)