Flux query to calculate start, stop, and duration of an alarm

Hi everyone! I need some help with Flux, I have done a ton of research but still can’t figure out how to do this.

I have my data coming in with a time stamp, alarm name, like this:

.

I need to calculate the start time stamp, end time stamp, and duration of each alarm. What’s hard is the only way to tell the ‘start’ of an alarm is for the previous row to be a different alarm name from the current row. And, calculating the ‘end time stamp’ means adding the duration to the ‘start time stamp’. What’s the best way to achieve this please? Some example query language would be most helpful as I’m still learning. Thank you! I tried using map with monitor.stateChangeOnly but was unable to get the stop_time to populate using this:

|> map(fn: (r) => ({ r with stop_time: time(v: int(v: r._time) + r.duration) }))

Perferred output would be:

start_time, stop_time, alarm msg, duration in minutes

Hello @Marion_Akagi,
You could do something like this to find the duration:

import "array"

data = array.from(rows: [{_time: 2020-01-01T00:00:00Z, _value: "foo"},
  {_time: 2020-02-02T00:00:00Z, _value: "foo"},
  {_time: 2020-03-02T00:00:00Z, _value: "foo"},
  {_time: 2020-04-01T00:00:00Z, _value: "boo"},
  {_time: 2020-05-02T00:00:00Z, _value: "boo"},
  {_time: 2020-06-02T00:00:00Z, _value: "boo"}])
  |> group(columns: ["_value"], mode:"by")  

durData = data 
  |> map(fn: (r) => ({ r with _time: uint(v: r._time)   }))
  |> spread(column: "_time")
  |> map(fn: (r) => ({ r with _time: string(v: duration(v: r._time)) }))  
  |> map(fn: (r) => ({ r with label: "duration" }))
//   |> yield(name: "durData")
  
first = data
 |> first() 
 |> map(fn: (r) => ({ r with _time: string(v: r._time) }))
 |> map(fn: (r) => ({ r with label: "start" }))
//   |> yield(name: "first")

last = data 
|> last() 
|> map(fn: (r) => ({ r with _time: string(v: r._time) }))
|> map(fn: (r) => ({ r with label: "stop" }))
//   |> yield(name: "last")

union(tables: [first, last, durData])
  |> pivot(rowKey:["_value"], columnKey: ["label"], valueColumn: "_time")
  |> yield(name: "final")

OR you probably want to use one of these:

But hopefully that gets you close.
Uncomment the yields() like print statements to understand how flux is operating.

Thank you SO much for pointing me in the right direction! @Anaisdg