Investigating for IotaWatt, AWS, InfluxDB and Grafana

I’ve previously used EmonCMS to log power and energy data from a IotaWatt  / EmonTx system. This works well, but I’ve been looking for a flexible and fast alternative. The combination of IotaWatt, InfluxDB and Grafana look to provide this. My experiences are recorded below in rough notes.

A neated and shorter page summarises this messy page.

Hosting InfluxDB and Grafana

Used AWS to create a free EC2 micro instance. There are other hosting options but this is flexible – it’s a virtual machine in the cloud. It requires Linux skills. Other hosting of InfluxDB and grafana are available “ready-to-go”.

Installing

Used Putty and PuttyKeyGen to connect to EC2 instance. Followed instructions general instructions here to setup AWS then installed influxdb and Grafana via the CLI and yum. Note versions change quickly so you’re best off going to influxdb and grafana websites for the latest.

AWS Tips

To get files use Putty and PSCP. In Putty, general a profile called “aws” with the private key, login, etc. Then conveniently use this in pscp.

 c:\users\me\Desktop\pscp.exe  -load aws ec2-user@ec2-IP.ap-southeast-2.compute.amazonaws.com:*.csv .

Ensure ports are open. For a lot of services you can check locally:

curl “http://localhost:8888“

If that works, but accessing from an outside IP doesn’t, check your security group in AWS>EC2>Security Groups. For example:

To setup your IP it’s best to use Elastic-IP from the outset. This connects a static IP to a EC2 instance of your choosing. Note your EC2 IP can change upon reboot, so you Elastic-IP.

Running and Making a Database to Store Iotawatt’s Output

ec2>service grafana-server start

ec2>sudo influxd > log 2>&1 &

ec2>influx

influx>CREATE DATABASE iotawatt-database-name

influx>USE iotawatt-database-name

influx>CREATE USER bbeeson WITH PASSWORD ‘password’ WITH ALL PRIVILEGES

Setting up Grafana

Tips for Energy in Grafana and Influx

– To get human times in CLI of Influx:

influx>precision rfc3339

– Getting Energy in kWh from a power feed in W:

SELECT integral(“value”,1000h) FROM “SolarTest” WHERE $timeFilter

– Forcing a panel to use a custom time, in the Grafana panel Time Override:

Period Required	“Last”	“Amount”
Today (from midnight)	now/d
Yesterday (from midnight)	now/d	1d/d
Two days ago (from midnight)	now/d	2d/d
2 weeks ago (from midnight) to now()-1d	14d/d	1d
2 weeks ago (from midnight) to last midnight	14/d	1d/d

– Install a dummy set of known data – this is really useful for testing. Ensure you set the timezone!

python csv-to-influxdb.py –dbname iotawatt –input demosolar.csv -m SolarTest –fieldcolumns value –server ec2-your-ip.ap-southeast-2.compute.amazonaws.com:8086 -u bbeeson -p password -tz Australia/Brisbane

Multiple Iotawatts, Tags, Measurements

I tried a number of methods for this, as IotaWatt provides flexibility in setting up tags, values, etc.

1. Default Iotawatt, Seperate Databases (Measurement: power1, FieldName: value, FieldKey: 101.1)

