How to aggregate data on duration

kapacitor

#1

Hi All,
I have a measurement in InfluxDB that keeps track of the status of a system. For example, consider the following measures:

03/22/18 00:00:00AM STATUS_A
03/22/18 09:00:00AM STATUS_B
03/22/18 13:00:00AM STATUS_C
03/22/18 18:00:00AM STATUS_B
03/22/18 19:00:00AM STATUS_D
03/22/18 21:00:00AM STATUS_A

What I need to do now is to derive how long the system was in each state every day. In the above example, the desired result is something like

STATUS_A 12h (from 00:00 to 09:00 and from 21:00 to 24:00)
STATUS_B 5h (from 09:00 to 13:00 and from 18:00 to 19:00)
STATUS_C 5h (from 13:00 to 18:00)
STATUS_D 2h (from 09:00 to 21:00)

I’m very new to the TICK stack, so I could be missing something quite elementary. I was thinking to use Kapacitor to create the aggregate result, but I don’t really know how to obtain the result.

I tried using integral(), but it did now work as expected.