Splunk Search

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) 
0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...