Help writing continuous query subtracting two series

1

I am new to influxdb and writing queries and need some help. I would like to setup a continuous query that subtracts two values with the result being stored. I am not very familiar with query language so that is where I am struggling.

What I want to do is subtract

SELECT * FROM "Home" WHERE "topic" = '/house/power/main'

from

SELECT * FROM "Home" WHERE "topic" = '/house/power/solar'

and store in a “topic” called “/house/power/netmain” at the same time interval the rest of the data is being collected.

I am using influxdb v1.2 and my data is setup as shown below. This data is going to Grafana so if there is a way to do this there without the continuous query that would work too.

time host topic value 2017-05-17T01:31:49.303162625Z "AllingtonServ" "/house/power/main" 2323.419 2017-05-17T01:32:20.30254561Z "AllingtonServ" "/house/power/main" 2304 2017-05-17T01:32:51.308210601Z "AllingtonServ" "/house/power/main" 2322.935 2017-05-17T01:31:49.303162625Z "AllingtonServ" "/house/power/solar" -1250 2017-05-17T01:32:20.30254561Z "AllingtonServ" "/house/power/solar" -1240 2017-05-17T01:32:51.308210601Z "AllingtonServ" "/house/power/solar" -1238

2

@adamalli In order to do math on data like you are describing you need to write that data as fields. Your writes in that case would look as follows:

AllingtonServ,tag1=tagValue house_power_main= 2323.419,house_power_solar=-1250 TS
AllingtonServ,tag1=tagValue house_power_main= 2304,house_power_solar=-1240 TS
AllingtonServ,tag1=tagValue house_power_main= 2322.935,house_power_solar=-1238 TS

Then you would use a continuous query similar to the below:

CREATE CONTINUOUS QUERY non_solar_generation ON data BEGIN
  SELECT (mean(house_power_main) - mean(house_power_solar)) AS derived 
  INTO AllingtonServDerived
  FROM AllingtonServ
  GROUP BY time(30s)
END

You could also skip the CQ and just run the math on the fly with the inner SELECT statement there.

3

Unfortunately my data is coming from individual MQTT subscriptions using telegraf to input into influxdb. My setup has 96 subscriptions, 32 in each topic. With in each topic the channels are named like main, solar, liv lights, etc…

My telegraf setup is as below.
topics = [
"/house/volts/#",
"/house/power/#",
"/house/energy_total/#",
"/house/dif_energy/#",
]
It sounds like the way my data is being collected it is not going to work for the format you suggest above.

4

@adamalli So I’ve been trying to do this with INTO queries for a while but I don’t know if I can. It looks like you are writing points into MQTT like so AllingtonServ value=1 TS with no other metadata and then you are relying on the MQTT topic to provide context. This is not a recommended way to use the database. Your particular sin would be stuffing an individual tag with too much metadata. I would assume you probably have a HouseId=XXXX tag as well.

At time of write to MQTT in your client you have all of the information needed to write the field names. You can still write them as individual points (AllingtonServ,HouseId=XXXX power_main=1939 TS) and have the database take care of them as long as the timestamps are set client side and the metadata all lines up. Is it possible to make that change to your clients?

If not you would likely need to use the Kapacitor flatten node and the dropOriginalFieldName node to make the data look as I described:

stream
    |from()
        ...
    |flatten()
        .on(topic)
        .dropOriginalFieldName(TRUE)

The reason for this is that InfluxDB only allows math between fields in the same measurement.

5

Well that all makes sense. Some background for you. I have a power monitoring device called Greeneye monitor that monitors the power of every circuit in my house. I then use a python script called btmon.py that was written to upload data to various online services. One of the services it provides is to publish to a MQTT server. It could probably be modified to have the better format you state above, but I am not a programmer so I will have to see if the original developer could make those changes.

The python script does have the ability to input data directly to influxdb in the format you mention, but I wanted to use MQTT for some other reasons.

6

@adamalli Well you could always use telegraf as your metrics batcher and forwarder. That InfluxDB output would be idea.

7

Hello I have the same problem I think.
The single queries are ok but the combined one give no result, any suggestion?

SELECT (non_negative_derivative(mean(“POWERPANNELLI”), 120s )- non_negative_derivative(mean(“POWERCASA”), 120s ))

AS “Consumo”

FROM (

SELECT mean(“ENERGY_Power”) AS “POWERCASA” FROM “telegraf”.“autogen”.“CASA” WHERE time > :dashboardTime: AND time < :upperDashboardTime: GROUP BY time(1m)

), (

SELECT mean(“ENERGY_Power”) AS “POWERPANNELLI” FROM “telegraf”.“autogen”.“Pannelli” WHERE time > :dashboardTime: AND time < :upperDashboardTime: GROUP BY time(1m)

)

GROUP BY time(1m) fill(null)