diff options
| -rw-r--r-- | config/logrotate/power-logger | 12 | ||||
| -rw-r--r-- | config/syslog/51power-logger.conf | 1 | ||||
| -rw-r--r-- | config/systemd/power-logger.service | 25 | ||||
| -rwxr-xr-x | power-logger | 119 | ||||
| -rwxr-xr-x | power-meter | 67 | ||||
| -rwxr-xr-x | scripts/install.sh | 9 |
6 files changed, 160 insertions, 73 deletions
diff --git a/config/logrotate/power-logger b/config/logrotate/power-logger new file mode 100644 index 0000000..089db74 --- /dev/null +++ b/config/logrotate/power-logger @@ -0,0 +1,12 @@ +/var/log/power_logger/logger.log { + rotate 7 + daily + compress + size 100k + nocreate + missingok + postrotate + kill -HUP `cat /run/power_logger/logger.pid` + rm -f /run/power_logger/receiver.pid + endscript +} diff --git a/config/syslog/51power-logger.conf b/config/syslog/51power-logger.conf new file mode 100644 index 0000000..c734814 --- /dev/null +++ b/config/syslog/51power-logger.conf @@ -0,0 +1 @@ +:programname, isequal, "power-logger" /var/log/power_logger/logger.log diff --git a/config/systemd/power-logger.service b/config/systemd/power-logger.service new file mode 100644 index 0000000..8774f96 --- /dev/null +++ b/config/systemd/power-logger.service @@ -0,0 +1,25 @@ +[Unit] +Description=power-logger +After=network.target +StartLimitIntervalSec=0 + +[Service] +Type=simple +Restart=always +RestartSec=1 +User=power-logger +Group=power-logger +Environment=GEM_HOME=/usr/local/lib/ruby/gems +StandardOutput=syslog +StandardError=syslog +SyslogIdentifier=power-logger +PIDFile=/run/power_logger/logger.pid +PermissionsStartOnly=true +ExecStartPre=mkdir -p /run/power_logger /var/log/power_logger /usr/local/share/power_logger +ExecStartPre=rm -f /run/power_logger/receiver.pid +ExecStartPre=chown -R power-logger:power-logger /run/power_logger /var/log/power_logger /usr/local/share/power_logger +ExecStopPost=rm -f /run/power_logger/logger.pid +ExecStart=/usr/local/src/power_logger/power-logger + +[Install] +WantedBy=multi-user.target diff --git a/power-logger b/power-logger index 827451f..dd94bb3 100755 --- a/power-logger +++ b/power-logger @@ -1,52 +1,59 @@ #!/usr/bin/env ruby # FIXME -$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/json-2.3.1/lib/" -$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/mqtt-0.5.0/lib/" -$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/optimist-3.0.1/lib/" -$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/sqlite3-1.4.2/lib/" -$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/zipf-1.2.6/lib/" +$LOAD_PATH << '/usr/local/lib/ruby/gems/mqtt-0.5.0/lib/' +$LOAD_PATH << '/usr/local/lib/ruby/gems/optimist-3.0.1/lib/' + +$stdout.sync = true +$stderr.sync = true require 'json' require 'mqtt' require 'optimist' require 'sqlite3' require 'time' -require 'zipf' + +$handles = { + 'baker' => 'fridge', + 'batum' => 'microwave', + 'bembry' => 'modem+printer', + 'crowder' => 'desk-2', + 'davis' => 'hifi', + 'doncic' => 'desk-1', + 'fournier' => 'air_washer', + 'gibson' => 'dishwasher', + 'gooden' => 'couch', + 'grant' => 'washing_machine', + 'kukoc' => 'rack', + 'nogueira' => 'kitchen_tech', + 'world-peace' => 'kettle+toaster' +} def shutdown - STDERR.write "Shutting down ...\n" + STDERR.write 'Shutting down ...' $db.close - $logfile.close - STDERR.write "Done!\n" + STDERR.write " done!\n" exit end -Signal.trap("INT") { shutdown } -Signal.trap("TERM") { shutdown } +Signal.trap('INT') { shutdown } +Signal.trap('TERM') { shutdown } -def db_open_or_new filename +def db_open_or_create filename if File.exists? filename $db = SQLite3::Database.open filename else $db = SQLite3::Database.new filename $db.execute <<-SQL - create table power_log( + create table power( id INTEGER PRIMARY KEY, timestamp DATETIME, - device_name TEXT, - device_location TEXT, + device TEXT, + handle TEXT, total FLOAT, total_start_time DATETIME ); SQL - $db.execute <<-SQL - create table power_meter( - id INTEGER PRIMARY KEY, - timestamp DATETIME, - total FLOAT - ); - SQL end end @@ -57,45 +64,55 @@ def db_execute sql, data break rescue SQLite3::BusyException STDERR.write "DB busy, skipping data point '#{d.to_s}'\n" - sleep 1 + sleep rand end } end +def db_insert device, handle, data + timestamp = Time.parse(data['Time']).utc.to_i + total_start_time = Time.parse(data['ENERGY']['TotalStartTime']).utc.to_i + db_execute \ + "INSERT INTO power(timestamp, device, handle, total, total_start_time) VALUES(?,?,?,?,?)", \ + [timestamp, device, handle, data['ENERGY']['Total'], total_start_time] +end + def parse_topic topic - parts = topic.split "/" - else - _, device_name, _, device_location_1, device_location_2, _ = parts - end - device_location = [device_location_1, device_location_2] - return device_name, device_location + parts = topic.split '/' + _, device, _ = parts + handle = $handles[device] + + return device, handle end def parse_message message return JSON.parse message end -def insert_power device_name, device_location, data - if data.has_key? "ENERGY" - timestamp = Time.parse(data["Time"]).utc.to_i - total_start_time = Time.parse(data["ENERGY"]["TotalStartTime"]).utc.to_i - db_execute \ - "INSERT INTO power(timestamp, device_name, device_location_primary, device_location_secondary, total, total_start_time) VALUES(?,?,?,?,?,?)", \ - [timestamp, device_name, device_location[0], device_location[1], data["ENERGY"]["Total"], total_start_time] +def check_and_maybe_create_pidfile filename + if File.exists? filename + File.open filename, 'r' do |f| + STDERR.write "Possibly already running as PID #{f.read}, exiting" + end + else + File.open filename, 'w' do |f| + puts "Running as PID #{Process.pid}" + f.write "#{Process.pid}\n" + end end end def main options = Optimist::options do - opt :host, "MQTT hostname", :type => :string, :default => 'localhost', :short => '-h' - opt :port, "MQTT port", :type => :int, :default => 1883, :short => '-p' - opt :db, "SQLite3 database file", :type => :string, :required => true, :short => '-d' - opt :log, "Logfile", :type => :string, :required => true, :short => '-l' + opt :host, 'MQTT hostname', :type => :string, :default => 'localhost', :short => '-h' + opt :port, 'MQTT port', :type => :int, :default => 1883, :short => '-p' + opt :db, 'SQLite3 database file', :type => :string, :default => '/usr/local/share/power_logger/power.db', :short => '-d' + opt :pidfile, 'PID file', :type => :string, :default => '/run/power_logger/logger.pid', :short => '-P' end - db_open_or_new options[:db] + check_and_maybe_create_pidfile options[:pidfile] - $logfile = WriteFile.new options[:log] + db_open_or_create options[:db] $mqtt_client = MQTT::Client.connect( :host => options[:host], @@ -105,13 +122,19 @@ def main $mqtt_client.get do |topic,message| begin - puts "#{topic}\t#{message} - #device_name, device_location = parse_topic topic - #data = parse_message message - #insert_power device_name, device_location, data - #$logfile.write "#{topic}\t#{message}\n" + logged = false + if topic.end_with? '/SENSOR' + data = parse_message message + if data.keys.include? 'ENERGY' + puts "[logged] topic=#{topic} message=#{message}" + device, handle = parse_topic topic + db_insert device, handle, data + logged = true + end + end + if not logged then STDERR.write "[ignored] topic=#{topic} message=#{message}\n" end rescue => exception - $logfile.write "[Ignoring]\t#{topic}\t#{message}\n" + STDERR.write "Exception: #{exception}\n" end end diff --git a/power-meter b/power-meter index 4e05af5..013da25 100755 --- a/power-meter +++ b/power-meter @@ -1,37 +1,54 @@ #!/usr/bin/env ruby +# FIXME +$LOAD_PATH << '/usr/local/lib/ruby/2.5.0/gems/mqtt-0.5.0/lib/' +$LOAD_PATH << '/usr/local/lib/ruby/2.5.0/gems/optimist-3.0.1/lib/' + +require 'optimist' require 'sqlite3' def main - db = SQLite3::Database.new ARGV[0] - devices = db.execute "select distinct device_location_primary, device_location_secondary FROM power" - puts devices.to_s - exit - devices.reject! { |i| not ["office", "living_room", "guest_restroom", "kitchen", "bedroom_2"].include? i[0] } - start_date = Date.new(2021,01,01).to_time.to_i - end_date = Date.new(2021,01,30).to_time.to_i + options = Optimist::options do + opt :db, "SQLite3 database file", :type => :string, :required => true, :short => '-d' + opt :from, "Calculate power consumption from date/time", :type => :string, :required => false, :short => '-f' + opt :to, "Calculate power consumption to date/time", :type => :string, :required => false, :short => '-t' + end + + db = SQLite3::Database.new options[:db] + + today = Date.today + if ! options[:from] + from = Date.new(today.year, today.month, 1).to_time + else + from = Time.parse(options[:from]) + end + if ! options[:to] + to = Time.parse "#{today.year}-#{today.month}-#{today.day}T23:59:59" + else + to = Time.parse(options[:to]) + end + + handles = db.execute "SELECT DISTINCT handle FROM power" + handles.map! { |i| i[0] }.sort! + max_handle_size = handles.map { |i| i.size }.max + totals = {} totals.default = 0.0 - devices.sort_by{|i| i[0] }.each { |device| - puts "select TOTAL, TIMESTAMP from power WHERE TIMESTAMP >= #{start_date} and TIMESTAMP <= #{end_date} AND DEVICE_LOCATION_PRIMARY = '#{device[0]}' AND DEVICE_LOCATION_SECONDARY = '#{device[1]}' ORDER BY TIMESTAMP ASC LIMIT 1" - exit - first = db.execute "select TOTAL, TIMESTAMP from power WHERE TIMESTAMP >= #{start_date} and TIMESTAMP <= #{end_date} AND DEVICE_LOCATION_PRIMARY = '#{device[0]}' AND DEVICE_LOCATION_SECONDARY = '#{device[1]}' ORDER BY TIMESTAMP ASC LIMIT 1" - last = db.execute "select TOTAL, TIMESTAMP from power WHERE TIMESTAMP >= #{start_date} and TIMESTAMP <= #{end_date} AND DEVICE_LOCATION_PRIMARY = '#{device[0]}' AND DEVICE_LOCATION_SECONDARY = '#{device[1]}' ORDER BY TIMESTAMP DESC LIMIT 1" - if first.size > 0 and last.size > 0 - #puts "#{Time.at(first[0][1])} --- #{Time.at(last[0][1])}" - #puts "#{first[0][0]} ::: #{last[0][0]}" - kwh = last[0][0] - first[0][0] - #puts "#{device.join '/'}: #{kwh.round 0} kW/h" - #puts - totals[device[0]] += kwh + + handles.each { |handle| + first = db.execute "SELECT total FROM power WHERE timestamp >= #{from.to_i} AND timestamp <= #{to.to_i} AND handle='#{handle}' ORDER BY timestamp ASC LIMIT 1" + last = db.execute "SELECT total FROM power WHERE timestamp >= #{from.to_i} AND timestamp <= #{to.to_i} AND handle='#{handle}' ORDER BY timestamp DESC LIMIT 1" + if first.size == 1 and last.size == 1 + consumption = last.first.first - first.first.first + totals[handle] += consumption end } - puts "TOTAL for #{start_date} - #{end_date}: #{totals.values.inject(:+).round 0} kW/h" - puts "\nBy Room" - puts "-------" - totals.each_key { |k| - puts " #{k}: #{totals[k].round 0} kW/h" - } + + puts "Power consumption from #{from} to #{to} = #{totals.values.inject(:+).round 1} kW/h" + puts "\nBy handle" + puts "---------" + totals.each_key { |handle| puts " - #{handle.ljust(max_handle_size)} = #{totals[handle].round(2).to_s.ljust(4)} kW/h" } + db.close end diff --git a/scripts/install.sh b/scripts/install.sh new file mode 100755 index 0000000..1f28629 --- /dev/null +++ b/scripts/install.sh @@ -0,0 +1,9 @@ +#!/usr/bin/env zsh + +sudo useradd -M -s /bin/bash power-logger +sudo mkdir -p /usr/local/share/power_logger +sudo mkdir -p /var/log/power_logger +sudo mkdir -p /run/power_logger +sudo chown -R power-logger:power-logger /usr/local/share/power_logger /var/log/power_logger /run/power_logger +sudo chmod 775 /usr/local/share/power_logger /var/log/power_logger /run/power_logger +gem install --install-dir=/usr/local/lib/ruby/2.5.0/ --bindir=/usr/local/bin/ process sqlite3 |