power_smart_meter_s0

Reading Smart-meter S0-Interface using GPIO and inserting it to MySQL to Grafana

Requirements (Install Guide for each point down Below)

1. Node.js
2. Grafana Server
3. MySQL Database

---

Install Requirements (Using Raspberry Raspbian)

1. npm / Node.js installation
   
   1. sudo apt update
      
   2. sudo apt upgrade
      
   3. sudo apt install npm
2. Grafana installation
   
   1. wget -q -O - https://packages.grafana.com/gpg.key | sudo apt-key add -
      
   2. echo "deb https://packages.grafana.com/oss/deb stable main" | sudo tee -a /etc/apt/sources.list.d/grafana.list
      
   3. sudo apt update
      
   4. sudo apt install grafana
      
   5. sudo systemctl enable grafana-server
      
   6. sudo systemctl start grafana-server
      
3. mysql database installation
   1. sudo apt install mariadb-server
   2. sudo mysql_secure_installation

---

Set-up Script - MySQL Database - Grafana Panels

1. Set-up script
   1. Download Repo
      git clone https://github.com/ledahosn/power_smart_meter_s0
   2. cd power_smart_meter_s0
   3. Install dependencies
      npm install
   4. Create .env file from example
      cp .env.example .env
   5. Edit .env with your Credentials
   6. start script manually
      1. node main.js [CURRENT_WATTS_ON_SMARTMETER]
   7. using npm package forever-service to create a service
      1. npm install -g forever
      2. npm install -g forever-service
      3. sudo forever-service install power_smart_meter_s0 --script main.js
2. mysql database configuration
   1. Create DB
      CREATE DATABASE smart_home;
   2. Use DB
      USE smart_home;
   3. Create the table
      CREATE TABLE power_usage( id int(11) NOT NULL AUTO_INCREMENT, ms_between_pulse int(11) DEFAULT NULL, current_power_consumption float DEFAULT NULL, script_timestamp datetime DEFAULT NULL, insert_timestamp datetime DEFAULT current_timestamp(), total_pulse_count int(11) DEFAULT NULL, PRIMARY KEY (id));
   4. Create user for grafana
      CREATE USER 'grafana'@'localhost' IDENTIFIED BY 'PASS';
   5. Create user for node-script
      CREATE USER 'nodescript'@'localhost' IDENTIFIED BY 'PASS';
   6. Grant read-only to grafana user
      GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON smart_home.* TO 'grafana'@'localhost';
   7. Grant all to node-script
      GRANT ALL PRIVILEGES ON smart_home.* TO 'nodescript'@'localhost';
3. Grafana Panel SQL
   1. Current Power Usage
      1. SELECT date_add(insert_timestamp, interval -2 hour) as 'time', current_power_consumption as 'Current Power Usage' FROM stromverbrauch ORDER BY insert_timestamp
   2. Total Power Usage & Cost
      1. SELECT date_add(insert_timestamp, interval -2 hour) as 'time', total_pulse_count as 'Total Power Usage', total_pulse_count/1000*0.2 as 'Total Costs' FROM stromverbrauch ORDER BY insert_timestamp
   3. Daily Power Usage & Cost
      1. SELECT date_add(STR_TO_DATE(CONCAT(SUBSTR(insert_timestamp, 1, 10), ' 00:00:00'), '%Y-%m-%d %T'), interval -2 hour) as 'time', MAX(total_pulse_count)-MIN(total_pulse_count) as 'Daily Power Usage', (MAX(total_pulse_count)-MIN(total_pulse_count))/1000*0.2 as 'Daily Costs' FROM stromverbrauch GROUP BY time