Downloading historic data


#1

I have been using the ‘download chart data’ function to examine historic data from my device. I’m sending data approximately once every 5 seconds from each device. The data for the previous hour appears to be the raw data. The data over a day appears to provide points which are averages over every minute. The data for a month is probably an average over a longer period but I haven’t determined how long yet.

I am interested in the peaks in my data. Hence generating an average produces a result which isn’t particularly helpful.

I appreciate the averaging is done to reduce the volume of data being stored (and transferred during a download). Also, too many data points in a graph is likely to be a problem. However, I’d be interested to know if there is a way to download the raw data rather than the averaged data.


#2

Hi @paul2,

The good news is that we should have all of the data points we’ve logged in our DB still, what you’re getting from the ‘download data’ button is just a quirk of the design of those chart widgets. We’ll be adding a data tab that has better access to this for the end user, but until then, I can help show you how to construct a URL that should be able to pull a .csv of all logged data points (or, with your credentials, help extract data for you so you don’t have to fumble with URL construction).

I’m working with my developers on the format of this URL, since it’s changed since the last time I used it on this forum. I’ll post back here once I have something that works with our current architecture.


#3

That sounds good.
I’ve previously been using IBM’s Watson IoT service. It’s incredibly difficult to use and the connection to it seemed unreliable. Cayenne is proving to be much simpler and more reliable.


#4

Hi,

Are you able to let me know when you think the data tab might be available and in the meantime can you advise how to construct the URL.

Paul


#5

Hi @paul2, I have something that should work here, apologies in advance that it’s a bit convoluted, and for the delay in my response. It will allow you to query for specific data points until such time as the data tab is in place (I don’t have any information on a release for that feature at this time).

To extract more detailed sensor data, try the following:

  1. Install the Postman Chrome App: https://chrome.google.com/webstore/detail/postman/fhbjgbiflinjbdggehcddcbncdddomop?hl=en

  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 ‘machine 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 BMP180 Temperature Widget on a Raspberry Pi on my account:

    https://cayenne.mydevices.com/cayenne/dashboard/raspberrypi/039a82c0-842a-11e7-a9f6-4b991f8cbdfd/settings/7fb622147dd6766ffe51680c3145e7643fa8d516

    In this case, 039a82c0-842a-11e7-a9f6-4b991f8cbdfd is my machine ID, and 7fb622147dd6766ffe51680c3145e7643fa8d516 is my sensor ID.

  4. Next you need to get the UNIX timestamp 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 Fri, 18 Aug 2017 21:23:25 GMT, so I’ll make that my end time. Using the site, it converts to the UNIX timestamp 1503091405. Let’s say I want to pull the last hour and 10 minutes of sensor data, so using Fri, 18 Aug 2017 20:13:25 GMT as my start time, that converts to 1503087205.

  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 JWT with a value of your access token from step 2
  • The 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://cayenne.mydevices.com/cayenne/api/history/get?scale=minute&machine_id=YOURMACHINEID&type=YOURSENSORID&start=STARTTIME&end=ENDTIME

    the scale value can be set to minute,hour,day,week,month,year, I’ve used ‘minute’ here for the most granular data. So using all of my examples in this post, my GET URL looks like:

    https://cayenne.mydevices.com/cayenne/api/history/get?scale=minute&machine_id=039a82c0-842a-11e7-a9f6-4b991f8cbdfd&type=7fb622147dd6766ffe51680c3145e7643fa8d516&start=1503087205&end=1503091405

  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 (in my case about 70 of them, one for every minute in the hour and 10 minute window I chose). To show the general format, mine looks like this: https://pastebin.com/ALDbUnjE if you’re curious about the weather in my office :slight_smile:

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


#6

[quote=“rsiegel, post:5, topic:4494”]

Hi,

Just trying to following the instructions. I got stuck at (2). Where can I find the ‘view’ menu on the Cayenne web dashboard. Could you provide a link to this?


#7

He’s referring to the View toolbar in the browser. Or you can just press F12 and click the Console tab.


#8

Hi,

I tried this but the response was
[]
for my devices.

I also tried the URL and token from your example. It returned some html rather than temperature data. Perhaps this won’t work because the token is not correct.


#9

I would expect that the token I posted above didn’t work as it’s unique to the account I used to build the example. In fact, I think I changed a few characters of it just out of a sensor paranoid internet security :slight_smile:

As for the [], I haven’t seen it with this example, but I remember when I was doing this some months ago (and the process was a little different) that I would occasionally get that response when trying to pull too much data at once. If you were going for a very large time range, it may be worth trying a smaller one to see if that pulls some data. I remember at that time that I had to break a few months of sensor history pulls into 3 or 4 pieces.

If its still not working for you, and you’re comfortable with me in your account, I’m happy to help assist with pulling info, or at least getting a URL that you can use for your account as a proven example. I know the process above is convoluted and I don’t want to burn you out probing around our raw systems, so just send me a PM with your account info and what sensor data/time ranges your interested in and I’m happy to spend some time pulling data or troubleshooting if I run into the same wall. You can always change your password before/after sharing with this form: https://cayenne.mydevices.com/cayenne/forgot


#10

Hi rsiegel,

Just started with Cayenne Dashboard and I’m very happy with the Interface and everything works fine, but (sorry!), Is there any news about downloading more data points? One measurement a day after a week of datacollecting is not that much.

Regards, Ron


#11

Follow the instructions in this thread


It seemed to work for me. A bit fiddly the first time but then it’s just a matter of changinjg the date/time or device.
Note that the unix time needs to be in milliseconds not seconds.


#12

I think Paul mostly covered it. I made a post for Raspberry Pi and Arduino (non-MQTT) connectivity as well here:

If you have any questions or trouble with the steps in those posts, please let me know.

Both of these processes are similar, and I fully understand that they are unwieldy and not ideal. Long term this sort of thing will be improved in the Cayenne UI so you just need to specify some time ranges/sensors and click “download”.

Since they are a little funny to work with, I’m offering my assistance to pull data for you, if you Private Message me with your account login/password and which sensor(s)/devices you want data for, and what time ranges. A fair warning that I’m on vacation Saturday through Sept 28th though – so if you want my assistance to pull the data let me know!