I have a fleet of cars, I’m monitoring the state (either free, busy) of each car over a period of time (18 hours). Each car goes from free to busy according to booking.
over time, when there is a state transition for a car, I collect, in the same csv file, the time stamp, the car id, the new state
at the end, the this csv file is parsed in one shot and data are sent to influxdb → “car id” is configured as tag and “car state” as field.
/*** below id part of the csv file /
carId;state,timestamp
…
209;1;2022-07-02 06:04:19.539000
177;1;2022-07-02 06:04:29.779000
240;1;2022-07-02 06:04:29.779000
231;1;2022-07-02 06:04:29.779000
…
177;-1;2022-07-02 06:30:29.789000
240;-1;2022-07-02 07:14:29.449000
…
177;1;2022-07-02 12:04:29.779000
/ ***/
Grafana now accesses the influxdb for visualization.
I’ve have been able to use the state time line plugin to extract and visualize the state of each car
What I’m struggling with is :
I would like to trace for each car the final percentage of time it has been in each state (free, bus).
I’m using flux
to me it means to accumulate the time in each state for each car and trace the result
I’ve tried using the monitor state (and still trying), I was able to gather the duration between state switches but I struggle how to accumulate these duration for free and busy separately and display them … should I use separate variables … ?
can anyone help me please, also I’ll let you know my progresses
flux stateDuration() does not do the trick as I’m continuously switching between free (-1) and busy (1) and stateDuration computes the time between two successive free or two successive busy.
from(bucket: “simdataset”)
|> range(start: -7d)
|> filter(fn: (r) => r[“_measurement”] == “$measurement”)
|> filter(fn: (r) => r[“_field”] == “occupied”)
|> elapsed(columnName : “elapsed”)
|> map(fn: (r) => ({ r with free: if r._value == 1 then r.elapsed * 1 else r._value * 0 }))
|> map(fn: (r) => ({ r with busy: if r._value == -1 then r.elapsed * 1 else r._value * 0 }))
|> cumulativeSum(columns : [“free”, “busy”, “elapsed”])
|> map(fn: (r) => ({ r with percentbusy: r.busy * 100 / r.elapsed }))
|> map(fn: (r) => ({ r with percentfree: r.free * 100 / r.elapsed }))
|> drop(columns:[“_value”, “busy”, “elapsed”, “free”, “percentfree”, “_field”, “_start”, “_stop”])
basically, for each car, I compute the elapsed time with the previous state and then use a map function to identify if it was free or busy time, then accumulates data all data together.
I’m ramping up on flux … I’m not sure I’m applying the best way to address the issue, I mean I’m kind of doing some low level coding … what do you think ? would it consumes too many resource the way I do it?
I’m 100% occupancy with car almost never used