topic Re: How to search the time difference between a certain event and the event prior? in Splunk Search

How to search the time difference between a certain event and the event prior?

_dave_b — Mon, 16 Nov 2015 19:42:16 GMT

I am trying to find the time of a type of event, and the time difference of the same type of event that happened just prior. There are other events interspersed in the logs, but I am not interested in them. So, for example, I want to find the times of all the earthquakes in California with magnitude larger than 2. I can find the time of the latest and earliest earthquake without a problem, but it gets less clear if I want to get more specific than that, like getting the latest and the one before the latest.

I just wish I could run a search, build an array from the results, then search through that array, but it seems as though I can't do that in Splunk.

Does anyone have any ideas on how to accomplish this?

Re: How to search the time difference between a certain event and the event prior?

mreynov_splunk — Mon, 16 Nov 2015 21:19:40 GMT

If magnitude is a field, which I assume it is, just include in your search magnitude > 2.
As for the event "just prior" - is there any extrapolation, in terms of time of event, that you can make?

Re: How to search the time difference between a certain event and the event prior?

_dave_b — Mon, 16 Nov 2015 21:32:05 GMT

Hi, yes I include in my search magnitude > 2. But I need the event times, which can be pretty random.

The only extrapolation I can make for the "just prior" is that the previous earthquake happened prior to the most recent earthquake

I thought I could do something like taking the last 2 events with head, then using tail on those events to triangulate the results to give me the second most recent event, but those commands don't seem to have that capability.

Re: How to search the time difference between a certain event and the event prior?

_dave_b — Mon, 16 Nov 2015 21:40:04 GMT

Oh yeah, If I could somehow maintain a running "memory" of what the previous event's timestamp is across each event, that would make things better. But I haven't seen a way to do that yet. Streamstats maybe?

Re: How to search the time difference between a certain event and the event prior?

mreynov_splunk — Tue, 29 Sep 2020 07:55:21 GMT

If this is something you need to do for an array of events, then yea, you should probably try streamstats.

If just one, then you can try something like this: since you know the timestamp of the latest event, search for _timestamp < latest_event_timestamp and get the latest() out of the result set.

https://answers.splunk.com/answers/149904/find-earliest-and-latest-event-per-day-for-a-time-range.html
http://docs.splunk.com/Documentation/Splunk/6.1/SearchReference/Commonstatsfunctions

Re: How to search the time difference between a certain event and the event prior?

woodcock — Mon, 16 Nov 2015 22:35:11 GMT

Like this:

... | reverse | streamstats current=f last(Magnitude) as prevMagnitude ...

Now that each event contains the magnitude of the previous event you no longer need any correlation between events so you can tack whatever you like onto the end:

... | search magnitude > 3.3

Re: How to search the time difference between a certain event and the event prior?

_dave_b — Tue, 29 Sep 2020 07:53:02 GMT

I want to do something like what you said, but how do I pass the value for the latest event timestamp over?

Would it be similar to this search?
search magnitude > 3.3 | eval latest_event_timestamp = _time | search magnitude > 3.3 AND _timestamp < latest_event_stamp

Re: How to search the time difference between a certain event and the event prior?

woodcock — Tue, 17 Nov 2015 18:05:11 GMT

My answer covers all this.

Re: How to search the time difference between a certain event and the event prior?

_dave_b — Tue, 17 Nov 2015 20:04:40 GMT

It does!

My next challenge is to get Splunk to differentiate the time stamps for each event at the location it happens, i.e, the time at each location where the earthquake happened. I suppose that will be the subject of a subsearch.

Anyways, thanks!

Re: How to search the time difference between a certain event and the event prior?

lmonahan — Wed, 11 Oct 2023 16:40:51 GMT

@woodcock wrote:
Like this:
... | reverse | streamstats current=f last(Magnitude) as prevMagnitude ...
Now that each event contains the magnitude of the previous event you no longer need any correlation between events so you can tack whatever you like onto the end:
... | search magnitude > 3.3

Incredible answer! So concise and powerful!