Hello Everyone.
I am trying to combine a few join’s via the below code. In short, I have a time series graph that I would like to start at value 0 for a given time range instead of the initial dynamic value.
For example; if the time series starts at value 123, then 123 would have to be subtracted from every value displayed to create a graph that starts at 0.
InfluxDB: v1.8.10
Chronograf: v1.8.10
Apologies for the image, I am only allowed to post one embed. Please open the image in a new window.
Why do I want this?
In Grafana you can only alert on Time Series panels. Take a look at the purple and green Stat panels shown in the below image. These panels have a delta transformation applied and the data starts at 0 for the given time range.
For example the time range selected here is 3 hours. Over this 3 hour period 1.09 GB of data was used in total per the Stat panel. I could set a alert for 100 GB for the start of the time period, however alerts can only be used on Time Series.
Now if we look at the Time Series, it showing 2.74 TB at the start! Way past the 100 GB limit. Although the total usage increases by the correct amount of the time period, the panel needs to start at 0 if you wanted to set a proper alert.
Code
Flux query language code.
rx = from(bucket: "unifidreammachinepro/autogen")
|> range(start: dashboardTime)
|> filter(fn: (r) => r._measurement == "usg_wan_ports" and r._field == "rx_bytes" )
tx = from(bucket: "unifidreammachinepro/autogen")
|> range(start: dashboardTime)
|> filter(fn: (r) => r._measurement == "usg_wan_ports" and r._field == "tx_bytes" )
rxtx = join(tables: {rx: rx, tx: tx}, on: ["_time"])
|> map(fn: (r) => ({
_time: r._time,
_value: r._value_rx + r._value_tx
})
)
firstrxtx = join(tables: {rx: rx, tx: tx}, on: ["_time"])
|> map(fn: (r) => ({
_time: r._time,
_value: r._value_rx + r._value_tx
})
)
|> first()
|> fill(usePrevious: true)
join(tables: {rxtx: rxtx, firstrxtx: firstrxtx}, on: ["_time"])
|> map(fn: (r) => ({
_time: r._time,
_value: r._value_rxtx - r._value_firstrxtx
})
)