Exporting .CSV crashes Web Dashboard, can't download data history

Bugs / Issues
1

Hello and tanks in advance for the help!
I needed to set up a simple temperature monitoring station, which is supposed to measure temperatures from two industrial refrigerators and log their temperatures at least hourly, and export the history of the sampled temperatures in .CSV so that I can plug the data into an Excel table under a standard formatting required for local regulations, in order to submit a monthly report.

It’s a simple task, and a fairly common application for Cayenne, but I’ve been running into some critical problems when it comes to the last step: exporting data from the Web Dashboard in csv format.

If I go into the “Data” tab within my project and select 1mo it successfully shows some of the latest measurements, loads more if I scroll down, but as soon as I click the “Download button” the page crashes and displays a default 404 error.
It’s not completely broken, though. If I try and do the same thing, only exporting the values for the last 24 hours, the web page successfully generates a .csv file and I have no problems at all.

I suspect it has to do with the way bigger size of the table, since I am requesting to download the data for the whole month?

The device works perfectly and so does the web dashboard. The graph widget logs data and accurately displays it in real time. Downloading data for the last month from the widget itself, under the “Overview” tab and not the “Data” tab works, too – but it’s not the full data for the whole month, just a set of points with daily averages, I guess.

I have only been running the project for a couple weeks. My current sketch, transmits data over two channels every 60 seconds, which is admittedly more resolution than I need for monthly reports, but having more data points is useful for short-term monitoring, and it would trigger over-temperature alarms sooner if needed, but I’ll tune it down if necessary.
The resulting table size should be, very crudely: 60 rows an hour * 24 hours a day * 30 days * ~150 characters per row… around 50 MB per channel, for a month of data?

  • I’d prefer sampling at least every 10-15 minutes or so, for redundancy, so what if I tuned it down to only send only a few times an hour, would that help?
  • Every row has the same DeviceID and SensorID fields, which are lengthy and useless to my specific application: is there a way to not store any to make the file lighter and more readily processed?
  • What is the maximum available cloud storage provided by Cayenne, anyway? Should I expect my older data to be erased after a while, and if so how soon? Months? Years? Does it depend on storage limits?

As per the rules of the community, this is my project info:

  • Device: NodeMCU ESP8266 board, connected over wifi, with two DS18B20 temperature sensors
  • Dashboard: Web
  • Connection: MQTT, onboard WiFi connectivity
  • Sketch written in Arduino IDE using CayenneMQTTESP8266.h, OneWire.h and DallasTemperature.h libraries.
2

On cayenne, the data is stored for 1 month only. The reason for the 404 error may be due to datatype and data uplink interval. So can share the code you are using. Each device has more than one sensors hence we have added the sensor ID field.

3

Thank you for the quick reply! Here is my code

#define CAYENNE_PRINT Serial
#include <CayenneMQTTESP8266.h>
#include <SimpleTimer.h>
#include <OneWire.h>
#include <DallasTemperature.h>

#define TIMER 60000

char ssid[] = "";
char wifiPassword[] = "";

// Cayenne authentication info. This should be obtained from the Cayenne Dashboard.
char username[] = "";
char password[] = "";
char clientID[] = "";

//Initialization of variables I set
unsigned long lastMillis = 0;
int fpast = 0;
bool flag = false;
const int tmpPin = 12;                //"Temperature Pin": Digital pin the DS18B20s are connected to.
                                      //Do not use digital pins 0 or 1 since those conflict with the use of Serial.

const int ledPin =  LED_BUILTIN;      // the number of the LED pin
int ledState = LOW;                   // ledState used to set the LED
unsigned long previousMillis = 0;     // will store last time LED was updated
const long interval = 500;            // interval at which to blink (milliseconds)

OneWire oneWire(tmpPin);
DallasTemperature sensors(&oneWire);
SimpleTimer firstTimer(TIMER);
SimpleTimer blinkTimer(500);

void setup() {
  Serial.begin(9600);
  Cayenne.begin(username, password, clientID, ssid, wifiPassword);
  sensors.begin();

  pinMode(LED_BUILTIN, OUTPUT); //blink function to see if it's powered on
}

void loop() {

  Cayenne.loop();

  unsigned long currentMillis = millis(); 
  
  if (currentMillis - previousMillis >= interval) {
    // save the last time you blinked the LED
    previousMillis = currentMillis;

    // if the LED is off turn it on and vice-versa:
    if (ledState == LOW) {
      ledState = HIGH;
    } else {
      ledState = LOW;
    }
    digitalWrite(ledPin, ledState);
  }

  if (firstTimer.isReady()) {
    lastMillis = millis();    //Sending current milliseconds of uptime on channel 0

    sensors.requestTemperatures(); //aggiunto dalla libreria

    Cayenne.celsiusWrite(1, sensors.getTempCByIndex(0)); //"1" is channel 1 on Cayenne, refrigerator

    Cayenne.celsiusWrite(3, sensors.getTempCByIndex(1)); //"3" is channel 3 on Cayenne, freezer

    Cayenne.virtualWrite(0, lastMillis);

    CAYENNE_OUT_DEFAULT();
    firstTimer.reset();
  }
}

// Default function for sending sensor data at intervals to Cayenne.
// You can also use functions for specific channels, e.g CAYENNE_OUT(1) for sending channel 1 data.
CAYENNE_OUT_DEFAULT()
{
  // Write data to Cayenne here. This example just sends lux data or proximity
  //Cayenne.luxWrite(2, 700);
  //Cayenne.virtualWrite(3, 50, TYPE_PROXIMITY, UNIT_CENTIMETER);
}

// Function for processing actuator commands from the Cayenne Dashboard.
// You can use functions for specific channels, e.g CAYENNE_IN(1) for channel 1 commands.
CAYENNE_IN(4)
{
  CAYENNE_LOG("Channel %u, value %s", request.channel, getValue.asString());
  int f = getValue.asInt();
  //there is a button widget set on channel 4, I use it to request an instantaneous reading of the temperature
  //to be displayed on two separate widgets.
  //Every time the value changes on channel 4 (from 1 to 0 and back, due to the manual action of the button) 
  //it sends out an instant reading of the temperature on channel 6 and 7
  if (f != fpast) {
    
    sensors.requestTemperatures();
    Cayenne.celsiusWrite(6, sensors.getTempCByIndex(0));
    Cayenne.celsiusWrite(7, sensors.getTempCByIndex(1));

    fpast = f;

  }
  //Process message here. If there is an error set an error message using getValue.setError(), e.g getValue.setError("Error message");
}

Thanks, let me know.
Is there any way to store more than one month of data, even if I have to pay?

4

remove this from CAYENNE_IN() and send it from the main loop once this function is run.

Remove Cayenne.virtualWrite(0, lastMillis);. not best practise to send data without datatype.
Also, any reason to use SimpleTimer. You can use millis() to get the time and send data at a timed interval.

5

Thanks,
I could remove Cayenne.virtualWrite(0, lastMillis); altogether, i don’t really need it, it was included in the example, and it felt like a simple way to see if the device is online and responsive.
I will move that section from CAYENNE_IN() to the main loop immediately.
I used SimpleTimer instead of millis() because I didn’t want to deal with any eventual overflow, it felt like a quick fix with no significant downsides.

Anyways, how is this going to help with my main issue, which is generating and downloading the CSV file?