Splunk Search
Highlighted

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

Communicator

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?

0 Karma
Highlighted

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

Splunk Employee
Splunk Employee

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?

Highlighted

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

Communicator

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.

0 Karma
Highlighted

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

Communicator

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?

0 Karma
Highlighted

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

Splunk Employee
Splunk Employee

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 < latestevent_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

0 Karma
Highlighted

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

Communicator

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 latesteventtimestamp = time | search magnitude > 3.3 AND _timestamp < latestevent_stamp

0 Karma
Highlighted

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

Esteemed Legend

My answer covers all this.

0 Karma
Highlighted

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

Communicator

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!

0 Karma
Highlighted

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

Esteemed Legend

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

View solution in original post