Addition of queries via Expression option not working

Hey guys,

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:

with number unit, the data looks like this:

Also, by testing with numerical numbers, it’s working:

So I’m basically stuck at this point…

I’ve also tried the transform data option, but I got the same result.

If it can help, I can create a variable to get the interfaces name.

Thanks in advance

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.

Editor has feature Table view feature:

Use it and inspect/debug time series for each query and make sure you have values with right time stamps, when you want to perform math.

But I would say just don’t generate time series and it will be simple math as you expect.

1 Like

Once again that have been a very good help!

So I can’t actually do what I want to do since I need to “play” with time for the operation?

Any other idea to an alternative solution?

But I would say just don’t generate time series and it will be simple math as you expect.

Just don’t use time series - I don’t see a reason why you need to format them as a timeseries:

1 Like

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…

Why log type? If you are only guessing, then please try also all other options there.

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):

we cant remotely see your screen :stuck_out_tongue_winking_eye: so can you share error

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"

1 Like

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.

What happens if you remove the time filter from the where clause
For test purposes
Also what data type is time?

Sorry for the delay, I went back to school…

Do you mean like this:

SELECT sum("ifHCInOctets") as "Incoming_first"
FROM "interfaces"
WHERE 
  agent_host =~ /^$14_router_IP$/
GROUP BY "ifDescr"

which returns me weird results as well:

image

I don’t understand what you mean by

I guess time is the Unix Time Stamp you can easily found on influxDB base:

time
----
1707730497000000000

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.

But now I have a new problem:

The database looks like this:

+------------+------------+------------------------+------------+-----------+
| time       | agent_host | intName                | tx         | rx        |
+------------+------------+------------------------+------------+-----------+
| 1707911594 | 10.211.0.2 | LoopBr                 |  101335670 |         0 |

but this query:

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

I’ve also tried the query:

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)

Which returns the same error

I’m closing this topic as it seems to be changing subject. I’m reopening another one concerning my last message.

1 Like