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?
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)
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.
@woodcock - need to reverse the last condition. Where each event's Last Policy Request >= compare_time.
Maybe, I'm not sure. Let's let OP decide; what do you say @JoshuaJohn?
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)