How can I calculate the timediff based on non-sequential sequence IDs?

[khavildar]

I have a requirement wherein I have to find timedifference of 2 events. Below is an example on the event type:

Host    Time          SeqID      Transaction
a          1:00:00      5               Start
b          1:30:00      7               Start
a          1:45:00      9               Complete
b          2:00:00      14             Complete
a          4:00:00      19             Start
c           4:30:00      23             Start
a          4:45:00       25            Complete

I need to calculate the timedifferences between 'Start' and 'Complete' for every Host using their SeqID.
To translate roughly, its like
concat(hostname,Time@Complete,SeqId@Complete) - concat(hostname,Time@Start,SeqID@Start)
But in the above calculation, i need to ensure the SeqID@Complete is the most numerically nearest one to the SeqID@Start.

Any thoughts / suggestions?

[renjith_nair · Accepted solution]

Hi @khavildar,

Try this

index="your index"  "your other search terms"|table _time,host,SeqID ,Transaction|sort host,SeqID |streamstats current=f last(_time) as prev by host|eval time_diff=_time-prev|where Transaction="Complete"|table host,SeqID ,time_diff

[khavildar]

Works like a charm! Perfect.
Thanks so much!