Use seperate databases to avoid any accidents. So I’ll have iotawatt-myhouse, iotawatt-house2, etc as database names. Use IotaWatt defaults (

• Nice and simple.
• No ability to work easily between databases to find “average daily consumption on Friday 13th”.
• Difficult to do maths between Measurements
• Doesn’t use tags, which InfluxDB supports well, which in turn makes Grafana templating easier.

The influxdb tables will look like:

> show measurements
Aircon
Lights
Power
Power1
Power2
Stove
Total
Voltage

> select * from Aircon limit 1
name: Aircon
time                 device     value
----                 ------     -----
2018-07-19T10:44:00Z iotawattap 0

 

2. IotaWatt Defaults, One Database (Measurement: power1, FieldName: value, FieldKey: 101.1)

Use the device tag (“Iotawatt-one”) on all measurement and put all iotawatts’ data into the same database. Using the default Iotawatt configuration this results in lots of seperate measurement tables. I found it confusing. Some (e.g. solar) related to just one iotawatt, other related to many (e.g. lights).  Tables structured in InfluxDB as for #1.

• Messy database
• No go

3. One Measurement per Iotawatt, One Database

Use one measurement table per IotaWatt. This is nice. Everything in one table. Since all measurements have the same timestamp it makes sense. Just one database.

• You can only store one metric (e.g. power). Others such as voltage can be stored, but don’t make unit sense. Would have to prefix/suffix.
• Neat Structure, One Database
• Doesn’t use tags, which InfluxDB supports well, which in turn makes Grafana templating easier.

 

3. One/Multiple Database, One Measurement Table, FieldNames: export|import|pool

• One measurement table enables maths between fields.
• Easy to work in Grafana
• Templating okay, but must itemise the CT names

> use database iotawatt

> show measurements

name: measurements
—-
Watts
> select * from Watts limit 2
name: Watts
time                 consumption device  export import net    solar voltage
—-                 ———– ——  —— —— —    —– ——-
2018-11-14T14:00:10Z 122.58      iotah33 -0     122.55 122.55 0.04  238.61

4. Multiple Databases, One Measurement, FieldName: power|current|, TagName:CT = |pool|export|import

This method is the best so far. It uses tags. It can use one or multiple databases. For one database, just name the measurement “elec_iotawatt_1”.

• Using tags to label the CTs enable grafana templating to really work
• Multiple metrics (pf, v, etc) on one line is logical
• Yet to set this up on a IotaWatt (I used a custom EmonTX and MQTT). But reckon it’s ok.
• Complex maths between CTs is hard, but aggregate functions are available. See below.

> show measurements
name: measurements
name
----
> select * from elec10s limit 10
name: elec10s
time                 ct      device    mean_current mean_pf mean_power mean_reacpower mean_realpower mean_v
----                 --      ------    ------------ ------- ---------- -------------- -------------- ------
2018-11-20T01:29:50Z cooking emontxa33 0.005        0.005   0.005      0.005          0.005
2018-11-20T01:29:50Z dhw     emontxa33 0.007        0.007   0.007      0.007          0.007
2018-11-20T01:29:50Z fridge  emontxa33 1.183        0.663   286.713    216.083        188.453
2018-11-20T01:29:50Z garage  emontxa33 0.008        0.008   0.008      0.008          0.008

Use Sub Queries to get totals across multple CTs. This firstly combines CT values for house and power, and stores the sum in 10s intervals. Then the sum is averaged over an hour.

select mean(total) from (SELECT sum(mean_power) as total FROM "elec" WHERE ("ct" =~/(house|power)/) group by time(10s)) group by time(1h)

Summary of IotaWatt Field/Tag/Measumet

1. Simple, not flexible

2. Messy, no go

3. Works well. Okay for templates. Cross CT complex calc ok.

4. Work best. Excellent for templates. Cross CT complex calc harder.

How to show …

 

Power Graph

Really easy, just add a Graph and just set metric field to (eg) “Oven”. Best to leave defaults, as it manages the query size automatically. See JSON.

Current Value

Set the metric as above, but set Options>Value>Stat to “current” to see the last one. You can select last() in the query, to limit recordset but you probably want sparklines, which need the default query. Override the time as per the table above (now/d) to foce today’s results. See JSON link below.

Yesterday’s Energy (or last week, etc)

Use a custom query (eyeball then “edit” mode”) to set up. Otherwise the 1000h (kilowatt hour) is lost. Integral is required for correctness, but sum/60/3600 or similiar will probably work.

SELECT integral(“value”,1000h) FROM “Consumption” WHERE $timeFilter fill(null)

Then set the time override as the table above (now/d, 1d/d)

Energy Per Day

Starting to get tricky. The key is the right query. The timezone shouldn’t (?) be necessary, but for this type of query it was for me. Otherwise it used UTC.

Note the group by day too, as we want multiple days’ values. This can be used in a graph or a table.

SELECT integral(“value”,1000h) FROM “Consumption” WHERE $timeFilter GROUP BY time(1d) fill(null) tz(‘Australia/Brisbane’)

Pies are nice

Use the piechart add-in, when use integral to get the energy, similiar to above.

Finding SolarExported, SolarConsumed, Net, Consumption

I used the following conventions and had to use Continuous Queries. Everything is positive except net. I tried solar as negative but found it more, not less, confusing.

Name	Sign	Meaning
net	+ve	Import from Grid
net	-ve	Export to Grid
solar	+ve	Solar produced, always +ve
consumption	+ve	In-house consumption, always +ve Will use both solar (if available) and grid import net = consumption – solar

From these there are derived figures:

Name	Sign	Meaning	Relation
import	+ve	Grid energy imported	= net WHERE Net>0
export	+ve	Solar energy exported. Not the same as (total) Solar, as some is consumed in-house.	= abs(net WHERE net<0)
solarconsumed	+ve	Solar energy produced and consumed inhouse.	= solar – export

Other common names. No punctionation or case.

Sample	Explain yourself
iotah33	all lowercase, iota+site
lights1	first lighting circuit
lights	total lighting
powerpoints	instead of “power”
airconkitchen or aircon1	numbers of unknown
aircon	total aircon

Dealing with Solar, Net and Export/Import

I want to get a query with: Exported (kWh) and Imported (kWh). I have a CT on the net (ie. street) supply, which returns -ve for export, +ve for import. I need:

export = net (when net<0)

import = net (when net>0)

There are two methods:

Method 1: Iotawatt Maths

Setup iotawatt using the abs() function to emulate a limit function (+ve) or (-ve):

Method 2 : Continuous Queries

If you what IotaWatt to just return pure CT values, upon which you then do complex stuff, this is a better method. Three phase supplys are a likely use case, as you need to total phases and account for solar on 1 or more phases. There may be a better way, but it seems one cannot “add measurements” or have multiple selects. Sub-queries worked but didn’t solve my problem.

I used Continous Queries (InfluxDB) to generate a table/measurement “Exported”|”Imported”|”Solar”|”SolarConsumed”. I did this on 10s intervals. Documentation is here.

There is a problem here: the fill(0) command isn’t respected, so nulls instead of 0 is inserted into times when (e.g.) import is 0. This is a known issue in InfluxDB. To work around, I created a sneaky “_fill” set of queries to force 0 to be written. Also note that cq_solarconsumed operates on the (CQ-built) Derived table, so you have to add it last.

cq_solar         CREATE CONTINUOUS QUERY cq_solar ON iotawatt BEGIN SELECT mean(value) AS Solar INTO iotawatt.autogen.Derived FROM iotawatt.autogen.Solar GROUP BY time(10s) END
cq_export        CREATE CONTINUOUS QUERY cq_export ON iotawatt BEGIN SELECT -1 * mean(value) AS Export INTO iotawatt.autogen.Derived FROM iotawatt.autogen.Net WHERE value < 0 GROUP BY time(10s) fill(0) END
cq_import        CREATE CONTINUOUS QUERY cq_import ON iotawatt BEGIN SELECT mean(value) AS Import INTO iotawatt.autogen.Derived FROM iotawatt.autogen.Net WHERE value >= 0 GROUP BY time(10s) fill(0) END
cq_import_fill   CREATE CONTINUOUS QUERY cq_import_fill ON iotawatt BEGIN SELECT mean(value) – mean(value) AS Import INTO iotawatt.autogen.Derived FROM iotawatt.autogen.Net WHERE value < 0 GROUP BY time(10s) END
cq_export_fill   CREATE CONTINUOUS QUERY cq_export_fill ON iotawatt BEGIN SELECT mean(value) – mean(value) AS Export INTO iotawatt.autogen.Derived FROM iotawatt.autogen.Net WHERE value > 0 GROUP BY time(10s) END

You’ll see these all go into a common measurement called “derived”. Fortunately, but confusingly, this doesn’t suffer from doubled-up time stamps. This is simliar to method #3 in “multiple iotawatts” above. It creates on measurement (table) with multiple fields. This is useful if you need to perform operations between fields (since you can’t between measurements) but has the pitfalls of #3 (namely… naming in grafana, and going above the vibe of influxdb).

Solar Consumed v Grid Consumed

I also wanted to get SolarConsumed (kWh) over arbitary period. This is SolarProduced – Export.

I used a continuous query for this:

name: iotah33
name          query
—-          —–
export        CREATE CONTINUOUS QUERY export ON iotah33 BEGIN SELECT mean(value) AS export INTO iotah33.autogen.derived FROM iotah33.autogen.export GROUP BY time(10s) END
solar         CREATE CONTINUOUS QUERY solar ON iotah33 BEGIN SELECT mean(value) AS solar INTO iotah33.autogen.derived FROM iotah33.autogen.solar GROUP BY time(10s) END
solarconsumed CREATE CONTINUOUS QUERY solarconsumed ON iotah33 BEGIN SELECT mean(solar) – mean(export) AS solarconsumed INTO iotah33.autogen.derived FROM iotah33.autogen.derived GROUP BY time(10s) END

We can create a piechart with an integral query comparing “SolarConsumed” and “Import” as per Energy Per Day, above. In grafana:

SELECT integral(“SolarConsumed”,1000h) as “SolarConsumed” FROM “Derived” WHERE $timeFilter GROUP BY time() fill(null)

SELECT integral(“Import”,1000h) as “GridConsumed” FROM “Derived” WHERE $timeFilter GROUP BY time(1h) fill(null)

Managing data retention

With a sample period of 10s the data can really add up. Assuming 10 measurements each 10s, with 12 bytes per measurement (perhaps 8B float and 4B time?) we get:

• 1Mb per day per iotawatt

This appears okay, and is probably okay to archive, but asking InfluxDB to integrate (for energy) over 100k samples per day is pushing your luck. I ran into memory problems and since I want many iotawatts, I decided to rationalise the data. The initial pass was:

1. Copy full resolution to <database>_archive on an hourly basis. This is not to be used in the interface/graphs, but as a backup / archive.
2. Downsample to 10 minute intervals. This gives about 5Mb per year per iotawatt. This can be used for daily graphs of historic data. Do this on a 10minute basis.
3. Downsample to kwhperday and kwhperhour via INTEGRAL. kwhperhour is just kilowatts if the sample period is regular, but best to be safe an do the integral. Use this for yearly and % type graphs.
4. Retain downsampled data indefinitely
5. Retain full resolution data for a week.

I used the following queries:

1. Copy full resolution to <database>_archive on an hourly basis. This is not to be used in the interface/graphs, but as a backup / archive.

 

CREATE CONTINUOUS QUERY archive ON iotawyatt  RESAMPLE EVERY 1h FOR 1h BEGIN SELECT mean(*) INTO “iotawyatt_archive”.”autogen”.:MEASUREMENT FROM /.*/  GROUP BY time(10s),*  END

Downsample to 10 minute intervals. Do this on a 10minute basis. Since the same measurement names are used, I created a faux RP “infinite_10m” which is infinite, but labelled with 10m so you can select all measurements in the 10m group: iotawatt.infinite_10m.*. (It’s a pity “show mesaurements” doesn’t show the two same-named-but-different-RP, just one. “SHOW SHARDS” helps.)

create retention policy infinite_10m on iotawyatt duration INF replication 1

CREATE CONTINUOUS QUERY tenminutes ON iotawyatt  RESAMPLE EVERY 10m FOR 10m BEGIN SELECT mean(*) INTO iotawyatt.infinite_10m :MEASUREMENT FROM iotawatt.autogen./.*/  GROUP BY time(10m) ,* END

Notes:

• – continuous queries don’t address previous data, but you can just run the query directly. The CQ simply adds an interval when it runs automatically.
• – the :MEASUREMENT is a placement for each measurement the * captures. Like a regex capture.
• the /.*/ is a regex for “everything”
• the ,* after GROUP BY means “copy tags too”

Results

Using grafana, this works as shown below. It’s a bit painful to select the correct retention group, etc. And it adds complexity to the database setup. However, it’s nice to be able to just sum(kwh) instead of integrating.

It turns out the kwh/day and kwh/hour is not that useful! Because you get this in grafana:

It sees the kwh/day reads as spot readings. Even lines don’t work as values between the readings are null or 0. Grouping by day (or hour for kwh/hour) and using (via the “toggle editor”) fill(previous). You still need to add the tz() flag. This is all a bit hacky.

Further thoughts based on these results:

Grafana is great at only selecting the # of points you can see. So there’s little benefit in downsampling data for that reason. It is really if influxdb is running out of memory or too slow when downsampling is good. A comprise for me IotaWatt might be as follows.

• Create kWh/h each hour. Use for Energy Per Day and Pie graphs. Tests shows integrals and sub-queries could mean a 1000ms execution time for “energy this year” queries. Use the kWh/h changes these to 10ms (cached?). With kWh/h you can just sum.
• Retain full resolution data. Don’t copy to _archive. Use only for last() type queries to show current results. 360MB/iotawatt/year.

An optional step to really minimise data would be to downsampled mean(W) per minute and use for everything, with a delete of the 10s data after 1 day. 60MB/iotawatt/year.

 

But wait, there’s a more … pivots

I wanted to create a table like this:

This time (in hours, 0-24) on the x-axis, and values on the y-axis. Multiple lines represent multiple days. A ‘heatmap’, for which there are Grafana plugins, is (kind of) what I need. The  “Cal-HeatMap” can be configured to display similiar information, but with days on the y-axis and values as colours. I think the figure above is more helpful.

Another option is flux, an extension to influxdb which can perform transforms. Installed the latest InfluxDB and Chronograf to get flux. Worked for basic flux script. The pivot() function might be able to make the necessary dataset. At the time of writing, it’s in early development. It also provides the ability to join two series of measurements together. For the moment, it’s too much of a learning curve. The heatmap will suffice.

Another option is Kapacitor, which has an hour() function. After https://docs.influxdata.com/kapacitor/v1.5/introduction/installation/“>installing and

Access for Others

Sharing doesn’t work by default in Grafana. You need to setup an organisation and edit grafana.ini to allow anonymous access. Google instructions. For public dashboard, users can just be given a share link. However, only one organisation (as setup for anonymous users in grafana.ini) can be accessed. So put all “open” dashboard in a seperate organisation. For other organsiations, you must log-in.

For private boards, you can add a “View” role user to an organisation and they have to log in to grafana.

To get nice access to grafana, either

1. Redirect port 80 to 3000

2. Reconfigure to port 80

Since I want to run a webserver on the same machine, I did #1. This was involved:

• Leave grafana on port 3000
• Use Route 53 (from AWS) to get a public domain (really cheap). https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/CreatingNewSub…“>Add a subdomain (live.example.com). This just maps back to (example.com).
• Install https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-lamp-amazon-linu…“>Apache (see AWS docs).  I used LAMP cos I wanted WordPress later.
• Install a reverse proxy to tell Apache2 to send all live.example.com to localhost:3000 and do this transparently. In http.conf:

<VirtualHost *:80>
ServerName live.example.com
ProxyPass / http://example.com:3000/
ProxyPassReverse / http://example.com:3000/
</VirtualHost>

Apache2 Tips

httpd -t -D DUMP_VHOSTS # see your vhosts

httpd -t -D DUMP_MODULES # see if your mods are install.

httpd -S # see startup parsing

httpd -k graceful # restart nice