Using InfluxDB 1.8.10.
I’m trying to monitor the bandwith of my routers. Since they don’t provide the total Rx Tx I have to calculate them by myself via query.
The goal is to calculate for each interface:
the Rx and Tx from the past 24h to 12h and then the same from 12h to now(). Then subtract the last value to the first one.
And, at the end, I should sum every result to get 24-hour bandwidth consumption.
I’ve tried (for the test I’m doing this under now() to 30m):
SELECT SUM("ifHCInOctets") as "Incoming_first"
FROM "interfaces"
WHERE
time >= now() - 30m
AND time < now() - 15m
AND agent_host =~ /^$14_router_IP$/
GROUP BY "ifDescr"
and
SELECT SUM("ifHCInOctets") as "Incoming_last"
FROM "interfaces"
WHERE
time >= now() - 15m
AND time < now()
AND agent_host =~ /^$14_router_IP$/
GROUP BY "ifDescr"
Which both returns values but when I add an expression to subtract my queries:
What’s looks like a simple math (A+B) is not so simple.
Start simple math:
10 (result from query A)
+
10 (result from query B)
=
20 (expected result from math expression $A+$B)
Actually, result from query is timeseries (see query config Format as: Time series) - there is list of values and each value represent some value in time, so it can be:
10 (result from query A at 2024-01-01 00:00:00)
+
10 (result from query B at 2024-01-01 00:00:00)
=
20 (expected result from math expression $A+$B at 2024-01-01 00:00:00)
But what if those values are from different times - what should be result of math expression - I would say this is undefined behaviour, because it is not possible to do a math:
10 (result from query A at 2023-01-01 00:00:00)
+
10 (result from query B at 2024-01-01 00:00:00)
=
???
There is link to Grafana doc, which is good to read:
With binary operations, such as $A + $B or $A || $B, the operator is applied in the following ways depending on the type of data:
If both $A and $B are time series data, then the operation between each value in the two series is performed for each time stamp that exists in both $A and $B. The Resample operation can be used to line up time stamps. (Note: in the future, we plan to add options to the Math operation for different behaviors).
I highlight it: If both $A and $B are time series data, then the operation between each value in the two series is performed for each time stamp that exists in both$A and $B.
With Logs type I still get an Unknow value for my math expression and Table type returns me the erroe [sse.readDataError] [A] got error: input data must be a wide series but got type long (input refid).
By the way, I though we could only get the time of the collected data with Time Series…
Well, that’s what I’ve done, I only have three options: Time series → not working because of time Table → returns an error Logs → not working (and I guess because of time):
Well, it was not clear as well I think haha.
Table type returns me the error:
[sse.readDataError] [A] got error: input data must be a wide series but got type long (input refid)
With the query:
SELECT sum("ifHCInOctets") as "Incoming_first"
FROM "interfaces"
WHERE
time >= now() - 30m
AND time < now() - 15m
AND agent_host =~ /^$14_router_IP$/
GROUP BY "ifDescr"
I would say you can’t generate non timeseries result with InfluxDB - there is still time dimension in the result.
You can try to use transformation - but don’t expect easy win. You will have to figure out how to use multiple set of transformations to get expected result.
I would say the easier option can be to generate timeseries with the same timestamp, so expression will be working fine (e.g. timegrouping with long time period).
The best option is if you change the approach. Total is just area under the curve for subsequent field values - and that’s a task for INTEGRAL. More complex InfluxQL (math function, maybe subquery), functions instead of simple A-B on the Grafana level.
BTW: I guess ifHCInOctets is a counter, so DERIVATE may be useful as well.
After having tried numerous methods (including transformations) with no convincing results, I chose another solution. I created a script on my routers that sends the rx and tx of each interface to a php web server. The server takes care of the necessary calculations and sends everything back to a mysql database server.
SELECT sum(total_tx)
FROM bandwidth
WHERE agent_host =~ /^$routerIP$/
AND time BETWEEN
(select MAX(time)
from supervision.bandwidth) - 3000
AND
(select MAX(time)
from supervision.bandwidth)
returns me the error:
db query error: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '/^'10.211.0.2','10.211.0.3','10.211.0.4','10.211.0.5','10.211.0.6','10.211.0....' at line 3
SELECT sum(total_tx)
FROM bandwidth
WHERE agent_host =~ $routerIP
AND time BETWEEN
(select MAX(time)
from supervision.bandwidth) - 3000
AND
(select MAX(time)
from supervision.bandwidth)
Which returns the same erroe (without the " /^ ").
Also tried:
SELECT SUM(total_tx)
FROM supervision.bandwidth
WHERE agent_host =~ '${routerIP:singlequote}'
AND time BETWEEN
(select MAX(time)
from supervision.bandwidth) - 3000
AND
(select MAX(time)
from supervision.bandwidth)
so can you share error
