Friday, February 9, 2018

Grafana: Getting close to having something nice

I spent a few hours getting a Pi 3B running properly for this Grafana project. I actually think it is going to worth the effort. Grafana seems to work well and I have all the code on my own machine.

What I had to do was first set up a usb drive for the machine so I didn't depend on a silly SD card. I went into this with my teeth clamped down because previously it had been a pain to implement <link>. This time, it was a piece of cake.

What the folk did at Raspberry Pi was take the idea of a usb drive seriously. You enable a bit in the configuration, read the SD card you already have running into a file, write it out to the usb drive, plug it in and boot the Pi. OK, it's a little bit more than that, but not much.

I followed the instructions for setting the bit that allows boot from usb drive in the instructions on the Pi site <link> and found out the bit was already set. Then I remembered doing it when I was experimenting before. Then I took out the SD card, stuck it in my laptop and copied an image to a file. Next, I plugged in the usb ssd I already had from last time and copied the image over to it.

I walked across the room and plugged the usb drive into the PI, plugged in the power and went back to the laptop. Putty connected to the Pi on the first try. Notice I didn't say anything about plugging in an SD card?

Nope, the machine is running just fine without an SD card at all. You do realize that this makes the Pi into a full blown computer that you can actually rely on! Yep, this may justify making an enclosure for the combination so it looks pretty on the shelf. Right now it looks a little forlorn over there:

Looks a little strange hanging by its wires doesn't it? Notice the unopened Echo Dot boxes right by it? That's for another project I hope to get to some day.

I created a special user in the database that can only read data from certain tables to protect against the scary "SQL injection" attacks by hackers so my database is OK. I forwarded a port to the machine from my router so it can be seen from outside when I finally trust it enough to do so; I may be ready to show this thing off in a few days.

Then I spent a little time adding a few new items to the Grafana dashboard so I had more things I could check on just by glancing at the display. It looks like this right now:

I have the appliance power usage and battery level of the room temperature sensors charted so I can follow them over time. There's other things I can chart for fun and some other indicators that would be nice to see from time to time, but I think I'll use this arrangement for a while to see how it feels.

This entire project has been a success. I guess it was time for my readers to nudge me again about using some of the public tools. Remember this all started when I wrote about Xively a little while ago <link>. Maybe I'll look at Home Assistant next.

Or maybe I'll just make cool charts for a week or two.


  1. You can try out ngrok to set up a secure connection into your network instead of using port forwarding With the right settings, port forwarding will be sufficient though.

  2. How would you keep, say 3 months of data, in a SQLite database? What would the process be to rotate data out?

    1. I did this with sqlite for a couple of years and the disk activity destroyed an SD card almost every two months. That was a major factor in both getting a NAS and installing small (40M) ssd's.

      But that doesn't answer your question. What I did was to create a record on the database that held the reading, date-time and maybe something else. Then, I just added a new record for every time I read the data. If I wanted it, I just read the records for the time span I wanted. It worked well.

      If you want to trim it to the last few weeks or months, it's just a delete of the records from the beginning to whatever date you want. There's a ton of posts out there that have examples.

      If you want to keep it really small and easy to read for time-series stuff like power usage or temperature, there's specialized high throughput software designed just for that. If you go look at the grafana home page, there's a list of these things. They're not data bases, so you can't do general inquiries or joins of data etc. But, they work really well for charting data.

      They're cool.

  3. I actually use my Pi and a sql server database running on a crummy old laptop to minimize power consumption. I use the laptop as a public share and a print server anyway so no loss there. I'm cursed and blessed with Microsoft. My msdn license gives me access to dev goodies and Windows 10 IOT core on my Pi with c# makes some things easy while others you would expect to be easy are a bear. Do you use your Iris door sensors as a temperature input as well? I'm battling some strange addressing problem but I want to eventually do whole house climate control louvers and such eventually because I've got a 70's house with really lame insulation.

    1. Those Iris door sensors with the temperature sensor are not only almost useless for temperature because of inaccuracy, they're a bad idea. You put these things on outside doors and expect them to reflect the room temperature? Not here in the middle of Arizona.

      They do work pretty well for sensing doors though.

    2. Just in case, I posted an edit to your temperature algorithm for those sensors here:
      If you are using what is on the page that would be why. I kept getting about 80 degrees and live in Ohio so I investigated. It may be worth a look. It's in c# tho.

    3. I gave up using the temperature portion of the sensors because of where I live. On inside devices they could be useful, but here in the desert, measuring temperature with a window sensor is enough to make you pull your hair out. Behind the curtains, it can be as much as 25F higher that the actual room temperature. Folk in really cold weather probably have a similar problem.

      But, for inside the room, your patch (thanks again) could be great.