Splunk Search

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

_dave_b
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
1 Solution

woodcock
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

woodcock
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

lmonahan
Path Finder

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

Tags (1)
0 Karma

mreynov_splunk
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?

_dave_b
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

_dave_b
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

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

0 Karma

_dave_b
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 latest_event_timestamp = _time | search magnitude > 3.3 AND _timestamp < latest_event_stamp

0 Karma

woodcock
Esteemed Legend

My answer covers all this.

0 Karma

_dave_b
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
Get Updates on the Splunk Community!

Thanks for the Memories! Splunk University, .conf24, and Community Connections

Thank you to everyone in the Splunk Community who joined us for .conf24 – starting with Splunk University and ...

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...