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

[_dave_b]

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?

[woodcock · Accepted solution]

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

[lmonahan]

---

@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!

[mreynov_splunk]

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?

[_dave_b]

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.

[_dave_b]

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?

[mreynov_splunk]

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.ht...
http://docs.splunk.com/Documentation/Splunk/6.1/SearchReference/Commonstatsfunctions

[_dave_b]

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

[woodcock]

My answer covers all this.

[_dave_b]

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!