How to edit my search to calculate duration?

[raby1996]

Hello,

I'm running the following search for a runtime report:

   my search  
     | rex field=source ".*?(?<Machine_Model>9\d\d)"
     | where Machine_Model=961 
     | rex field=source ".*?9\d\d(?<Machine_Serial>\S{7})"
     | rex "(?:\n|.)\s+(?<BU1>\d+\.\d+\.\d+\.\d+)" max_match=0 
     | eval BU2=max(BU1)
     | rex "(?:\n|.).*?Package:\s+(?<Package>\S+)" 
     | dedup BU2, Machine_Serial
     | stats dc(BU2) as count values(BU2) as BU2 values(_time) as time by Machine_Serial

Which return results like these (for simplicity I've change the field values in the table) -

  Machine_Serial      BU2      time
  _______________________________________
  1234                1.2      30      
                      1.1      20 
                      1.0      10
  ---------------------------------------
  1235                1.0      10
  ---------------------------------------
  1236                1.3      30
                      1.2      10

And I would like to calculate the difference between each time - in other words, the amount of time each machine remained on a code level with the latest being the difference between the date the search is ran (lets say today =50), so it would look something like this:

  Machine Serial      BU2      time      time_diff
  ___________________________________________________
  1234                1.2      30        20
                      1.1      20        10
                      1.0      15        5
   --------------------------------------------------
  1235                1.0      10        40
   --------------------------------------------------
  1236                1.3      30        20
                      1.2      10        20

Is there any way to achieve this? Thank you in advance.

[sundareshr · Accepted solution]

Try this

.... | eval z=mvzip(BU2, time) | mvexpand z | streamstats window=2 current=f range(time) as time_diff by machine_serial | eval time_diff=if(isnull(time_diff), 50-time, time_diff) | stats values(BU2) as BU2 values(time) as time values(time_diff) as time_diff by machine_serial

[raby1996]

Thank you!