Replace a column with diagonal value in a table

[khavildar]

host time timediff
a 12:00 END
a 11:55 1
a 11:50 1

I want to replace the "END" in timediff with the below value:
timedifference of the first 2 rows - i.e 12:00 - 11:55
and add the timediff of the second row which is 1 . Essentially , its 5 + 1 = 6 which should be the new value of END

I have used delta command to get the 6 value but am struggling to replace the END with the value.

[khavildar]

Thanks for that Renjith!
I used your streamstats logic with delta and got it working. Below is how I got the query:

'base search' | table _time,host,timediff | sort host | delta _time as td | eval diff=abs(td) | eval end=timediff+diff | table _time host timediff end | eval n=if(isint(end),end, "0") | reverse | streamstats current=f window=1 last(n) as prev_diff |reverse | eval newrpo=if(timediff="END",prev_diff,timediff) | table _time,VMName,newrpo

[renjith_nair · Accepted solution]

@khavildar,

Try this,

    "your search to return above data"
    |reverse|streamstats current=f window=1 last(time) as prev_time,last(timediff) as prev_diff|reverse
    |eval timediff=if(timediff=="END",round((strptime(time,"%H:%M")-strptime(prev_time,"%H:%M"))/60,0)+prev_diff,timediff)
    |fields - prev_diff,prev_time

Full search with dummy data

|makeresults|eval host="a",time="12:00,11:55,11:50",timediff="END,1,1"
|makemv delim="," time|makemv delim="," timediff|eval z=mvzip(time,timediff)|table host,z
|mvexpand z|eval s=split(z,",")|eval time=mvindex(s,0),timediff=mvindex(s,1)|table host,time,timediff
|eval _COMMENT="ALL ABOVE IS JUST TO CREATE DUMMY DATA AND NOTHING TO DO WITH ACTUAL SEARCH :-)"
|reverse|streamstats current=f window=1 last(time) as prev_time,last(timediff) as prev_diff|reverse
|eval timediff=if(timediff=="END",round((strptime(time,"%H:%M")-strptime(prev_time,"%H:%M"))/60,0)+prev_diff,timediff)|fields - prev_diff,prev_time

[KailA]

Hi,

Can you show us you search, I'm sure I can help you with your problem 🙂

Kail

[khavildar]

Hi!
thanks much but the below answer worked for me! I have pasted the base query and how i worked around it.

[horsefez]

KailA is an expert on time differences. :yayfox: