Math operations between fields

Hello,
I posted this in Stackoverflow (https://stackoverflow.com/questions/43902592/math-operations-between-measurements) because I didn’t knew this forum. This is the issue.

I’m sending measurements to InfluxDB using Telegraf, but I have troubles trying to do math operations between different fields.

For example, I have a data set like this:

SELECT * FROM "Storage" WHERE hostname = 'agent01'

time                Device                          StorageAllocationUnit StorageUsed TotalSize agent_host   host  hostname
----                -----------                     --------------------- ----------- --------- ----------   ----  --------
1494279001000000000 Virtual memory                  1024                  385169092   400171824 agent01      host1 agent01
1494279001000000000 Swap space                      1024                  144         4194300   agent01      host1 agent01
1494279001000000000 Shared memory                   1024                  2830276     2830276   agent01      host1 agent01
1494279001000000000 Physical memory                 1024                  385168948   395977524 agent01      host1 agent01
1494279001000000000 Memory buffers                  1024                  3973996     395977524 agent01      host1 agent01
1494279001000000000 Cached memory                   1024                  306704448   306704448 agent01      host1 agent01

I want to calculate the used Memory with this formula:

Physical memory = StorageUsed*StorageAllocationUnit where Device = 'Physical memory'
Cached memory = StorageUsed*StorageAllocationUnit where Device = 'Cached memory'
...
Used Memory = Physical memory - Cached memory - Memory buffers - Shared memory

It is possible to do math operations between fields? all of them are in the same measurement.

I’m using InfluxDB 1.2.2.

1 Like

@Silva Each of what you are calling “measurements” are actually being stored as fields in influx. Also what you are calling a “series” is actually a measurement in influx. Math between fields in a measurement is supported in InfluxDB. Can you share the queries that you have tried?

1 Like

I updated the post with the changes that you told me.
I tried using JOINs but Influx don’t support that command, and I don’t know other way to do it.

I have to substact the outcome of these 2 queries:

SELECT StorageUsed*StorageAllocationUnit FROM "Storage" WHERE hostname = 'agent01' where "Device" = 'Physical memory';
SELECT StorageUsed*StorageAllocationUnit FROM "Storage" WHERE hostname = 'agent01' where "Device" = 'Cached memory';

Like this:

SELECT (SELECT StorageUsed*StorageAllocationUnit from "Storage" where "hostname" = 'agent01' and "Device" = 'Cached memory') - (SELECT StorageUsed*StorageAllocationUnit from "Storage" where "hostname" = 'agent01' and "Device" = 'Physical memory') as Difference

Looking for the same - one measurement math between 2 fields (both calculated by non_neg_derivative).

SELECT “compress” FROM (SELECT (non_negative_derivative(mean(“bytes_recv”), 1s) *8 WHERE “interface” = ‘eth1’) - (non_negative_derivative(mean(“bytes_sent”), 1s)*8 WHERE “interface” = ‘eth2’) as “compress” FROM “net” WHERE “host” = ‘ovpn1’ AND $timefilter GROUP BY time($interval))

Any ideas regarding my request?

Many Thanks in advance!

I have exactly same problem. I need some kind of way to do math between fields.
I use telegraf to collect CPU and memory data from various types of devices via SNMP. I have no problem writing data to InfluxDB and Elasticserch and later I use queries to calculate percents. But problem is that various device report these types of metrics differently. Some report used memory + free memory, some used memory + all memory and etc. So I end up having to put up to 10 different queries in Grafana just to make universal dashboard for memory.

Hi ,

I am also trying in similar kind of Quires , My intention is to Calculate “Physical Memory excluding Buffer ,Cache and Shared memories” from SNMP Data. can any one help us on this ??

I gone through “Cannot compute math on result of two subqueries · Issue #9779 · influxdata/influxdb · GitHub” closed in 2018 , is computation on sub query elements issue, still persists ? or is there any work around for this ??

Hello. From what I’ve attempted it seems you must include a function for each field. Lets say you want to subtract the sum of two fields, fieldA and fieldB. To do this you must use the functions as follows:
SELECT sum("fieldA") - sum("fieldB")

An incorrect way of attempting this would be:
SELECT sum("fieldA"-"fieldB")

Hope this helps anyone who stumbles on this thread. I know it’s a little late for OP and others.