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!

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
Retired

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") | ...
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...