Furnace monitor that shows furnace usage over time

I created a device that monitors how long my furnace has been used and logs it with HA. It uses a microphone to listen to the furnace, then logs the number of seconds it’s been running every 15 minutes. Then I can use a SQL query to add up the number of seconds/day the furnace has run. The results are interesting. I can export the results of the query into a spreadsheet and graph them. I want to try using this sensor on other things to monitor their usage, dishwasher, washing machine, etc.

Does HA have a way to do this on the HA server? It would be nice to see a graph of my furnace usage on the front page of HA.

Below is the ESP8266 code and the SQL query I wrote:

sqlite3 -separator , home-assistant_v2.db "select strftime('%m-%d-%Y', last_changed), sum(state) as seconds from states where entity_id='sensor.burner_act15mins' group by 1;"

#include <ESP8266WiFi.h>
#include <PubSubClient.h>

#define mqtt_server ""
#define mqtt_user "XXXX"
#define mqtt_password "XXXX"
#define mqtt_topic "sensor/sound"
#define IOPIN 14          //The pin number the sensor is on.

//Samples are the number of signals from the sensor, We need to account for noise
#define SAMPLELENGTH 2000 //Milliseconds
#define SAMPLECOUNT 5     //Number of sample counts per SAMPLELENGTH to be considered an active event. Counts are sampled every 10ms in 100ms intervals

//How many minutes to add up active samples until sending them to the server.
#define COUNTPERIOD 15 * 60 * 1000 //Milliseconds

const char WiFiSSID[] = "XXXX";
const char WiFiPSK[] = "XXXX";
char MAC_char[18];
int currentstate = HIGH; //HIGH or LOW

WiFiClient espClient;
PubSubClient client(espClient);

void setup() {
  uint8_t MAC_array[6];
  // put your setup code here, to run once:
  client.setServer(mqtt_server, 1883);

  //Setup the pins to monitor switches.
  Serial.println("Setting pin mode.");

  //Get the mac and convert it to a hex string.
  Serial.println("Finding MAC address.");
  for (int i = 0; i < sizeof(MAC_array); ++i) {

void loop() {
  static int samplestarttime = 0;
  static int samplecounts = 0; //Active sensor readings
  static int activesamples = 0; //Number of active samples accumlated per count period.
  static int lastStateSend = 0;

  //MQTT connection management
  if (!client.connected()) {

  //Get the number of samples.
  if(millis() - samplestarttime < SAMPLELENGTH) {
    //Block on the pin and wait for a state change, count the changes.
    int loop = millis();
    while(millis() - loop < 500) {
      if(pulseIn(IOPIN, LOW, 500) > 0) {
  } else {
    //A full interval of time has passed
    Serial.println(String(SAMPLELENGTH/1000) + " second interval: " + String(samplecounts));
    if(samplecounts > SAMPLECOUNT) activesamples++;

    //Reset the counters
    samplestarttime = millis();
    samplecounts = 0;
  //Resend the state every interval of minutes and on bootup.
  if(millis() - lastStateSend > COUNTPERIOD || lastStateSend == 0) {
    //Convert the number of active samples to the number of active seconds.
    sendState(String(activesamples * (SAMPLELENGTH / 1000)).c_str());
    lastStateSend = millis();
    activesamples = 0;

void sendState(const char *state) {
  client.publish((mqtt_topic+String(MAC_char)).c_str(), String(state).c_str(), true);

//This function maintains a connection to the MQTT server.
void reconnect() {
  char uid[21] = "ESP";

  strcat(uid, MAC_char);
  Serial.println("Reconnecting MQTT server.");
  while (!client.connected()) {
    client.connect(uid, mqtt_user, mqtt_password);
  Serial.println("Connected MQTT server.");

void connectWiFi()
  WiFi.begin(WiFiSSID, WiFiPSK);

  Serial.println("Connecting to WiFi.");
  while (WiFi.status() != WL_CONNECTED)
    //Delay until connected.
  Serial.println("Connected to WiFi.");