How to use delta or another search command to find the difference between the most recent event and the event prior?

[_dave_b]

Hello. I'm trying to find the time delta between the most recent event and the event prior to it. Delta sounds like it would do the job, but is giving me 'null' for the most recent event.

i.e.,

source="all_month.csv" host="keyw-h5pzdv1" sourcetype="csv" place=*California* | delta _time AS timeDeltaS p=1 | eval timeDeltaS=abs(timeDeltaS) | eval timeDelta=tostring(timeDeltaS, "duration")  | table timeDelta, _time

returns this:

                2015-11-10 10:08:13.220
00:34:58.310    2015-11-10 09:33:14.910

but I want it to return this:

00:34:58.310    2015-11-10 10:08:13.220
00:48:43.870    2015-11-10 09:33:14.910

So that it shows that the time between the latest event (at 10:08) and the one before it (at 9:33) is 34:58, and it lines up with the current event.

Is there a way I can use this with delta or some other command?

Thanks for your help

[woodcock · Accepted solution]

Like this:

source="all_month.csv" host="keyw-h5pzdv1" sourcetype="csv" place=*California* | reverse | streamstats current=f last(_time) AS prevTime | eval timeDelta = tostring(_time - prevTime, "duration") | table timeDelta, _time | reverse

[jeffland]

You'll have to specifically tell splunk to use the current time for the delta of your most recent event, something like

... | delta _time AS timeDeltaS p=1 | eval timeDeltaS=coalesce(abs(timeDeltaS),now()-_time) | eval timeDelta=tostring(timeDeltaS, "duration") | table timeDelta, _time