In a previous article I described how to use InfluxDB to collect data from an ESP8266 and present that in Grafana or Chronograf. Grafana can also be used to present data that has been collected in a MySQL or MariaDB database. If you want your data as a graph, make sure that the data is stored as a number (albeit an int or a double or a float). If it is stored as a string, Grafana can only put it in a Table.
Suppose one has an SQL database called ‘esp-data’ that has a Table called ‘Sensor’ with the following structure:
MySQL datastructure
That can be presented in Grafana as a graph (or a table), but we need to prepare a few things: It is best to make a user that has only ‘Select’ priviliges. That is because the SQL statements we use later in Grafana could influence your data when a mistake is made. In order to create a new user go into MySQL, type mysql -u yourusername -p
then: CREATE USER 'grafana' identified BY 'GRAFANA' followed by: GRANT SELECT ON * TO 'grafana' This created a usr called ‘grafana’ with password ‘GRAFANA’.
You may also need to configure your MySQL for remote access, but for now we leave it as is. I will come back to that later.
Next thing to do is to go to your grafana interface. You will find that at the computer it is installed on, at port 3000. So type ‘localhost:3000‘ in your browser. When it opens up, go to ‘datasources’. after logging in, go to the cogwheel in the left column and choose ‘Data sources’. Then pick ‘MySQL’ and fill in the details below. Ofcourse you can give the datasource another name than I did and/or choose another timezone. Then click “test and Save” and if everything goes well, you should have the connection made. (note: the image shows ‘password configured’ as i already entered the password. You will still need to enter the ‘GRAFANA’ password)
Grafana datasource
If however you get an error, there can be a number of possibilities. You will have to check the logfile at /var/log/grafana/grafana.log to see what exactly went wrong. What usually goes wrong is that you didn’t create the right user, that your timezone was an unknown format (remove it to make sure), or that MySQL was not started. What can also cause a problem is when your MySQL doesn’t accept remote connections. That can be fixed: In your console type:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
In that file, look for this line:
bind-address = 127.0.0.1
Change it to:
bind-address = 0.0.0.0
Save and close the file. Restart the MySQL service with:
sudo systemctl restart mysql
When you have the connection established goto the ‘+‘ sign in the left colomn, choose: ‘Create’ and ‘ Dashboard’ and then ‘add new panel’. Fill that in like so:
That will give you you a graph akin to this one, provided there is data in the time frame you have selected
The time frame to look at can be selected at the top of the screen:
Do not forget to save your dashboard!!
In this article I presumed you already had a MySQL database that was being filled by an ESP8266. If you do not, my advice is to not use MySQL/MariaDB but to pick InfluxDB, but if you insist on using MySQL, randomnerdtutorials has an excellent article on how to set up a MySQL database and fill it with an ESP8266. I also presumed you already had grafana running, if not, refer to the article linked at the top of this post
In an earlier post I referred to the use of InfluxDB and Grafana to store and present data. In this post I will explain how to do this and i will also add Chronograf, another graphics package. There is a recent post of randomnerdtutorials on Influxdb as well, but where Rudy and Sarah discuss the use of a Cloud server (with only 30 days data retention in their free plan). For those interested, it might be good to know that recently InfluxDB allows filling a database through MQTT messages. I though will be using a local install of InfluxdB and will connect it to Grafana. As i am using Linux, that will be the install I will be discussing as that is the one I can test.
Install InfluxDB (Ubuntu) One can use the Software manager for that, but I am not sure which version that will install (could be 1.6). I will do a manual install of version 1.8 (there is version 2, but I still prefer 1.8). (for other OS’s check here)
Type the following commando’s in your Command line
sudo echo "deb https://repos.influxdata.com/ubuntu bionic stable" | sudo tee /etc/apt/sources.list.d/influxdb.list
sudo apt update
sudo apt install influxdb
Once influxdb is done installing, check the status
sudo systemctl status influxdb
In your case it will probably not say ‘active’ but more likely ‘dead’, as it is not running yet. The next command will start it and make sure it is reloaded every time you start your system
sudo systemctl enable --now influxdb
Should you prefer a manual start, one can do that with
sudo systemctl start influxdb
Configure InfluxDB
type:
sudo nano /etc/influxdb/influxdb.conf
That will open the config file. Go to the [http] section and uncomment ‘enable = true’ and uncomment the bind-address
Save a the file and leave the editor with Ctrl-X and type ‘Y’ on the question ‘save file?’
As the influxDB records are all time based, a timestamp is automatically added to the records. The tag keys are optional, the ‘measurement’ is not Anyway we will call the measurement ‘beds’ and add a tag-key ‘bedname’. Then we add 4 fields called ‘moisture’,’temperature’,’licht’ and ‘druk’ (the latter two being dutch words for ‘light’ and ‘pressure’, you may rename them). INSERT beds,bedname=bed1 moisture=590,temperature=24.5,licht=1400,druk=1013 and then show the record with the ‘select’ command
Add a couple of more records this way
Mind you that InfluxDB stores numbers as floats. If you specifically want to store them as integers, you have to add the suffix ‘i’, your command line should look like this:
Once you have sent a value that created a float, you cannot send integers to that field anymore.
As filling a database with manual commando’s is no fun, we will let the ESP8266 do that. For simplicity i will not read out any real sensors, but use random values, but you can easily replace those by a sensor of your choice:
#include <esp8266wifi.h>
#include <esp8266wifimulti.h>
#include <influxdb.h>
#define INFLUXDB_HOST "192.168.2.9" //Enter IP of your device running Influx Database
#define WIFI_SSID "yourssid" //Enter SSID of your WIFI Access Point
#define WIFI_PASS "yourPW" //Enter Password of your WIFI Access Point
ESP8266WiFiMulti WiFiMulti;
Influxdb influx(INFLUXDB_HOST);
void setup() {
Serial.begin(115200);
WiFiMulti.addAP(WIFI_SSID, WIFI_PASS);
Serial.print("Connecting to WIFI");
while (WiFiMulti.run() != WL_CONNECTED) {
Serial.print(".");
delay(100);
}
Serial.println("WiFi connected");
Serial.println("IP address: ");
Serial.println(WiFi.localIP());
influx.setDb("garden");
Serial.println("Setup Complete.");
}
void loop() {
InfluxData row("beds");
row.addTag("bedname", "bed1");
row.addValue("moisture", random(500,800));
row.addValue("temperature", random(10, 40));
row.addValue("licht", random(1100,1400));
row.addValue("druk", random(980, 1023));
influx.write(row);
delay(20000);
}
If you wish you could add a line likerow.addValue("rssi", WiFi.RSSI()); which will add the WiFi signal strength.
Should you want to delete 1 or more records, do that with the ‘DROP SERIES command, like so: DROP SERIES FROM beds WHERE bedname='bed1' In InfluxDB only tags or measurements can be part of this WHERE clause, fieldnames cannot. You can do all sorts of selections on the dataset, try for instance: select temperature from beds where time > now() - 5m.
That concludes the InfluxDB section, but let me just add a few words: This is version 1.8. Should you point your browser to localhost:8086, you will find a 404 error. In version 2 you supposedly will find a user interface.
Grafana (on Ubuntu)
Grafana can be installed via the software manager in Ubuntu, but if you want more flexibility, check this link. or this one (for various operating systems). (Update: per June 7,2022 the newest version is 8.5.5. ) Install like this:
You can check your current version by going to any dashboard and go to settings:
Grafana version
After installation of Grafana, start it and go to http://localhost:3000. The grafana user interface opens
In the left navigation of the Grafana UI. At the left hand side is a column with icons: hover over the gear icon to expand the Configuration section. Click Data Sources. Click Add data source. Select InfluxDB from the list of available data sources. On the Data Source configuration page, enter a Name for your InfluxDB data source. Under Query Language, select either “InfluxQL” or “Flux”. I picked InfluxQL. I understand (but am not sure, that with InfluxDB version 2, picking Flux is the better option.
Under HTTP, you most likely will find ‘URL http://localhost:8086’. Leave that for now, but we may have to revisit it.
Under Auth, select Basic auth
Under BasicAuth Details, enter:
Under InfluxDB Details, enter the following:
User:-> john
Password:->JOHN
Under InfluxDB Details
Database: your database name -> garden
User: your InfluxDB username: ->john
Password: your InfluxDB password:-> JOHN
HTTP Method: Select GET or POST:->POST
Once you are sure you filled out everything correct click “Save &Test“
Now go to the ‘+’ sign in the left hand column and Create- Dashboard and then ‘Pick an Empty Panel’
As we have 4 fields that we are interested in (temperature, moisture, light and pressure, we will be adding 4 fields. Under ‘Data source‘ pick the connection we created earlier (InfluxDB). Under ‘FROM–Select measurment‘ you should get the option ‘beds’ (which is the dataset we have been creating. Pick that one.
Under SELECT field, pick the field you want. I picked ‘Druk’ (=pressure). Under ALIAS type ‘Airpressure’ You should end up with this
Now add 3 more series in which you choose the other 3 channels and give them an appropriate ‘ALIAS’. Once your program has collected a number of data points, it should look something like this:
That is the basic setup of InfluxDB with grafana with an ESP8266.
Adding a BME280
So let’s now add a real sensor, like the BME280. That only requires a slight alteration of the program:
#include <ESP8266WiFi.h>
#include <ESP8266WiFiMulti.h>
#include <Wire.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BME280.h>
#include <InfluxDb.h>
#define INFLUXDB_HOST "host_ip_nr" //Enter IP of device running Influx Database
#define WIFI_SSID "Your SSID" //Enter SSID of your WIFI Access Point
#define WIFI_PASS "Your Password" //Enter Password of your WIFI Access Point
ESP8266WiFiMulti WiFiMulti;
Influxdb influx(INFLUXDB_HOST);
//BME280
Adafruit_BME280 bme; // I2C
float temperature;
float humidity;
float pressure;
// Initialize BME280
void initBME(){
if (!bme.begin(0x76)) {
Serial.println("No valid BME280 sensor found");
while (1);
}
}
void setup() {
Serial.begin(115200);
WiFiMulti.addAP(WIFI_SSID, WIFI_PASS);
Serial.print("Connecting to WIFI");
while (WiFiMulti.run() != WL_CONNECTED) {
Serial.print(".");
delay(100);
}
Serial.println("WiFi connected");
Serial.println("IP address: ");
Serial.println(WiFi.localIP());
influx.setDb("garden");
initBME();//call BME280 initiation routine
Serial.println("Setup Complete.");
}
int recordnr = 0;
void loop() {
temperature = bme.readTemperature();
humidity = bme.readHumidity();
pressure = bme.readPressure()/100.0F;
loopCount++;
InfluxData row("beds");
row.addTag("Sensor", "BME280");
row.addTag("RecordNr", String(recordno));
row.addValue("temperature", temperature);
row.addValue("humidity", humidity);
row.addValue("pressure",pressure);
row.addValue("rssi", WiFi.RSSI());
influx.write(row);
Serial.print(String(recordno));
Serial.println(" written to local InfluxDB instance");
Serial.println();
delay(60000);
}
A quick check of the database shows it is filling up:
Then we add 3 queries to our Grafana dashboard, like so:
repeat this for the pressure and temperature. Don’t forget to set proper names for the sensor readings under ‘ALIAS’ in the query.
And now the dashboard will look like this:
If you only see dots instead of lines, go to the properties at the right side of your screen and set the following parameters
As you can see, the temperature and humidity are a bit pushed together because of the high value of the pressure readings. It is possible to only view a subset of the graph by clicking on the legends you want to see. Use shift-click for more than one line.
It is also possible to define mare than 1 and even more than 2 Y axis, each with their own scale:
Grafana offers a lot more possibilities and once you reached this point, it is best to just play around with the various options presented. Don’t forget to save your dashboard.
Chronograf (on Ubuntu)
Chronograf is another package that can be used to present InfluxDB data, it is in fact from the same manufacturer. Installation is as follows:
Subsequently you can add your fields, which will fill out the Query automatically
Under the ‘visualization button you can modify the properties of the graph. Then use the green button to ‘send’ your query to the dashboard. The dashboard will open and at first you may be daunted by the many text windows telling you you need to install a plug-in.
Fear not as these all relate to functions/queries one CAN add (but doesn’t need to). Just scroll to the bottom and you will find the graphs you are looking for.
Ofcourse it is possible to combine queries in one graph, like so:
One can delete all the before mentioned text windows, which is a bit tedious. It is apparently possible to avoid these popping up by disabling the “_internal” database in a no doubt deeply hidden config file.
Summary InfluxDB is a very versatile database that is different from and dare i say more flexible than databases as MySQL or MariaDB. Grafana and Chronograf are two extremely versatile graphic packages that can be used to present data from a number of sources, including influxDB. On a whole I found Grafana slightly easier to use and producing nicer graphs, but that is no doubt because i have more experience with Grafana than with Chronograf.
Earlier I described how to send messages from your ESP8266 (or ESP32 for that matter) via the Telegram message app. Signal is a slightly less known app that is getting more and more popular and it is also possible to send messages from your microcontroller through that app. It is even simpler (in my humble opinion than via Telegram). Here is what to do: install the messenger app on your phone and register. Add the number “+34 603 21 25 97” to your contact list and give it a name (let’s say ‘Signal Bot’)
Yes, the SignaBot has Whatsapp too
Send the following message to that contact: “I allow callmebot to send me messages”
You should get a reply “New user registered”, followed by an API key
It is now possible to send messages to your phone with https:/api.callmebot.com/signal/send.php?phone=<yourphonenumber>&apikey=xxxxxx&text=This+is+a+test
So suppose your international phonenumber =+31612345678 and your Api=123456, and your message is “Hello World”, then the call will become:
Currently it is not possible yet to send messages to Signal, but that supposedly is in the making.
Program A https connection is necessary, which the ESP8266/ ESP32 can do. Unles you plan to send highly classified material, it is easiest to do away with certificates or fingerprint and use ‘client.setInsecure();’ You can download the program here.
Just a few remarks: My first version of the program worked immediately. However, it did send one message, and somehow it would never send another message. Tried several solutions, but none brought it back to life. Then I picked a basic example of Ivan Gorotkov that I adapted to use with setInsecure() rather than with fingerprint. As Ivan’s core code was virtually akin to my first program, albeit with some checks on the http request….it did not work. Curious about what the request reply was (hoping to find an indication of the problem), I started fiddling a bit with the cut-off character. Normally this was a “\n”, but that is not very helpful if the first character is in fact a return. To my surprise, using another character, suddenly brought the program back to life and it has been working reliable since (albeit that one time a message took a bit long to arrive). Now it should not really matter in what way i read the reply string in order for the program to function properly, but it does, so I suspect there is a bit of a timing issue before the connection is closed. If anybody can give some insight in this i would be glad to hear it.
mind you though that telegram als has its own bot service I refer to that one at the beginning of this article. As i do not use facebook, I cannot really try that out, but the procedure is described here.
The AyncElegantOTA library is an easy way to add WebOTA to your projects with well, uh an ‘Elegant’ interface.
A recent discussion an facebook was about ‘how can I modify the AsyncElegantOTA?’
Ofcourse it is not so hard to do that if you know something about JavaScript and/or HTML, but one who dives into the library files will soon see that a large part of it seems to be encrypted, which no doubt is a hurdle for many, but it can be taken.
It might be worthwhile though to ask yourself why you would want to change the AsyncElegantOTA library, why not just use the native ESP8266HTTPUpdateServer library and make any desired modifications in that one (coz let’s admitt it, that one has a boring interface). No need to decode any encrypted files. You will find that library in C:\Users\<name>\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\2.7.3\libraries\ESP8266HTTPUpdateServer.src. There is a great example of this library being modified to include password protection and to look a bit more jazzy.
But if you really want to tackle the AsyncElegantOTA library here is how to do it:
Go to where you installed the AsyncElegantOTA files and open the elegantWebpage.h file. It looks like this:
The part we are interested in is de PROGMEM content, but we can’t do anything with it as long as it is still encoded. To decode it copy the PROGMEM content in its entity, without the brackets. So in fact just the numbers with the comma’s in between
Then go to the Cyberchef webpage, and paste the PROGMEM content in the input field. We need to do 2 decoding actions on the inputfield. First: Under operations choose “From Decimal” (under the heading ‘Dataformat’), double click it so it is moved under “Recipe” and pick ‘Comma’ as the delimiter. (if you follow the link I gave, both operations should already be added)
However, before you add the second operation (the ‘Unzip’), we will first do a check: Look in the ‘Output’ header. You will find 3 parameters
If you check ‘length’ in the ‘Output’ heading, you will see that number is equal to the ELEGANT_HTML_SIZE constant that is mentioned in the elegantWebpage.h. if you are using version 2.2.5 of the library, that number most likely is “53715”. Just take a mental note of where/when that number popped up (i.e. ‘after the ‘From decimal conversion of the encoded section’) because we need that mental note later to calculate a new value Output’ The second decoding we need to add is an unzip. So under operations we now choose “Gunzip” (find it under the ‘Compression’ heading), and in the output file an HTML structure should appear.
If you scroll through the HTML, you will see that it has JavaScript embedded in it the HTML code. This in total (the HTML and the JS) is the code you can modify. What you exactly want to modify is up to you.
Once you have modified the HTML /Javascript file, there are several ways you could embed it in the library again; You could leave it uncrypted and put it back in your elegantWebpage.h file as a ‘Raw literal’, like so:
provided that “ELEGANT_HTML” now is the name you gave to the raw literal. This is not the method I followed though, as I found it simpler to just encode the HTML file again. In order to do this, we go back to the Cyberchef website but now paste the HTML file in the input field. Either remove or pause the two previous operations and now add “Gzip” and “To Decimal”, like so:
If you have done right you now should get a new decimal code in your output field. Copy that and paste it over the previous PROGMEM content in your ‘elegantWebpage.h’ file.
You are now almost done. you need to update the size constant in your elegantWebpage.h file.
const uint32_t ELEGANT_HTML_SIZE = 53715;
Well, remember how I asked you to make a mental note earlier of where you could find the length of the file?
Well we have to find that length for our modified and recrypted file After you copied your “altered and recrypted to decimal again” file from the output field and pasted it in the elegantWebpage.h file, you now paste it in the input field of the Cyberchef website again. Make sure the only operation you choose is “From Decimal” with ‘Comma’ set as delimiter. Now in the Output field header you will see the new size: use that one to update the ELEGANT_HTML_SIZE constant.
In this post I will describe a monitor for a Coldframe (that is sort of a small unheated greenhouse).
It has the following functions/uses the following sensors:
A HTU21D to measure humidity and temperature inside the coldframe. The HTU21D is a very stable easy to use sensor….but after it has been exposed to 100% humidity for a while, it starts to behave funny, it may give you readings of over 100% or even negative readings. Not sure if this will leave longterm damage but I understand it can be ‘cured’ by putting it in a 120 degree celsius oven for about an hour.
Four DS18B20’s to check temperature in the soil, the air outside the coldframe, the electronics as well as a reservoir.
A soil moisture sensor, consisting of 2 carbon rods (those are resistant against electrolysis). Though I could have used the A0 pin of the ESP8266 I am using, I found that one to be unreliable, so I am using a PCF8591 ADC for that. The PCF8591 is a reltively slow 8 bit ADC, but it suffices for the goal. If you want a fast one, consider the ADS1015.
A leaf wetness sensor (actually it is cheap chinese webshop ‘rainsensor‘). When used upside down it measures condensation, which is a measure for leaf wetness. I use the pcf8591 ADC to read it.
A solar panel as a backup power source. I use one channel of the pcf8591 to measure the voltage.
A rain meter (outside the cold frame of course) that I read with yet another channel of the pcf8591.
Time is set by a time server and after that kept by the ESP8266
A Switch, checking if the lid is open.
There are also three outputs:
A fan for ventilation. I have the fan blowing out air from the coldframe -in case it got too hot- but I probably will just put it in another position and use it simply for circulation. The fan is driven through a transistor driver.
A fan circulating hot air through an underground heat sink. This fan is autonomous, so there is no switch on the webserver-interface. It does two things: if the coldframe is getting too hot, it will blow that air through the heatsink, thus storing it. If the coldframe gets too cold and the temperature of the heatsink is higher, it will push the warmth of the heatsink into the coldframe. The heatsink is an underground space filled with stones, throughwhich I have led a pipe.
A motor to open the lid of the cold frame. The motor is driven through a MOSFET Credit where credit is due, the code around the outputs ‘I stole’ from randomnerdtutorials and modified it for my own use
The reporting and control is done as follows:
A webserver connected to the home network (ESP8266 in STA mode)
A stand-alone webserver (ESP8266 in AP mode)
Thingspeak
A MySQL/MariaDB database on a separate computer (e.g. a raspberry). This is also based on some articles by randomerdtutorials.
Googlesheet.
Telegram messages: I use Telegram to report the min and max temperature every day at 6 a.m. It also sends me a message once the temperature goes over a set value. In future I may start to use it to control the Outputs described above
The processor used is a Wemos D1 mini, It might be wise though to get a Wemos D1 mini Pro or an ESP8266-07 board as that has an antenna. Could come in handy, depending where in the garden you place the coldframe. One more remark about the HTU21D, both the sparkfun library and the adafruit library are rather ‘spartan’ in the use of the HTU21D sensor. It has the possibility of heating up the sensor to prevent condensation. I have added some code to switch that on when the sensor is close to 100% humidity, but only during a few hours in the night. When that internal heating is switched on the temperature is shown in red, like shown below
HTU21D Heated
Construction. it is not my intention to fully describe how to make a coldframe (I may in future on ‘instructables’), as I presume everybody can make a box with a lid from glass. The one I made is not perfect as it only lets in light from above. I built it during the corona lockdown and had to make do with what I had. But i do want to point out a few details: I put all the electronics in a wooden box with plastic lid, that I just hung in the Coldframe. It has the BH1750 (cover temporarily removed for the picture) on top of it
For the back and one side I used polystyrene, covered with duct tape. In my experience that holds up quite long against the elements. The construction to open the lid took a lot of pondering. Most of the linear actuator solutions required me to attach the push rod to the lid…which would impede manual opening. In the end I decide for an egg shaped disc that is eccentrically connected to a motor so when it turns it opens and lowers the lid. make sure to apply some grease on the turning part.
For those not familiar with cold frames: The main ‘danger’ of a cold frame is that it gets too hot during the day. Opening the lid is essential then. It is easy to automate that: insert a line that checks the temperature against a set value and start the motor. As I have chosen to let the motor turn one direction that would mean let it turn for a few seconds and then stop it. I could fine-tune that by adding a position switch, or even use an H-bridge (requiring an extra pin), but as I have not been on holiday much during the lockdown, I have chosen to keep it manual.
To measure the outside temperature I put a DS18B20 in a piece of conduit pipe sprayed black on the top half. the idea behind that is that the top will heat more than the bottom, causing an updraft, so the DS18B20 that is located in the bottom will always get fresh outside air
The code: The core of the code is an Asynchronous webserver that presents the sensordata via server events, every 10 seconds. To prevent the ‘on-start’ values being empty I do an initial replace of the placeholders with the freshly read sensordata. and to make sure I also start with a negative number for the timer so the first 10sec update comes almost immediately.
If one wants to fill a webpage with data there are several approaches: 1. build the webpage, then read the sensors and fill them in; 2. Read sensors while building the webpage and fill them in step by step; 3 read all the sensors first. I have chosen for the latter.
The webserver contains several icons. I have used 3 ways to put those icons there: 1. from the fontawesome library; or of not available there: 2. directly inserted with base64 codes; or 3. from SPIFFS. I did that mainly to play around with different techniques, but to use SPIFFS just for 1 picture might be a bit silly and you may consider to not do that. (Edit: I did away with the SPIFFS picture, just picked another one from fontawesome)
The code keeps track of max and min temperature during a 6 a.m to 6 a.m. 24 hr timeslot. Should you lose power or update the code in between via OTA, or if for whatever reason the processor resets, the kept min and mac temperatures are lost. It is easy to prevent that by storing them in SPIFFS (but you will rapidly wear out the EEPROM), or RTC memory (in a 4 byte per value block). The RTC memory survives reset but not a power outage. I just did not find it important enough to bother with and it introduces some other problems. Mind you that rtcData stored in the first 32 blocks will be lost after performing an OTA update, because they are used by the core internals.
Thingspeak The thingspeak code is very straightforward. You will need to open a Thingspeak account though and note the channel number and write API code to insert in the program.
When using Thingspeak, one can install one of the many Thingspeak apps (such as Thingshow, Thingview or Thingviewer)on one’s phone for easy acces to the data.
Thingshow
MySQL In order to use this you will need to setup MySQL or MariaDB on a server somewhere. A raspberry Pi would be suitable for that. Earlier I referenced to two articles on randomnerd tutorials that describe how to set up a MySQL database, no need to reinvent the wheel here, although I did adapt the code a bit.
Googlesheet You wil need to setup Googlesheet for that as I have described here. You will find an example here too.
InfluxDB I wrote a separate article on collecting data in influxDB (and show it in Grafana), which is rather easy to do that with a POST statement. Add code like this:
void (influxDB() { influxData ="........" ---> add your fields and data here
// Create POST message ifhttp.begin(influxUrl); ifhttp.addHeader("Content-Type", "application/x-www-form-urlencoded"); ifhttp.setAuthorization(influxUser, influxPassword);
// The first attempt to send returns -1 int httpCode = -1; while(httpCode == -1){ httpCode = ifhttp.POST(influxData); http.writeToStream(&Serial); } ifhttp.end(); }
There is also a library for InfluxDB. The program below fills a database ‘garden’ and a set called ‘bed’ with (random) values for moisture, temperature, light and barometric pressure
#include <ESP8266WiFi.h> #include <ESP8266WiFiMulti.h> #include <InfluxDb.h> #define INFLUXDB_HOST "YourInfluxdbIP" //Enter IP of device running Influx Database #define WIFI_SSID "YourSSID" //Enter SSID of your WIFI Access Point #define WIFI_PASS "YourPW" //Enter Password of your WIFI Access PointESP8266WiFiMulti WiFiMulti; Influxdb influx(INFLUXDB_HOST);
Carriots Carriots, now called Altair Smartworks is an IoT data collection and processing site. It allows you to store your data and have it processed and attach specific actions to it. It might be useful and is fairly easy to add. Check here.
The code can be updated via website OTA. For development serial OTA is the easiest, so if that is what you prefer: I left the original serial OTA statements in so it is easy to convert back.
Download You can download the code here. A remark though: it is my ‘as is’ working code and while developing it, I may have put in some unnecessary things and comments and also my variables might not have been grouped logically. Normally i clean up code before publishing it….but that means I have to retest it again and i just did not have the time for that right now. I may in future. But as said, it is a working code. You do need to add your own data such as passwords and API’s though. I am in the process of rewriting the code from the ground up,based on experience gained with the using the ColdFrame for some time
————————————————————
Experience after a few weeks: In general the system functions well. The cooling/heating system was able to keep the temperature above 0 degrees celsius when the temperature outside dropped to minus 6 at night for a few hours. It had a bit more trouble when it was freezing minus 6 the entire day. Mind you though that a relatively small object is harder to keep warm because of the unfavorable volume vs. surface ratio.
The HTU21D was a bit of a disappointment. According to the datasheet, the humidity operating range is 0-100% and I have no reason to doubt that. However, after continued exposure to 100% RH, condensation within the sensor can play havoc on the results. RH values above 100% or below 0% are possible then. Robert Smith in fact mentions this in his excellent comparison of various humidity sensors. He though sees it as a possibility to “do your own calibration”. I think though that if the HTU21D jumps from say 114% to -3% within 10 secs, there is more at large than the calibration not being fine tuned. The question though is whether the condensation also influences the temperature readings. I do not think so, but I might add another DS18B20 just to make sure. I doubt if another humidity sensor would do better. Measuring near the upper limit of the range for a prolonged period seems to be problematic for all. The problem is the condensation in the sensor and no doubt other sensors, like the BME280 would suffer the same fate (eventhough the BME280 seems to use different type of sensor (resistive vs capacitive)). Also here, the relatively small size of the box may play a role as a relatively low amount of water will easily raise the potential humidity. Ventilation can help, but it is a 2 edged sword: ventilation will generally also drop the temperature, which will make the relative humidity go up. Also the 100% humidity would normally occur when the temperature would go down to 6-7 degrees or lower, i.e. usually at night, and that is the moment you do not want the temperature to drop any lower. Alternatives: BME280, Sensirion SHT4x or SHT71, HDC1080, AHT20 (datasheet mentions in fact a drift if subject to prolonged >80%RH). The Si7021 is practically identical to the HTU21D (in fact you may get the one if you order the other at less than reputable shops). They are only distinguishable by the serialnumber that supposedly is 60 for the HTU21D and 57 for the Si7021.
The BH1750 functioned well, it provided good information on what hours were most sunny on the spot the coldframe was standing.. It had one quirk though, occasionally (though very rarely), it would display the default value of 56412 Lux for a while and then it would be OK again ( did not find any fault in the wiring). A full reset would normally ‘cure’ it as well.
Obviously not a matter of just ‘finetuning’
The reporting through Telegram worked fine. Only 2 little ‘annoyances’ When the temperature ‘hoovers around 0 degrees or 25 degrees celcius (the lower and upper limit warning levels), I could get a couple of warnings in a row: I get a warning when the temparature dropped below zero, then when it would briefly rise above zero again the warning flag would be reset and subsequently if the temperature would drop below half a minute later I would get a warning again. It is basically the system doing what it was told to do. I could ‘solve’ that but not allowing a second warning within say 5 minutes, but I really did not find it important enough.
The leaf wetness sensor functioned ‘moderately’. Somehow the surface was not as appealing to condensation as the glass top (or the HTU21D sensor for that matter). As I still consider it important info, I will try to find another solution for it.
The combination of the webserver info that is basically à la minute (every 10 secs to be precise) and Thingspeak/MySQL/GoogleSheet that gives an historic overview worked very well.
Improvements Apart from the HTU21D and leaf wetness sensor I have to look at/reconsider, there are a few things I may add: currently experimenting with a TCS34725 sensor to see if I can monitor ‘green development’. I am considering if I should add a moveable setpoint for the cooling fan to set in, but it may not be important enough. Currently I have 4 ADC channels through the PCF8591. I use one of those channels for soil moisture. If I want to use the system in a larger coldframe, or a greenhouse, I may need to add some ADC’s. The current soil sensor I use (carbon rods) works fine, albeit that the thickness and bluntness of the rods can make it hard to stick it in a pot, so I may decide for a slightly leaner and sharper capacitive sensor, or just use metal nails. The latter though would require a transistor to switch off the current when not used (in order to minimize electrolyses), which takes an extra pin of the ESP8266. I may expand the function of the lid opener to automatically stop when the lid is open or the lid is closed. Also considering to add Tweets for notification next to Telegram
If you have a Thingspeak account, tweets can be sent either from your ESP via “thingtweet” or directly from Thingspeak.
I did add an emergency heating in the mean time. It is set to kick in if the temperature drops below 0 degrees, in spite of the underground heatsink providing heat. It is basically a relay that can switch on a heater. In my cold frame it is a resistor heater, if used in a greenhouse it could be a dedicated electrical heater. With seemingly only 2 projected frost days remaining till mid May, I will need to wait with proper testing.
Coldframe Aircon
When the Coldframe is getting too hot, the fan will pull hot air through the underground heatsink that has a rather steady temperature 0f 8-12 degrees Centigrade. When it is getting too cold, the same fan will pull cold air through the underground heatsink which then will warm the air. If that is not enough to prevent coldframe temperature to stay above zero, a heater above the fan wil kick in. I made the heater from 24 half-Watt resistors that are pushed to their limit, producing 12 Watt in heat. The main aim is to keep the temperature above freezing, not to heat the coldframe to say 20 degrees. As said, this is still experimental stage. It happened to me once that the 6 a.m. telegram message was not received. Checking my SQL database and Thingspeak showed the connection was briefly dropped around 6 a.m. As I do not do any error checking on the connection with Telegram (or MySQL for that matter), the program is not aware of the message not being received. If it is important to you, the result of bot.sendMessage(chat_id, bottext, "HTML"); is “0” if the transmission failed and “1” if it succeeded. You could do a check on that and resend if necessary. I am using an insecure connection with Telegram by specifying “setInsecure” in the setup. That’s easier and I am not sending top secret info. If you want the connection to be secure, you can do that by stating:
Experience after a few months When summer came, I had trouble loading the coldframe’s website on my network. As I still could load the the AP website when I was close to the coldframe, I was suspecting something was blocking the connection to the router. The most likely suspect was a row of potato filled containers between the coldframe and my house. When those grew into firm plants it isn’t unlikely to think these walls of moisture blocked the signal. Yet the connection to Thingspeak as well as my mySQL server seemed not to suffer.
Granted, I do not really use the coldframe in the summer, yet it might be wise to use a Wemos Pro (or ESP8266-7) with external antenna and place a router or WiFi extender in a room directly facing the garden (currently it connects to a router on the opposite side of the house, 1 floor up).
Also, when I will build another coldframe, I will try to find a way to remove the electronics from the box in an easier way, e.g.by making external plug connections with the ventilators, motors and some of the sensors. and I will start streamlining the code a bit more.
In my previous article, I made a skeleton program for a tutorial about the various ways of WiFi connection of an ESP8266, I ran into an odd problem that turned ourt to be related to the password. the code aimed to include both STA and AP connection and I set up the credentials like this.
#define WIFI_SSID "YOURSSID"
#define WIFI_PASS "YOURPASSWORD"
// Set AP credentials
#define AP_SSID "YOUR_DESIRED_AP_NAME"
#define AP_PASS "YOUR_DESIRED_AP_PW"
After uploading the acces point ofcourse identified itself as ‘YOUR_DESIRED_AP’ and I quickly set to correct my mistake of not filling out my credentials and I changed it to:
// Set AP credentials
#define AP_SSID "Pond"
#define AP_PASS "1234"
After uploading again, I was surprised to see that instead of the expected AP SSID ‘Pond’, I now saw ESP-859C2F, with the numbers being the last 3 bytes of my ESP’s MAC-number. Chosing that accespoint made me connect to the ESP without asking for a password. what the heck was going on? I knew the code was allright as on myinitial upload I had seen the SSID of the AP when it was still called “YOUR_DESIRED_AP_NAME”. To make a long story short: I found out the problem was with the password: apparently there is a problem with a password that is too short, or contains numbers. 1234–> no proper AP_SSID visible abcd–>works well abcd1234 –>no proper AP SSID visible abcd123–> works well. So initially I thought that perhaps the amount of letters needed to be bigger than the amount of numbers in th epassword. but ‘1234567’ worked fine….till I tried ‘123456’ which did not work, and then ‘1234567’ did not work either anymore and i had to go to ‘12345678’ to make it work. Note: I uploaded the sketches with the “Tools-erase all flash content set”. So I am still not entirely sure the exact problem, but I do know that numbers in the password apparently can cause problems with the AP
Driving a 4 channel relay board with ESP8266-01 through MQTT or Webserver
In a previous post I discussed making a WiFi relay inspired by the trouble Ralph Bacon had with a single relayboard from Aliexpress. Given the fact that the Sonoff SV is relatively cheap (don’t forget the shipping cost though), and the WiFi relay at AliExpress even cheaper, it is a nickle and dime question whether it is wise to DIY such a project yourself. However, if you already have an unused ESP8266-01 and a relay lying around, basically all you need are some DuPont cables to connect the two (as shown in my previous post).
It becomes more interesting to use all 4 pins of an idle lying ESP8266-01 and add a 4 channel relay board.
The ESP8266-01 however is a bit particular with the pins it has,as two of the 4 I/O pins are the pins that need to be pulled HIGH on startup whereas the remaining two pins are the UART. Those UART pins can be used as GPIO, but the problem is that they do not know that, they need to be told that they are no longer UART but in fact GPIO. We do that with a special pinMode command (more about that later).
The relayboard I had in mind is a bit peculiar. I discussed the full workings of this board in an earlier post.
For now however suffice to say the inputs need an active LOW to close the relays, while in rest, the inputs are HIGH.
That is in fact quite handy when using an ESP8266-01, as two of the 4 pins, GPIO0 and GPIO2, need to be pulled high on start-up, something this circuit actually does. It is necessary though to feed the board, including the optocoupler with 5Volt. The connections between the ESP8266-01 and the relay board are made as follows:
If you are using a different relay board that does not have it’s inputs pulled high in rest and you are using the ‘old’ ESP8266 module, then you need to add 10k pull-ups on GPIO 0 and GPIO2. Not necessary if you are using the ESP8266-01S.
A reminder: the relayboard needs 5Volt and the ESP-01 needs 3.3Volt. You could use an LDO like the AMS1117 3.3 to drop the 5Volt to 3.3 however,do not connect the two Vcc’s directly. Wait, let me emphasize that: DO NOT CONNECT THE Vcc OF THE ESP TO THE VCC OF THE RELAY BORD, JUST DONT!!!!
The full board
The ESP8266-01 adapter board
Finally, put it in a nice enclosure
4 Channel relay housing
Software
MQTT
For theMQTT program I followed the structure that is used by computourist with regard to the use of MQTT messages.
The idea behind that is that commands going from MQTT broker to the node are called ‘southbound’ (‘sb’), while the ones going to the broker (so usually the ‘ state’) are called ‘northbound’ (‘nb’). (with ‘southbound being a command and northbound being the status). The specific functions in the node are addressed as numbered devices.
This has advantages and disadvantages. The disadvantage being that you get codes like: “home/sb/node01/dev17” rather than something like: “home/cmd/wifirelay/relay1“.
Also the handling of the incoming MQTT is bound to a specified length, so altering it needs to be done with some consideration.
The advantages are that the handling of the code is easier and in fact extending the code with more functions is fairly easy.
What happens in fact is that once a subscribed MQTT code comes in, it is stripped to a its last two digits. These digits define the function it fulfills.
The Payload can be “ON”, “OFF”, “READ”, or a number, depending on the function chosen. The “READ” payload reads and returns the state of the specific function (‘device’) that is being read. Some ‘devices’ -as for instance the IP number or the MAC address, can only be read and not ‘set’.
The full list of devices is as follows:
00 uptime: read uptime in minutes
01 interval: read/set transmission interval for push messages
02 RSSI: read WiFi signal strength
03 version: read software version
05 ACK: read/set acknowledge message after a ‘set’ request
16 read/set relay1 output
17 read set relay2 output
18 read set relay3 output
19 read set relay4 output
92 error: tx only: device not supported
91 error: tx only: syntax error
99 wakeup: tx only: first message sent on node startup
As I mentioned earlier, the UART pins need to be told that they should behave like GPIO pins. That can be done with the statement:
pinMode(1,FUNCTION_3);
pinMode(3,FUNCTION_3);
However, this will not work when there are any hardware serial statements left (such as Serial.print, Serial.begin).
Also, the device will not boot if you happen to pull pin 1 (Tx) down.
So in order to control the relays from e.g. OpenHAB, this is what you add to your itemsfile:
Switch rel1 "WiFi relay 1 [%s]" (GF_Corridor) { mqtt="[mosquitto:home/sb/node01/dev17:command:*:default]" }
Switch rel2 "WiFi relay 2 [%s]" (GF_Corridor) { mqtt="[mosquitto:home/sb/node01/dev18:command:*:default]" }
Switch rel3 "WiFi relay 3 [%s]" (GF_Corridor) { mqtt="[mosquitto:home/sb/node01/dev19:command:*:default]" }
Switch rel4 "WiFi relay 4 [%s]" (GF_Corridor) { mqtt="[mosquitto:home/sb/node01/dev16:command:*:default]" }
this will render the following menu:
The program can be downloaded here. FYI, I trimmed down an existing, bigger program of mine. I left a bit of code here and there that might not be of immediate use in this project, but may come in handy if you want to use the code on a Wemos.
Wifi relay with ESP8266-01 and STC15F104 microprocessor
His frustration is understandable as that particular module is needlessly complicated. It seems the ESP8266-01 is mainly there to make the WiFi connection, while the relay is triggered by yet another microprocessor, the STC15F104. Communication between the two is via the serial port of the ESP8266, as if the designers thought, how can we make this in the dumbest way possible.
If you want to use this relay, this is how to do it:
Set the serialport to 9600 with : Serial.begin(9600);
To enable the Relay send the proper command bytes to serial port:
To disable the Relay send the following bytes to the serial port:
const byte reBufferOFF[] = {0xA0, 0x01, 0x00, 0xA1};
Serial.write(ReBufferON, sizeof(ReBufferOFF));
Apparently it will also work with AT commands.
The circuit looks like this
###
An even simpler board
The ‘simple’ relay board
In his follow up video # 110 Ralph describes another, simpler relay board (pictured), that also frustrated him as the manufacturer apparently had not included the necessary pull-up resistors on the Chip Enable and on GPIO0 and GPIO2. (Edit: this turned out not to be entirely true as the board comes with an ESP8266-01S that has the necessary pullups on board)
Both videos came in my focus again, when i discussed the ‘simpler’ board with a diy mate and frequent commenter. It is very cheap to buy and once you add the resistors (to make it start up correctly) factually you have a Sonoff SV.
Ofcourse the Sonoff SV is less than 5 Euro (plus shipping), as opposed to the ‘brandless’ relay board only costing some 2.60 euro, so you might as well get the real thing, but it opens some interesting perspectives, especially as I had most of the stuff laying around namely an ESP8266-01 a relay module and a 3.3Volt power module, all fairly cheap. Just a couple of DuPont cables to connect the three, and it should be fine. I know it is all nickles & dimes stuff but lets do a quick calculation.
Total 2.08 euro as opposed to 2.62 euro (in a nicer package), so not really cost effective to ‘DIY’ but if you have the stuff laying around, better to use it than for it gather dust. It also allows you to choose another pin than GPIO2 to drive the relay.
Ralph also offers a program to replace the existing firmware in the ESP8266, as well as a phone app (all found in the description of his video). Ofcourse it is also possible to replace the firmware with MQTT responsive firmware. For that you could e.g. use my Sonoff Touch program, albeit that in line 17, you have to change “TouchLED=12;” to “TouchLED=2;”
But why stop there? the ESP8266-01 has 4 I/O pins, if we ad a small 220->5V power module and a 4 channel relay board, we could make a sonoff 4ch. These cost about 22 Euro. So that would be more rewarding to build. That however will be for part 2.
Connecting your ESP to Thingspeak with a program written in your Arduino IDE is quite easy and those programs come a dime a dozen.
However, when I went to look for a program that did the same in LUA I came a cross a number of programs that simply didn’t work.
So I had to give it a go myself.
The below program is more or less a skeleton for thingspeak connection. It reads two values and posts those on Thingspeak. As it was just for testing I didnt want to add a lot of sensors, so th eonly two values read are the Internal reference voltage and the analog value from ADC0.
To make the program easily adaptable, I readthe sensors in a seperate function.
If you want to add a variable resistor to test the ADC, mind you that it can only take 1 Volt input so you need a voltage divider that limits it to 1 volt instead of 3.3 Volt.
When using the program dont forget your network credentials and Thingspeak API Key
WRITEKEY="T3I6T9YF67JE527" -- set your thingspeak.com key
wifi.setmode(wifi.STATION)
wifi.sta.config("YourSSID","YourPW")
wifi.sta.connect()
tmr.delay(1000000)
volt=0 --Internal reference voltage
ana=0 -- analog port A0 Mind you, this one has a 1 Volt max
--read sensor
function ReadSensor()
volt = node.readvdd33()
ana=adc.read(0) -- 1V=1023
print("Internal Ref.Voltage: " ..(volt/1000).."."..(volt%1000).." Volt")
print("Analoog: "..ana)
end
-- send to https://api.thingspeak.com
function sendTS()
conn = nil
conn = net.createConnection(net.TCP, 0)
conn:on("receive", function(conn, payload)success = true print(payload)end)
conn:on("connection",
function(conn, payload)
print("Connected")
conn:send('GET /update?key='..WRITEKEY..'&field1='..(volt/1000)..'.'..(volt%1000)..'&field2='..ana..'HTTP/1.1\r\n\
Host: api.thingspeak.com\r\nAccept: */*\r\nUser-Agent: Mozilla/4.0 (compatible; esp8266 Lua; Windows NT 5.1)\r\n\r\n')end)
conn:on("disconnection", function(conn, payload) print('Disconnected') end)
conn:connect(80,'184.106.153.149')
end
ReadSensor()
sendTS()
tmr.alarm(1,6000,1,function()ReadSensor()sendTS()end)
Now I haven’t been completely honest here, as it isnt really good practice to read the internal Voltage and the A0 port as it reads either the one or the other.
When using the A0 to read internal voltage in the Arduino IDE rather than in LUA ADC_MODE(ADC_VCC); in combination with ESP.getVcc() is now the correct way to do it. Using readvdd33 causes problems
Connecting your ESP to Thingspeak with a program written in your Arduino IDE is quite easy and those programs come a dime a dozen.
Arduino, ESP8266, ESP32 & Raspberry Pi stuff 