Knowledge Management

how to add a duration column for each record, using its timestamp subtract the timestamp of the previous record,

wangkevin1029
Communicator

Hi, Splunkers,

I have a splunk search table as below, I want to add a duration column for each record, using its timestamp subtract the timestamp of the previous record,  

for example, here for record 2, duration should be 2021-12-14 12:55:25.258  - 2021-12-14 12:55:03.339

_time                                               columna

2021-12-14 12:55:03.339   abc
2021-12-14 12:55:25.258   efg
2021-12-14 12:55:25.336   hij

any help would be appreciated.

Kevin

Labels (1)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

There's a bit easier solution relying on streamstats alone

<base search>| streamstats current=t window=2 range(_time) as duration

 

View solution in original post

wangkevin1029
Communicator

thx for your quick response.

my table has its own sort,  so, not sure extra sort 0 _time will mess up original order?

I will give a quick try.

 

Kevin

 

 

0 Karma

wangkevin1029
Communicator

btw, since I use sort _time asc,  the time on top is earlier than time at the bottom

0 Karma

johnhuang
Motivator

Here's a possible solution:

 

| sort 0 -_time
| streamstats last(_time) AS end_epoch current=f
| eval end_time=strftime(end_epoch, "%Y-%m-%d %H:%M:%S")
| eval start_time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| eval time_range=start_time." - ".end_time
| eval duration_sec=end_epoch-_time
| sort 0 _time

 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

There's a bit easier solution relying on streamstats alone

<base search>| streamstats current=t window=2 range(_time) as duration

 

wangkevin1029
Communicator

Thank you, PickleRick,  it works perfectly, and more efficient, 

really take some time to digest these amazing, also complicated parameters, syntax.... 

 

To johnhua,  your code also works beautifully, also learned new commands from your code.

 

Kevin

0 Karma

johnhuang
Motivator

Yep but he asked for this format "2021-12-14 12:55:25.258  - 2021-12-14 12:55:03.339"

0 Karma

PickleRick
SplunkTrust
SplunkTrust

You're right. I missed that. Just read "duration" 😉

0 Karma

wangkevin1029
Communicator

works perfectly,  thx you very much. you are a Splunk master

let me digest your code.... amazing.

Kevin

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...