IOT Geiger Counter (InfluxDB)

Of course every household needs a Geiger Counter and I bought this kit to do all the fancy 400/500V voltage work along with an SBM-20 Geiger-Muller Tube on eBay for the actual radiation detecting. Typically it seems people hook this up to a tablet etc. and run an app but my plan was to log to InfluxDB. It can also operate stand-alone which is why I added a handy display (Nokia 5110).

Finished IOT Geiger Counter

Notes:
This type of detector is designed to detect Beta & Gamma rays. (it cannot detect Alpha rays but this sensor can be added easily if wanted.

What it does:
– Listens & counts pulses for 60 seconds
– After 60 seconds writes this value to InfluxDB
– Updates display with current metrics. (last 60 second reading, average reading, max reading, estimated dosage & the current IP address)

Connecting it up:

LCD PinESP8266 Labelled PinESP8266 GPIO PinGeiger Detector Board
1 – RSTD0GPIO 16
2 – CED1GPIO 5
3 – DCD2GPIO 4
4 – DIND3GPIO 0
5 – CLKD4GPIO 2
6 – Vcc3.3V3.3V
7 – Backlight3.3V (on)3.3V
8 – GroundGroundGroundGround
D5GPIO 14Int (interrupt)
VU/ 5VVU / 5V5V

Code:
See latest code at my GitHub (or below):
– Requires Adafruit libraries. Link 1, Link 2
– Requires Running Average Library
– Requires InfluxDb library

#include <ESP8266WiFi.h>
#include <InfluxDb.h>
#include <SPI.h>
#include <Adafruit_GFX.h>
#include <Adafruit_PCD8544.h>
#include "RunningAverage.h"

#define INFLUXDB_HOST "192.168.1.XXX"
#define WIFI_SSID "XXXXXXXXXXXX"
#define WIFI_PASS "XXXXXXXXXXXX"
#define DATABASE "XXXXXXXXXXXX"
#define MEASUREMENT "XXXXXXXXXXXX"
#define DEVICE "XXXXXXXXXXXX"
#define ID "Geiger_Counter"
#define LOG_PERIOD 60000

Influxdb influx(INFLUXDB_HOST);
Adafruit_PCD8544 display = Adafruit_PCD8544(2, 0, 4, 5, 16); //LCD 1.5 Inch (Nokia 5110)84×48 (x,y) pixels
RunningAverage raMinute(60);

//################
int debug = 1; //#
//################

int loopCount = 0;
int cpm = 0;
int cpm_max = 0;
int cpm_1hr_avg = 0;
int cpm_ravg = 0;
int counts = 0;
int cal_factor = 1;
int wifiStatus;

unsigned long currentMillis;
unsigned long previousMillis; //variable for time measurement

void setup(){                                               
  Serial.begin(9600);      // start serial monitor
  delay(1000);
  Serial.println("");
  Serial.println("");
  Serial.println("Setup Routine of ESP8266 Geiger Counter");

  display.begin();
  display.setContrast(55);
  display.display();        // show adafruid splashscreen
  delay(2000);
  display.clearDisplay();   // clears the screen and buffer

  pinMode(LED_BUILTIN, OUTPUT); //D4
  digitalWrite(LED_BUILTIN, HIGH); //Turns it off

  pinMode(14, INPUT_PULLUP);                  // set pin INT0 input for capturing GM Tube events / GPIO5 = D1
  attachInterrupt(14, tube_pulse, FALLING); //defines interrupts

  raMinute.clear();

  influx.setDb(DATABASE);

  display.setTextSize(1);
  display.setTextColor(BLACK);
  display.setCursor(0,0); display.setTextSize(1);display.print("Up Hrs: ");display.setCursor(48,0);display.print("0");
  display.setCursor(0,9); display.setTextSize(1);display.print("CPMi 1m:");
  display.setCursor(0,17);display.setTextSize(1);display.print("CPM avg:");
  display.setCursor(0,25);display.setTextSize(1);display.print("CPM Max:");
  display.setCursor(0,33);display.setTextSize(1);display.print("uSv/hr: ");
  display.setCursor(0,41);display.setTextSize(1);display.print("Not Connected");
  display.display();

    wifiStatus = WiFi.status();
    if (wifiStatus != WL_CONNECTED) {   
      new_connection();
    }
    else {
        display.fillRect(0,41,84,48, WHITE);
        display.setCursor(0,41);
        display.print(WiFi.localIP());
        display.display();
    }
    

  if (debug == 1) {Serial.println("Setup Complete.");}
}

void loop(){                                              
  currentMillis = millis();
  if(currentMillis - previousMillis > LOG_PERIOD){
    cpm = counts * cal_factor;                        

    raMinute.addValue(cpm);
    cpm_ravg = raMinute.getAverage();

    if (cpm > cpm_max){
      cpm_max = cpm;
    }
    
    Serial.print("CPM: ");                         
    Serial.println(cpm);                          

    display.fillRect(48,0,40,40, WHITE);
    display.display();
    display.setCursor(48,0);display.print(loopCount*.0166);
    display.setCursor(48,9);display.print(cpm);
    display.setCursor(48,17);display.print(cpm_ravg);
    display.setCursor(48,25);display.print(cpm_max);
    display.setCursor(48,33);display.print(cpm_ravg*0.0057);
    display.display();

    Serial.println("Attempting to write to DB");   
    counts = 0;

    InfluxData row(MEASUREMENT);
    row.addTag("Device", DEVICE);
    row.addTag("ID", ID);
    row.addValue("CPM", cpm);  
    row.addValue("LoopCount", loopCount);
    row.addValue("RandomValue", random(0, 100));
  
    wifiStatus = WiFi.status();
    while ( wifiStatus != WL_CONNECTED )
        {
          new_connection();
        }
  
    influx.write(row);
    if (debug == 1) {Serial.println("Wrote Data.");}
  
    //WiFi.mode(WIFI_OFF); // Probably turn off Wifi if want to save battery
    //WiFi.forceSleepBegin();
    //delay( 1 );
  
    status_blink();
    previousMillis = currentMillis;
    loopCount++;
  }
}

ICACHE_RAM_ATTR        //Needed to fix ISR not in IRAM boot error
void tube_pulse(){     //procedure for capturing events from interrupt
  counts++;
}

void new_connection() {
  
    wifiStatus = WiFi.status();
    
    if (wifiStatus != WL_CONNECTED) {   
       
        WiFi.mode(WIFI_STA);
        WiFi.begin(WIFI_SSID, WIFI_PASS);
        int loops = 0;
        int retries = 0;
        display.fillRect(0,41,84,48, WHITE);
        display.setCursor(0,41);
        display.print("Not Connected");
        display.display();
       
        while (wifiStatus != WL_CONNECTED)
        {
          retries++;
          if( retries == 300 )
          {
              if (debug == 1) {Serial.println( "No connection after 300 steps, powercycling the WiFi radio. I have seen this work when the connection is unstable" );}
              WiFi.disconnect();
              delay( 10 );
              WiFi.forceSleepBegin();
              delay( 10 );
              WiFi.forceSleepWake();
              delay( 10 );
              WiFi.begin( WIFI_SSID, WIFI_PASS );
          }
          if ( retries == 600 )
          {
              if (debug == 1) {Serial.println( "No connection after 600 steps. WiFi connection failed, disabled WiFi and waiting for a minute" );}
              WiFi.disconnect( true );
              delay( 1 );
              WiFi.mode( WIFI_OFF );
              WiFi.forceSleepBegin();
              delay( 10 );
              retries = 0;
              
              if( loops == 3 )
              {
                  if (debug == 1) {Serial.println( "That was 3 loops, still no connection so let's go to deep sleep for 2 minutes" );}
                  Serial.flush();
                  ESP.deepSleep( 120000000, WAKE_RF_DISABLED );
              }     
          }
          delay(50);
          wifiStatus = WiFi.status();
        }
        
        wifiStatus = WiFi.status();
        Serial.print("WiFi connected, IP address: ");Serial.println(WiFi.localIP());
        display.fillRect(0,41,84,48, WHITE);
        display.setCursor(0,41);
        display.print(WiFi.localIP());
        display.display();
    }
}

void status_blink() {
  digitalWrite(LED_BUILTIN, LOW);   // Turn the LED on (Note that LOW is the voltage level   
  delay(100);
  digitalWrite(LED_BUILTIN, HIGH);   // Turn the LED on (Note that LOW is the voltage level
  delay(100);
  digitalWrite(LED_BUILTIN, LOW);   // Turn the LED on (Note that LOW is the voltage level   
  delay(100);
  digitalWrite(LED_BUILTIN, HIGH);   // Turn the LED on (Note that LOW is the voltage level
}

ToDo:
– Comment code
– Add in check at start of code to see if tube functioning.
– I would like to update the running average to 60min average but not enough time to currently do these 10 lines of code.
– Add in control (on/off ) for the LCD backlight, buzzer & Wifi for battery consumption.
– Perhaps would be nice to log to SD card also, not sure if I still have enough I/O for that.
– The ‘case’ is a very rough and not worthy of sharing, a nicer more bespoke would be ideal.
– Add radiation symbol on splash screen.

Resources I used:
https://mightyohm.com/blog/2014/11/a-spotters-guide-to-the-sbm-20-geiger-counter-tube/

That’s it!

ESP8266 & Stepper Motors

Nothing outrageously complicated but when I used the default arduino stepper library to control my 28BYJ-48 stepper motors on my ESP8266 they only turned one direction. With modifications to the library it was possible to get one stepper motor working correctly but the ESP crashed if I tried to control 2 stepper motors, solution sketch below:

What did work:
– Sketch below. (Note: No fancy acceleration/deceleration but I didnt need it)

int pos_rot = 0;  
int pos_elev = 0; 

int rot_counter = 0;
int elev_counter = 0;

int next_rot = -1;
int next_elev = 1;

int step_delay = 10; //Delay between steps in ms

const int motor_pin_1 = 16; // ESP D0
const int motor_pin_2 = 5;  // ESP D1
const int motor_pin_3 = 4;  // ESP D2
const int motor_pin_4 = 0;  // ESP D3
const int motor_pin_5 = 2;  // ESP D4
const int motor_pin_6 = 14; // ESP D5
const int motor_pin_7 = 12; // ESP D6
const int motor_pin_8 = 13; // ESP D7

void setup() {
  pinMode(motor_pin_1, OUTPUT); // Blue
  pinMode(motor_pin_2, OUTPUT); // Pink
  pinMode(motor_pin_3, OUTPUT); // Yellow
  pinMode(motor_pin_4, OUTPUT); // Orange
  pinMode(motor_pin_5, OUTPUT);
  pinMode(motor_pin_6, OUTPUT);
  pinMode(motor_pin_7, OUTPUT);
  pinMode(motor_pin_8, OUTPUT);
  
  // Begin Serial communication at a baud rate of 9600:
  Serial.begin(115200);
  delay(100);Serial.println("Leaving Setup");delay(100);
}

void loop() {
    //test_sweep();
    rot(2038);delay(2000);rot(-2038);   // 2038 steps clockwise followed by the same counterclockwise
    elev(1019);delay(2000);elev(-1019); // 1019 steps clockwise followed by the same counterclockwise
}

void test_sweep(){
      Serial.print("Rotating CCW, pos_rot = ");Serial.println(pos_rot);

    while (pos_rot < 2038) {
    rot(1);
    pos_rot = pos_rot + 1;
 
        if ((pos_rot < 1019) && ((pos_rot % 2) == 0)) {
          elev(2);          
        }
        else if ((pos_rot > 1019) && ((pos_rot % 2) == 0)) {
          elev(-2);
        }
  }
    while (pos_rot > 0) {
    rot(-1);
    pos_rot = pos_rot - 1;
 
        if ((pos_rot > 1019) && ((pos_rot % 2) == 0)) {
          elev(2);
        }
        else if ((pos_rot < 1019) && ((pos_rot % 2) == 0)) {
          elev(-2);
        }
  }
}

void elev(int num_steps) {
    if (num_steps > 0) {
        while (elev_counter < num_steps) {        
          elev_step(next_elev);
          next_elev = next_elev +1;
          if (next_elev > 7) { next_elev = 0;}
          elev_counter = elev_counter +1;
        }
    }
    else {
        num_steps = abs(num_steps);
        while (elev_counter < num_steps) {        
          elev_step(next_elev);
          next_elev = next_elev - 1;
          if (next_elev < 0) { next_elev = 7;}
          elev_counter = elev_counter +1;
        }    
    }
    elev_counter = 0;
}

void rot(int num_steps) {
    if (num_steps > 0) {
    
        while (rot_counter < num_steps) {        
          rot_step(next_rot);
          next_rot = next_rot +1;
          if (next_rot > 7) { next_rot = 0;}
          rot_counter = rot_counter +1;
        }
    }
    else {
        num_steps = abs(num_steps);
        while (rot_counter < num_steps) {        
          rot_step(next_rot);
          next_rot = next_rot - 1;
          if (next_rot < 0) { next_rot = 7;}
          rot_counter = rot_counter +1;
        }    
    }

    rot_counter = 0;  
}

void rot_step(int go_step) {
  switch (go_step) {
      case 0:  // 0001
        digitalWrite(motor_pin_1, 0);  // Blue
        digitalWrite(motor_pin_2, 0);  // Pink
        digitalWrite(motor_pin_3, 0);  // Yellow
        digitalWrite(motor_pin_4, 1);  // Orange
      break;
      case 1:  // 0011
        digitalWrite(motor_pin_1, 0);
        digitalWrite(motor_pin_2, 0);
        digitalWrite(motor_pin_3, 1);
        digitalWrite(motor_pin_4, 1);
      break;
      case 2:  //0010
        digitalWrite(motor_pin_1, 0);
        digitalWrite(motor_pin_2, 0);
        digitalWrite(motor_pin_3, 1);
        digitalWrite(motor_pin_4, 0);
      break;
      case 3:  //0110
        digitalWrite(motor_pin_1, 0);
        digitalWrite(motor_pin_2, 1);
        digitalWrite(motor_pin_3, 1);
        digitalWrite(motor_pin_4, 0);
      break;
      case 4:  // 0100
        digitalWrite(motor_pin_1, 0);
        digitalWrite(motor_pin_2, 1);
        digitalWrite(motor_pin_3, 0);
        digitalWrite(motor_pin_4, 0);
      break;
      case 5:  // 1100
        digitalWrite(motor_pin_1, 1);
        digitalWrite(motor_pin_2, 1);
        digitalWrite(motor_pin_3, 0);
        digitalWrite(motor_pin_4, 0);
      break;
      case 6:  //1000
        digitalWrite(motor_pin_1, 1);
        digitalWrite(motor_pin_2, 0);
        digitalWrite(motor_pin_3, 0);
        digitalWrite(motor_pin_4, 0);
      break;
      case 7:  //1001
        digitalWrite(motor_pin_1, 1);
        digitalWrite(motor_pin_2, 0);
        digitalWrite(motor_pin_3, 0);
        digitalWrite(motor_pin_4, 1);
      break;
    }
    delay(step_delay);
}

void elev_step(int go_step) {
  //Serial.println(go_step);
  switch (go_step) {
      case 0:  // 0001
        digitalWrite(motor_pin_5, LOW);
        digitalWrite(motor_pin_6, LOW);
        digitalWrite(motor_pin_7, LOW);
        digitalWrite(motor_pin_8, HIGH);
      break;
      case 1:  // 0011
        digitalWrite(motor_pin_5, LOW);
        digitalWrite(motor_pin_6, LOW);
        digitalWrite(motor_pin_7, HIGH);
        digitalWrite(motor_pin_8, HIGH);
      break;
      case 2:  //0010
        digitalWrite(motor_pin_5, LOW);
        digitalWrite(motor_pin_6, LOW);
        digitalWrite(motor_pin_7, HIGH);
        digitalWrite(motor_pin_8, LOW);
      break;
      case 3:  //0110
        digitalWrite(motor_pin_5, LOW);
        digitalWrite(motor_pin_6, HIGH);
        digitalWrite(motor_pin_7, HIGH);
        digitalWrite(motor_pin_8, LOW);
      break;
      case 4:  // 0100
        digitalWrite(motor_pin_5, LOW);
        digitalWrite(motor_pin_6, HIGH);
        digitalWrite(motor_pin_7, LOW);
        digitalWrite(motor_pin_8, LOW);
      break;
      case 5:  // 1100
        digitalWrite(motor_pin_5, HIGH);
        digitalWrite(motor_pin_6, HIGH);
        digitalWrite(motor_pin_7, LOW);
        digitalWrite(motor_pin_8, LOW);
      break;
      case 6:  //1000
        digitalWrite(motor_pin_5, HIGH);
        digitalWrite(motor_pin_6, LOW);
        digitalWrite(motor_pin_7, LOW);
        digitalWrite(motor_pin_8, LOW);
      break;
      case 7:  //1001
        digitalWrite(motor_pin_5, HIGH);
        digitalWrite(motor_pin_6, LOW);
        digitalWrite(motor_pin_7, LOW);
        digitalWrite(motor_pin_8, HIGH);
      break;
    }
    delay(step_delay);
}

That’s it!

Complete Influx TICK Stack Disaster Recovery

My entire system became corrupt one day and while it was technically booting it was not functioning. I did not have proper backups so the road to recovery was long & painful. I now have better emphasis on backups.

Typically all Influx data is backed up by:

influxd backup -portable /media/usb/drive

and restored with

influxd restore -portable /media/usb/drive

I did not have this luxury so I started with copying all the main files to and external drive, these were:

/var/lib/influxdb/data
/var/lib/influxdb/wal
/var/lib/influxdb/meta
/var/lib/kapacitor/kapacitor.db

Okay we are now finished with the corrupted image, do a full fresh install of your system. (tutorial)

Great we are now all setup, insert USB where files were backed up to before, we need to tell Influx config to look at memory stick, edit the below file with:

sudo nano /etc/influxdb/influxdb.conf 
[meta]
  #dir = "/var/lib/influxdb/meta"
  dir = "/media/usb/drive/meta"

[data]
  #dir = "/var/lib/influxdb/data"
  dir = "/media/usb/drive/data"

  #wal-dir = "/var/lib/influxdb/wal"
  wal-dir = "/media/usb/drive/wal"

I also needed to change the user of the files on the USB by:

sudo chown -R influxdb:influxdb /media/usb/drive

We will revert some of the above changes later on.
Note: My original plan was to have all files on the USB drive permanently but as soon as I added the data source in Chronograf everything broke so I undid this. I just used this step to export the data properly.

Reboot system.

Now all your old data should be loaded.

Now we will create a proper backup of the data with the below:

influxd backup -portable /media/usb/drive

Revert all changes in the influxdb.conf file:

sudo nano /etc/influxdb/influxdb.conf 

Now restore all data back to the default locations by:

influxd restore -portable /media/usb/drive

Since it took me a few days to figure out how to restore data I already had the system back up recording data, the above restore does not work if a database is already created so I had to side-load all databases in with:

influxd
CREATE my_data_bak
USE my_data_bak
SELECT * INTO my_data..:MEASUREMENT FROM /.*/ GROUP BY *
DROP DATABASE my_data_bak
exit

Finally add back in your Chronograf alerts etc. by:

sudo mv /var/lib/kapacitor/kapacitor.db /var/lib/kapacitor/kapacitor_orig.db 
sudo mv /media/usb/drive/kapacitor.db /var/lib/kapacitor/
sudo chown -R kapacitor:kapacitor /var/lib/kapacitor/kapacitor.db

Future planning would be to keep regular backups with: (you need to do this individually for all databases). See my other post on this.

influxd backup -portable /media/usb-influx/backup
kapacitor backup /media/usb/drive/kapacitor.db

Reboot and we are done!

Using integral function on Grafana (covert Watt to kWh)

After fighting for longer than I’d like to admit with this function I finally managed to get it working.

I use a single stat visualation and the below queries to give me energy usage in Watt/Hour from my data stored in Watts.

1hr Usage: (Relative time over-ride = 1h)

SELECT integral("Energy_Usage",1h) FROM "esp" WHERE ("Device" = 'esp_03') AND $timeFilter GROUP BY time(3h) 

24hr Usage: (Relative time over-ride = 24h)

SELECT integral("Energy_Usage",1h) FROM "esp" WHERE ("Device" = 'esp_03') AND $timeFilter GROUP BY time(3d) 

7 Day Usage: (Relative time over-ride = 7d)

SELECT integral("Energy_Usage",1h) FROM "esp" WHERE ("Device" = 'esp_03') AND $timeFilter GROUP BY time(21d) 

That’s it!

Installing TICK Stack on RPi4

Nothing complicated this time, just commands I use to setup my Influx TICK stack from fresh install.

sudo apt-get update
sudo apt-get upgrade
wget -qO- https://repos.influxdata.com/influxdb.key | sudo apt-key add -
source /etc/os-release
test $VERSION_ID = "10" && echo "deb https://repos.influxdata.com/debian buster stable" | sudo tee /etc/apt/sources.list.d/influxdb.list
sudo apt-get install influxdb
sudo apt install influxdb-client
sudo apt-get update
sudo apt-get install telegraf
sudo apt-get install chronograf
sudo apt-get install kapacitor
sudo systemctl unmask influxdb.service 
sudo systemctl start influxdb 
sudo apt-get install fail2ban
sudo apt-get install ntp
sudo apt-get install ntpstat
systemctl stop systemd-timesyncd
systemctl disable systemd-timesyncd
/etc/init.d/ntp stop
/etc/init.d/ntp start
sudo reboot

Confirm everything is working:

sudo service kapacitor status
sudo service chronograf status
sudo service influxdb status
sudo service telegraf status
ntpstat

You can also head to the Chronograf configuration page on: http://192.168.1.xxx:8888

That’s it!

Backup & Restore Grafana

I had full TICK stack and Grafana running on a RPi4 for a couple of months without issue until suddenly CPU usage went through the roof and reduced functionality (caused by InfluxDB, unknown why) so I need to do a full reinstall, at this point I decided to put Grafana on a separate machine (RPi3), below is how to export Grafana configuration and import onto a different machine.

Export old config by copying the below files to external USB drive:

/var/lib/grafana/grafana.db
/etc/grafana/grafana.ini

After Installing Grafana on new machine:
Note: You can upgrade to the highest minor release of your current Grafana version, I upgraded from 6.3 to 6.7.3. All versions viewable here.

sudo apt update
sudo apt upgrade
sudo apt-get install -y adduser libfontconfig1
wget https://dl.grafana.com/oss/release/grafana-rpi_6.7.3_armhf.deb
sudo dpkg -i grafana-rpi_6.7.3_armhf.deb
sudo systemctl unmask grafana-server.service
sudo systemctl start grafana-server
sudo systemctl enable grafana-server.service
sudo reboot

Insert USB into new machine and import the configuration files again:

cd usb-drive/
sudo cp grafana.db /var/lib/grafana/
sudo cp grafana.ini /etc/grafana/

The only other adjustment I had to do was adjust the Grafana Datasources URL from the previous local host to the InfluxDB Server address since they were now on different machines.

That’s it!

Tweeting when Aircraft Overhead

As per my previous post (specifically the “Logging to database: (SQLite)” paragraph) I am logging detected flights to SQL database, with a small bit of code we can tweet when certain aircraft are detected overhead:

First create a twitter account if not done so already.

Next setup tweepy for python and get your twitter authentication tokens using this tutorial: https://realpython.com/twitter-bot-python-tweepy/

Finally replace the last line of the exiting write to database code at my GitHub with:

    for index, row in df1.iterrows():
        if df1['hex'][index] == 'HEX_CODE_YOU_WANT_TO_TWEET_ABOUT': 
            print("Found")
            import tweepy
            # Authenticate to Twitter
            auth = tweepy.OAuthHandler("AUTH_TOKEN","AUTH_TOKEN")
            auth.set_access_token("AUTH_TOKEN","AUTH_TOKEN")
            api = tweepy.API(auth)

            try:
                api.verify_credentials()
                print("Authentication OK")
                api.update_status('Tweet Text' + str(dateTime))

            except:
                print("Error during authentication")
        else:
            print("Hex was: ", df1['hex'][index])

    exit()

That’s it, happy tweeting!

Logging dump1090-fa to local database

As per my previous post I am feeding ADSB Exchange and Flight Radar24 from a RaspberryPi Zero and a USB DVB-T tuner.

This post is broken into three un-linked sections:
1. Logging all flights to .csv file.
2. Deciding csv was not ideal and move logging to database SQLite (incl. setup).
3. Solution to show all days flights on webpage.

Logging to .csv file:

I wanted to locally log flights that flew overhead each day but didn’t have the knowledge to put it all together until /u/gl0ckner/ on Reddit posted his work on logging flights to a .csv file. That didn’t work right out of the box for me so I made some tweaks and my slight modified version can be found on my GitHub. Simply download the file and run it manually by:

python3 /home/pi/flightlogger/flight_logger_csv.py

Or make executable and add to crontab to execute every minute as so:

chmod +x /home/pi/flightlogger/flight_logger_csv.py
crontab -e
* * * * * /usr/bin/python3 /home/pi/flightlogger/flight_logger_csv.py

A new .csv is created for each day. It works well. After a few days I thought it would be more helpful to query the data if it was in a database.

Note: So now a new entry is made to the database every minute regardless if the particular aircraft has been logged previously or not on the same day. I want to change it to say only log an aircraft if not logged in the last hour, this has been implemented in the below database option.

Logging to database: (SQLite)

After a comment by /u/Uncle_BBQ on the same Reddit post who submitted his work on this I thought I would give it a go. This script logs each overhead flight once into the database. It was my first time ever using a database and as usual it didn’t work right out of the box for me so I had to make a few tweaks, below is how to get it running:

#First install dependencies.
#They did not install properly for me from the script so did it manually.
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install sqlite3
pip3 install pandas
pip3 install numpy
pip3 install cython
pip3 install sqlalchemy
pip3 install psycopg2

Now we need to set up the database:

sqlite3 flightdata_1h.db
CREATE TABLE flightdata (date_time NUMERIC, date NUMERIC, time NUMERIC, hex TEXT, flight TEXT, alt_baro NUMERIC, alt_geom NUMERIC, gs NUMERIC, track NUMERIC, geom_rate NUMERIC, squawk NUMERIC, emergency TEXT, category TEXT, nav_qnh NUMERIC, nav_altitude_mcp NUMERIC, lat NUMERIC, lon NUMERIC);
.quit

Finally copy the file from my GitHub and run:

python3 /home/pi/flightlogger/flight_logger_sql.py

To see what’s in the database we can query it by:

sqlite3 flightdata_1h.db
SELECT date, time, hex, flight FROM flight_data;

Your output will look like this:

2020-03-21|20:56:23|406c39|VIR25B
2020-03-21|20:58:20|4077be|JCO7X

Finally to run the script every minute, make it executable and add to crontab:

chmod +x /home/pi/flightlogger/flight_logger_sql.py
crontab -e
* * * * * /usr/bin/python3 /home/pi/flightlogger/flight_logger_sql.py

You can query the database directly if needed, for example:

sqlite3 flightdata_1h.db
SELECT date_time, date, time, hex FROM flight_data ORDER BY DATE(DATE) desc LIMIT 100;

Updating webpage with flights that went overhead today.

Note: The perfect solution is when a webpage is requested the database is queried and the results delivered. Since I’m running SQLite and all tutorials were for SQL (and MariaDB wouldn’t run on RPi Zero) I went about it a different way. Through a cron job every hour the database is queried and the results pushed to a .csv file, this csv file is then put into a table and the webpage delivered whenever requested.

To populate the .csv file I use:

#!/usr/bin/python3

# Import dependcies (probably don't need half of them, I just used an old file)
import os
import json
import csv
from dotenv import load_dotenv
from datetime import datetime
from datetime import date
from datetime import time
from datetime import timedelta
import requests
import pandas as pd
import numpy as np
import sqlalchemy
from sqlalchemy import create_engine

# Load env variables
load_dotenv(dotenv_path='')
db = 'sqlite:////home/pi/flightlogger/flightdata_1h.db'
db_table = 'flight_data'

# connect to database
engine = create_engine('sqlite:////home/pi/flightlogger/flightdata_1h.db')

# Get today's date
today = date.today()

# Get the current time
time = datetime.now().strftime("%H:%M:%S")

# Create a current time stamp
dateTime = datetime.strptime(datetime.now().strftime('%Y-%m-%d %H:%M:%S'), '%Y-%m-%d %H:%M:%S')

# Try to connect to database
try:
    df2 = pd.read_sql("SELECT * FROM flight_data WHERE flight_data.date_time > datetime('now','localtime', '-3600 seconds')", engine) #SQLite Syntax
    dbConnected = True

except:
    # If database does not exist or is unable to connect then print that
    print('Unable to connect to database.')
    # Set boolen value to False
    dbConnected = False

df2.to_csv("/var/www/html/data.csv", mode='a', header=False)

To clear the .csv file every night at midnight I use:

file = open("/var/www/html/data.csv","w")
file.write("index,date_time,hex,flight,alt_baro,alt_geom,gs,track,geom_rate,squawk,emergency,category,nav_qnh,nav_altitude_mcp,lat,lon,date,time\n")
file.close()

Cron jobs to run the above:

0 * * * * /usr/bin/python3 /home/pi/flightlogger/db_flight_to_csv.py
10 0 * * * /usr/bin/python /home/pi/flightlogger/csv_clear.py

The webpage located at /var/www/html/index.html is:

<!DOCTYPE html>
<html lang="en">
<!-- http://bl.ocks.org/ndarville/7075823 -->

    <head>
        <meta charset="utf-8">
        <style>
            table {
                border-collapse: collapse;
                border: 2px black solid;
                font: 12px sans-serif;
            }

            td {
                border: 1px black solid;
                padding: 5px;
            }
        </style>
    </head>
    <body>
        <!-- script src="http://d3js.org/d3.v3.min.js"></script -->
        <!-- script src="d3.min.js?v=3.2.8"></script -->
        <script src="d3.v3.min.js"></script>

        <script type="text/javascript"charset="utf-8">
            d3.text("data.csv", function(data) {
                var parsedCSV = d3.csv.parseRows(data);

                var container = d3.select("body")
                    .append("table")

                    .selectAll("tr")
                        .data(parsedCSV).enter()
                        .append("tr")

                    .selectAll("td")
                        .data(function(d) { return d; }).enter()
                        .append("td")
                        .text(function(d) { return d; });
            });
        </script>
    </body>
</html>

Give ‘pi’ user access to edit the .csv file:

sudo chown -R pi /var/www/html/data.csv 

That’s it!

Other references I used:
https://www.hackster.io/mjrobot/from-data-to-graph-a-web-journey-with-flask-and-sqlite-4dba35
https://towardsdatascience.com/sqlalchemy-python-tutorial-79a577141a91
https://docs.sqlalchemy.org/en/13/core/engines.html#postgresql
https://www.sqlitetutorial.net/sqlite-commands/

Overwrite InfluxDB point

I had an issue where I had spurious high values reported to one of my databases and I didn’t have time to debug for a while so I ended up overwriting the point about once a week. I couldn’t find a way to delete the measurement completely but overwriting works well:

Launch Influx CLI:

 influx

Select your database:

 use dev_db

Find the point you want, for me it was always the max value:

SELECT max("Energy_Usage") FROM "esp" WHERE ("Device" = 'esp_03') 

The result returned was:

name: esp
time                max
----                ---
1583863516000000000 1049397312

Now we take the time returned from above and rewrite over the point in the database, (I overwrote it with a value of 150:

INSERT esp,Device=esp_03 Energy_Usage=150 1583863516000000000

That’s it!

PiHole logging to InfluxDB & Grafana Dash

Building on the work of others before me, below you will find a tutorial to get PiHole logging to InfluxDB using a python script and then to a Grafana Dashboard. All required code available on my GitHub.

SSH into your PiHole: ssh pi@xxx.xxx.xxx.xxx and run the below:

Install python dependencies:

sudo apt-get install python-influxdb

Create the below python file:

sudo nano influx_scripts/piholestats.py
#! /usr/bin/python

# History:
# 2016: Script originally created by JON HAYWARD: https://fattylewis.com/Graphing-pi-hole-stats/
# 2016 (December) Adapted to work with InfluxDB by /u/tollsjo
# 2016 (December) Updated by Cludch https://github.com/sco01/piholestatus
# 2020 (March) Updated by http://cactusprojects.com/pihole-logging-to-influxdb-&-grafana-dash

import requests
import time
from influxdb import InfluxDBClient

HOSTNAME = "pihole" # Pi-hole hostname to report in InfluxDB for each measurement
PIHOLE_API = "http://192.168.1.XXX/admin/api.php"
INFLUXDB_SERVER = "192.168.1.XXX" # IP or hostname to InfluxDB server
INFLUXDB_PORT = 8086 # Port on InfluxDB server
INFLUXDB_USERNAME = ""
INFLUXDB_PASSWORD = ""
INFLUXDB_DATABASE = "dev_pihole"
DELAY = 10 # seconds

def send_msg(domains_blocked, dns_queries_today, ads_percentage_today, ads_blocked_today):

	json_body = [
	    {
	        "measurement": "piholestats." + HOSTNAME.replace(".", "_"),
	        "tags": {
	            "host": HOSTNAME
	        },
	        "fields": {
	            "domains_blocked": int(domains_blocked),
                    "dns_queries_today": int(dns_queries_today),
                    "ads_percentage_today": float(ads_percentage_today),
                    "ads_blocked_today": int(ads_blocked_today)
	        }
	    }
	]

	client = InfluxDBClient(INFLUXDB_SERVER, INFLUXDB_PORT, INFLUXDB_USERNAME, INFLUXDB_PASSWORD, INFLUXDB_DATABASE) # InfluxDB host, InfluxDB port, Username, Password, database
	# client.create_database(INFLUXDB_DATABASE) # Uncomment to create the database (expected to exist prior to feeding it data)
	client.write_points(json_body)

api = requests.get(PIHOLE_API) # URI to pihole server api
API_out = api.json()

#print (API_out) # Print out full data, there are other parameters not sent to InfluxDB

domains_blocked = (API_out['domains_being_blocked'])#.replace(',', '')
dns_queries_today = (API_out['dns_queries_today'])#.replace(',', '')
ads_percentage_today = (API_out['ads_percentage_today'])#
ads_blocked_today = (API_out['ads_blocked_today'])#.replace(',', '')

send_msg(domains_blocked, dns_queries_today, ads_percentage_today, ads_blocked_today)

Save and Exit.

I have the file run on a cron job every minute. Others set it up as a service but cron job works just fine for me:

crontab -e
*/1 * * * * /usr/bin/python /home/pi/influx_scripts/piholestats.py

We need to create Influx database next, I carried this out through the Chronograf web interface but add it through the terminal by the below if required:

influx
create database dev_pihole
exit

Now onto Grafana Dash:

Add the “dev_pihole” database to the Grafana Data Sources list.

Next go to “Import dashboard” and paste in the JSON code on my Github. I tweaked a previous dashboard slightly.

All done!