How to search the delta between the Unix Time of each sequential web log grouped by ID?

[farismitri]

To put it as simply as possible:

Imagine 8 log entries with only two fields per log, t = time & ID = Identifier

Logs:

#1 t=1.0 ID=1
#2 t=2.0 ID=1
#3 t=3.0 ID=1
#4 t=4.0 ID=1
#5 t=1.0 ID=2
#6 t=1.5 ID=2
#7 t=2.0 ID=2
#8 t=2.5 ID=2

I want to extract a field based on the delta between the time stamps, and have it linked to the ID succeeding each call. Lets call this field D (= Delta)

So ideally the associated stats (or whatever applicable Splunk search is right for this) would look like:

t=1.0 ID=1 D=0.0
t=2.0 ID=1 D=1.0
t=3.0 ID=1 D=1.0
t=4.0 ID=1 D=1.0
t=1.0 ID=2 D=0.0
t=1.5 ID=2 D=0.5
t=2.0 ID=2 D=0.5
t=2.5 ID=2 D=0.5

The idea behind this is doing analysis on behavior of scripted vs human interactions in web logs based on delta's between calls from a single client identifier. And each Delta only needs to be correlated back to the next sequential timestamp for each ID.

Any input would be much appreciated!

[farismitri]

Aha, Streamstats!

All these answers are utterly helpful, so I would like to choose them all, however I don't think I am able to do so.

No particular response was better, so I've up-voted all of them.

Thank you all for your assistance!

[ryanoconnor · Accepted solution]

You're going to be looking for the streamstats command http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/streamstats

An example search for windows event logs would be something like the following:

index=wineventlog sourcetype="WinEventLog:Security"  EventCode=4624 | streamstats range(_time) as Duration by user window=2

Where Duration would be the time in seconds between the previous and current event where the user in that event logged in. Good luck!

[woodcock]

One minor nit-pick: this solution shows the duration between an event and the NEXT event, not the previous. To do what you literally said (which I think the OP was desiring), you will have to insert |reverse before the | streamstats. Major ++ for the using both range and window!!!

[farismitri]

@Woodcock, OP here, I ended up using pieces of all responses (Specifically "|reverse") in order to achieve my desired results!

Wanted to take a moment to appreciate how awesome the Splunk Answers community is. First question i've posted after 2 years using Splunk and I'm blown away at the helpfulness!

Thanks again, all!

[ppablo]

Glad you found the help you needed from one of the best communities around 🙂

Cheers!

Patrick

[woodcock]

Like this:

... | reverse | streamstats current=f last(t) AS tPrev BY ID | eval D = t - coalesce(tPrev, 0)

[sundareshr]

See if this gets you going

... | streamstats window=1 current=f global=f latest(_time) as nexttime by id | eval d=tostring(nexttime-_time, "duration") | ...