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

[wangkevin1029]

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

[wangkevin1029]

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

[wangkevin1029]

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

[johnhuang]

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

[PickleRick · Accepted solution]

There's a bit easier solution relying on streamstats alone

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

[wangkevin1029]

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

[johnhuang]

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

[PickleRick]

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

[wangkevin1029]

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

let me digest your code.... amazing.

Kevin