topic Re: How to search the delta between the Unix Time of each sequential web log grouped by ID? in Splunk Search

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

farismitri — Thu, 26 May 2016 23:26:44 GMT

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!

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

sundareshr — Fri, 27 May 2016 00:37:07 GMT

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") | ...

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

woodcock — Fri, 27 May 2016 00:40:00 GMT

Like this:

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

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

ryanoconnor — Fri, 27 May 2016 00:41:53 GMT

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!

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

farismitri — Fri, 27 May 2016 23:21:03 GMT

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!

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

woodcock — Fri, 27 May 2016 23:30:52 GMT

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

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

farismitri — Wed, 01 Jun 2016 02:23:39 GMT

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

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

ppablo — Sat, 04 Jun 2016 20:20:22 GMT

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

Cheers!

Patrick