Week 8: Graphing with Grafana

This week introduced us to Grafana, an open source platform for visualizing our time series data. I ended up using data from the InfluxDB for no particular reason except that I happened to click on that first. This program makes it so easy to query data, but it certainly helps that we learned how to do it by scratch of the past couple of weeks. Some of my graphs are below. I could play with these all day.

Click to enlarge

Of course I went for the red, green, and blue sensor readings first, creating heatmap and gauge visualizations for both.

I set the date format of the heatmaps to time series buckets and captured hourly averages, displayed above for the past seven days. The white bands mark when lights are out and I’m likely sleeping or the sun when down before I got home that day. Here’s the query for red (which is basically the same for green and blue):

SELECT mean("value") FROM "red" WHERE ("device" = 'device_en') AND $timeFilter GROUP BY time(1h) fill(null)

The gauge show the average reading from the past 15 minutes:

SELECT mean("value") FROM "red" WHERE ("device" = 'device_en') AND $timeFilter GROUP BY time(15m) fill(null)

Click to enlarge

Here are the average hourly readings of all three colors for the past seven days displayed as a line graph:

SELECT mean(\"value\") FROM \"red\" WHERE (\"device\" = 'device_en') AND time >= now() - 7d GROUP BY time(1h) fill(null);SELECT mean(\"value\") FROM \"green\" WHERE (\"device\" = 'device_en') AND time >= now() - 7d GROUP BY time(1h) fill(null);SELECT mean(\"value\") FROM \"blue\" WHERE (\"device\" = 'device_en') AND time >= now() - 7d GROUP BY time(1h) fill(null)"

Click to enlarge

I also graphed the new lux and color temperature values that I started recording over a week ago. Again, hourly average readings for the past week. Interesting that the highest lux values are recorded during the afternoon when color temperatures are at the lowest. Except on March 21st—did I keep the drapes closed that day? Grafana’s query inspector:

SELECT mean(\"value\") FROM \"lux\" WHERE (\"device\" = 'device_en') AND time >= now() - 7d GROUP BY time(1h) fill(null);SELECT mean(\"value\") FROM \"colorTemp\" WHERE (\"device\" = 'device_en') AND time >= now() - 7d GROUP BY time(1h) fill(null)

Click to enlarge

Finally, the min, max, and mean daily readings for temperature and humidity since February 17, 2019. You can see where three days when the data logging stopped in mid March, and also when the temperature dropped below 40 degrees Fahrenheit on March 4th because I left the window open too much one night. Here’s the query from Grafana’s query inspector for temperature (basically the same for humidity):

SELECT min(\"value\"), mean(\"value\"), max(\"value\") FROM \"temperature\" WHERE (\"device\" = 'device_en') AND time >= 1550463093160ms GROUP BY time(1d) fill(null)