Archive

Relative Time based on last event

JoshuaJohn
Contributor

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?

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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) 

View solution in original post

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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) 

View solution in original post

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!