Extracting widget history data (for MQTT and LoRa devices)

Please note that this post only applies to MQTT (‘Bring Your Own Thing’) and LoRa connected devices. For non-MQTT Raspberry Pi and Arduino devices, you want this sister post.

I’ve found that the “Download Chart Data” button doesn’t always satisfy everyone’s needs to pull data which has been logged to Cayenne out of the system. The purpose of this post is to show you how you can extract the raw sensor data for LoRa and MQTT (‘Bring Your Own Thing’) devices from our system.

The process is a bit complex, but should allow you to work around any bugs or limitations in our data download feature until such time as we improve it. Once you’ve done it a few times it becomes fairly straightforward.

Extracting historical data for a single widget

  1. Install the Postman Chrome App: Postman - Chrome Web Store

  2. You’ll need to get something we call an ‘access token’. To get this, log into the Cayenne web dashboard on your account in Chrome, and open the console with View > Developer > JavaScript Console. In the console, paste: localStorage.getItem("session"). This will return something like this:

    {“id":“728005”,“email”:"testtest@test.com”,“first_name”:“Test”,“last_name”:“Test”,“access_token”:“eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJlbWFpbCI6InJvYm55MjAxN0B5b3BtYWlsLmNvbSIsImZpcnN0X25hbWUiOiJSb2IiLCJsYXN0X25hbWUiOiJUZXN0Iiwic2NvcGUiOlsic2VsZiJdLCJ1c2VyX2lkIjoiNzI4MDA1IiwiaWF0IjoxNTAzMDg3NDE5LCJleHAiOjE1MDQzODM0MTksImF1ZCI6Im15ZGV2aWNlcy5jYXllbm5lLnByb2QiLCJpc3MiOiJteWRldmljZXMuYXNnYXJkLnByb2QiLCJqdGkiOiJmZjI5OThjNS0yNGU4LTRiYWMtODIwZC0zODAyNzgzMjBjODkifQ.h_KQmJR8wcCghHMqgcvcYFgfv8PqmWUJ1VbaYlR9fXA”,“refresh_token”:“cf265d2aeee235a93b876d3773fce106baa89fc8”,“client_id”:“0d31d860-3591-11e7-85a7-c34806d4e04d”,“client_secret”:“b1ba96b44f102eedfd47aafdf92474bd2782b563”}

    The access token is the part that I’ve bolded, and will be different for your account.

  3. Next, you’ll need two more items, a ‘device id’ which identifies the parent device on your Cayenne account, and a ‘sensor id’ that identifies the widget you want to pull historical data for. You can get these both at once. Open the Settings dialog for the widget whose data you’re pulling on the web dashboard, and make note of what’s in your browser address bar. Here is an example from a Temperature Widget on a LoRa device on my account:

    https://cayenne.mydevices.com/cayenne/dashboard/lora/02f2d830-454c-11e7-b5e5-0383e230cbfa/settings/82c007a0-4ad2-11e7-98bd-9ffdab1de3b9

    In this case, 02f2d830-454c-11e7-b5e5-0383e230cbfa is my device ID, and 82c007a0-4ad2-11e7-98bd-9ffdab1de3b9 is my sensor ID.

  4. Next you need to get the UNIX timestamp in milliseconds for the start and end times of the date range you want to pull. This website is pretty useful for that.

    I’m writing this at Tue, 29 Aug 2017 15:10:55 MDT (GMT -6), so I’ll make that my end time. Using the site, it converts to the timestamp 1504041055976. Let’s say I want to pull the last two days of sensor data, so using Sun, 27 Aug 2017 15:10:55 MDT as my start time, that converts to 1503868255000.

  5. Next, launch Postman that you downloaded in step #1. Go first to the ‘Headers’ tab. You’ll need to add two keys here:

  • One key named Authorization with a value of Bearer <access_token> (the access token from step 2)
  • A second key named Content-Type with a value of application/json

  1. In the large “GET” field, you need a URL of this format: https://platform.mydevices.com/v1.1/telemetry/YOURDEVICEID/sensors/YOURSENSORID/summaries?type=custom&startDate=STARTDATE&endDate=ENDDATE. So using all of my examples in this post, my GET URL looks like:

    https://platform.mydevices.com/v1.1/telemetry/02f2d830-454c-11e7-b5e5-0383e230cbfa/sensors/82c007a0-4ad2-11e7-98bd-9ffdab1de3b9/summaries?type=custom&startDate=1503868255000&endDate=1504041055976

  2. The moment of truth: press the big blue ‘Send’ button in Postman. If everything was set correctly, you’ll be rewarded with a JSON readout of each of the data points you requested. To show the general format, mine looks like this: [ { "v": 35, "ts": "2017-08-29T21:10:00.000Z", " - Pastebin.com if you’re curious about the weather outside our office :slight_smile:

:sweat_smile: That’s it! Let me know if you get stuck following that or have any questions. It’s finicky but once you get the hang of it, just rinse and repeat for each sensor.

-or-

Extracting historical data for all widgets on a MQTT/LoRa device

To do this, follow steps 1 through 5 above. You can skip gathering the Sensor ID in step 3 since we’re going to pull all sensors in this example.

For the GET field in Postman, you’ll want to build a URL like:

https://platform.mydevices.com/v1.1/telemetry/YOURDEVICEID/summaries?type=custom&startDate=YOURSTARTDATE&endDate=YOURENDDATE

using my example device above, this looks like:

https://platform.mydevices.com/v1.1/telemetry/02f2d830-454c-11e7-b5e5-0383e230cbfa/summaries?type=custom&startDate=1503868255000&endDate=1504041055976

This will return a larger JSON block that includes all widget data for the specified date range!

2 Likes

Thanks for this. Very useful to be able to access the data.

The finest resolution seems to be 1 min. My device is currently sending data every 10 seconds. Is 1 minute the finest resolution possible? Is the 1 minute data an average of the data received during the previous minute or something else?

Hi @paul2, just wanted to acknowledge that I see this question, and I’m asking internally to see if there is a 10 second resolution available (or really, just what is the maximum resolution we can pull via this method). I’ll report back after I’ve chatted with the developer who helped me with this initial writeup.

Hello,

Is there a way to specify (in the URL) a different resolution than the default “minute”, for example 1 hour?

This is great but I had a little issue with epoch time. Using the link provided I used the converter but it gave the a unix timestamp and when I used it it didn’t work.

startDate=1515110400&endDate=1515196800

I compared my timestamp to yours and noticed yours had more digits.

startDate=1503868255000&endDate=1504041055976

So I realised the difference is seconds from epoch and milliseconds from epoch. So I made the transformation and ended up with this:

startDate=1515182400000&endDate=1515268800000

So a quick way to get both start and end date without going to the epoch converter is to just paste in to the console in dev tools.

new Date('01/06/2018 20:00').getTime()
2 Likes

It’s good advice @rymercho :slight_smile:

I generally just append three zeroes to the usual ‘seconds from epoch’ reading since fractions of a second are not a concern for me when pulling sensor data from Cayenne.

@amitrica & @paul2, to answer your questions about the data resolution possible through the method outlined in this thread, currently it is not adjustable – so if your device sends as often as one minute, we’ll give you everything we have up to this resolution. There are no filters like hour/day like there are for the non-MQTT data in the other thread. We’ll consider adding them as a feature to this API in the future.

In the meantime, all of the information that comes through the method in this thread is standard JSON with the timestamp in its own field, so it should be possible to filter out data using this.

Hi, I’m trying to find the most recent value (and its time) of a sensor.

I’ve gone through this procedure which works good, but its somewhat inelegant to be fetching a minute, then an hour, then a day, … to try to find the last time the sensor updated.

Does this “summaries” system offer a “last updated value” from its history, or just a “current value” that would appear on the dashboard (and its time)?

bumped into this in today’s latest posts:

https://platform.mydevices.com/v1.1/telemetry/8x-4x-4x-4x-12x/sensors/8x-4x-4x-4x-12x/summaries?type=latest

which returns a nice:
{
“v”: “21.800”,
“ts”: “2018-02-07T21:04:01.698Z”,
“unit”: “C”,
“device_type”: “analog”
}
]
… which is just what I’m looking for.

Then went looking in here Cayenne Cloud API Cayenne Docs

… but still cannot find anything about this “telemetry” category. Lots of listing jobs, and creating rules, … but no mention of “telemetry” or “summaries”.

So, very confused. :confused:

@ecoqba any ideas here?

good morning I am trying to recover the data from my device and it does not return any data.
if I do it for widgets if you bring me the information, but I would need to bring everything together since they are too much widgets

@demo.iot.arcos.dorad can you Private message me your device_id and we will look into the issue.

Hi ,

I am trying to download historical data from cayenne for all my sensors connected top raspberry pi. I tried the above method but it gives me a 404 error or unauthorized.
Could someone help me with process to get historical data for a duration of about a month?
I also tried download option in the console but it doesnt seem to work for the amount of data i have.

have a look at this topic How to access Cayenne API using Insomnia, Node-red and Thunkable

Thanks I will have a look!