Splunk Search

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

farismitri
Explorer

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!

0 Karma
1 Solution

ryanoconnor
Builder

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!

View solution in original post

farismitri
Explorer

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!

0 Karma

ryanoconnor
Builder

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!

View solution in original post

woodcock
Esteemed Legend

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
Explorer

@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
Community Manager
Community Manager

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

Cheers!

Patrick

0 Karma

woodcock
Esteemed Legend

Like this:

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

sundareshr
Legend

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") | ...
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!