InfluxDB+Grafana persistence and graphing

influxdb
graphs
grafana
Tags: #<Tag:0x00007f014639d9a8> #<Tag:0x00007f014639d868> #<Tag:0x00007f014639d5e8>

(Mark) #609

Hi, I am relatively new to Openhab but I thought I’d share something here to inspire others.

A few months ago I really didn’t know anything about Openhab, Javascript or any programming, and still don’t :rofl:. I don’t know much about Linux either nor am I comfortable with and console commands; but nevertheless with a lot of reading and support here I achieved something I am rather pleased with. So I’d thought I’d share and say thank you.

The screen shot below is taken from Grafana but the dials also shows dynamically in Habpanel. I also have the usual plots and charts for all my room thermostats (Openwebnet binding) but here I show some nice Grafana gauges displaying the dynamic data from my Mobile-Alerts wireless rain ,wind, temperature and humidity sensors. These sensors enable a relatively inexpensive wireless weather station to be built or monitor things like garage, fridges, fish tank, pools and ponds etc. They can be found under various brand names. Discussion: mobile-alerts-and-openhab

The dial pointers are animated and move in a nice slow steady manner to their position. I get email alerts and push notifications if its very windy or it started to rain.




I did it all using Openhabian on a Raspberry Pi 3b.

I got the sensor data from the internet using a combination of Mobile-Alerts REST API query and HTML caching of the Mobile-Alerts sensor web page. Which method I used depends on the sensor type. Only the pro-sensors have the option of the REST API.

In Grafana I used the D3 Gauge panel plugin and implemented Value and Tick mapping for the wind direction gauges. The rain total gauge is, like the charts, also dynamic and adjusts its value depending on the time period selected. To achieve this I used SELECT mean, difference and cumulative sum in the metrics as the mobile-alerts data is just a running total of rain fall since the sensor was activated.

So, thank you again everyone who helped me achieve this. :grinning::grinning::grinning:

ps Just noticed an issue with the average wind direction. North directions are around 15, 0, 1 in number terms. So, when the wind is northerly and flipping between 0 and 15 results in average of 8 ie south grrr. Should work for other wind directions but North is a problem. Any ideas how to fix that would be appreciated. …Looks like I have to some maths and convert the wind angle to x and y components. Then average these x and y values and convert back to angle. I could use JavaScript to compute the x and Y using SIN and COS. Persist these values into influx and get influx to transform the average back to angle using ATAN I don’t see ATAN appearing in Grafana metrics options but there is talk of it for Influx … hmmmm.

That’s for unweighted average. For weighted then I need to factor in the speed. I think unweighted would be OK now .

Edited to add … it seems I can’t compute accurately the average wind direction due to Openhab and Influx limitations. Influx can’t do calculations with different measurements and Openhab can’t combine items into one measurement for persistence. I got most working but can’t compute the final part avgX / avgY


(Nakh Home) #610

Hi

Do someone has an idea why the image is tiny?


(Rich Koshak) #611

Can’t you create a new Item populated with a Rule? That’s the standard approach.

Often you can also put all the measurements into a Group using

Group:Number:AVG Measurements

The state of Measurements will be the average of all its members. You can then persist Measurements to InfluxDB and chart it normally.


(Mark) #612

Rich, I am often coming across your good ideas. That to me sounds like another one :slight_smile:


(Mark) #613

Having thought about it I don’t think that’s going to work. :slightly_frowning_face:

I will explain the problem in more detail.

Ignoring wind speed weighting for now, the way to calculate average wind direction is not to use angles but to break down the wind direction into its component W_E and N-S directions, ie +/- X and +/- Y and then average these. This avoids the problem of for example 0 and 359 degrees both North averaging to 180 ie South. With the average X and Y dynamically calculated I would then need to convert them back into a final average wind direction like this:

atan ( avgX / avgY )

The averages are the dynamically calculated ones that Influx can do. I don’t think that Openhab group Items average is going to work in this case. Influx can also perform the atan function, I tested it, but it can’t divide X measurements by Y measurements. And I can’t get Openhab to persist the x and Y components as one measurement set as Influx fields, tags which could have solved the Influx divide problem. (This part I am learning so I could be wrong)

Thanks to your help elsewhere with type conversations, which I always struggle with, I got the code below to update Items with the latest X and Y wind direction components, and then persist these to Influx.I tried to get Influx to do the finally conversion back to an angle but as expected it didn’t work.

In my case the wind direction angle is in 22.5 degree steps from 0 to 15

logInfo("Mobile Alerts" , ' direction number = '+ MobileAlertsWeatherStationWindDirectionNumber.state)
val Number wdn =  MobileAlertsWeatherStationWindDirectionNumber.state.as Number
logInfo("Mobile Alerts" , ' wdn = '+ wdn)

val radians = Math.toRadians(22.5 * wdn.doubleValue)
val wdx = Math.sin(radians)
val wdy = Math.cos(radians)
MobileAlertsWeathStationWindDirectionX.sendCommand(wdx) 
MobileAlertsWeathStationWindDirectionY.sendCommand(wdy)

The average wind direction gauge is just a bit of fun. I like to see the animation of the dial pointers moving slowly into place :rofl: The chart below is actually a more informative way to display what the wind has been doing. You can easily see the wind changed to a general new direction or if its just random gusts. You can also see the 0,360 problem as the data appears to split but actually is the same wind direction.


(Rich Koshak) #614

Probably not if you need to handle special cases. So you will need a Rule to calculate the average and put the result in an Item that gets stored in persistence.


(Thomas Bail) #615

I am not sure if someone has posted this before, but i find this i a very handy tool to work with Grafana.

Just to easily drop some series or check data for some minutes it works fine for me.


(Christoph Wempe) #616

Looks very usefull.

Thanks.

Maybe it is worth mentioning it in the first post.


(Mark) #617

Hi,
I spent all morning trying to get rid of some bad data from the database. I couldn’t get anything to work and in desperation I dropped the database, which did work. Influx just appeared to ignore me. No errors,no response. Grrrr.

Then I found out I was specifying the time wrong in my queries. Noob moment again. I was missing the T and Z in the time stamp!! Doh!!! In my defence most searches on using Influx CLI do not show the time queries written that way but with spaces.
eg
time >= ‘2018-08-30T10:54:00Z

What do they mean?

Also, discovered that this CLI command makes all the influxdb timestamps human readable :slight_smile: All times are GMT aka UTC

From Influx CLI

precision rfc3339

Or from console

influx -precision rfc3339

(Mikael Gustafsson) #618

That’s an ISO standard way to declare a date and time. I think the standard is ISO 8601. The T is a separator for the date and time parts and if I remember correctly the Z stands for local time zone. Something with time zones anyway.


(Nakh Home) #619

Please, could you export it to json and share it

G


(Mark) #620

I could except it looks different now :slight_smile: Always tinkering :rofl:



(Rich Koshak) #621

Z without anything after it means UTC, or what is commonly referred to as GMT. To represent your timezone you would append a + or - and the number of hours offset. For example, right now my timezone (Mountain Time Zone in the US) would be represented using Z-06

ISO 8601 is a very complex standard. People do not realize how complicated date/time values really are.


(Baki Med) #622

Hello everyone, I encountred this problem while trying to integrate my Grafana dashborad in my sitemap


This is my command in default.sitemap, please tell me what I’m wrong with?

Image refresh=60000 url=“http://192.168.137.60:3000/d/5d4fCZmRz/new-dashboard-copy?edit&orgId=1&from=1535703929991&to=1535714729991

thanks you :slight_smile:


(illnesse) #623

btw, Discrete is a pretty useful plugin for timelines and such


(Mark) #624

Has anyone tried the free hosted Grafana? Please comment on advantages and disadvantages.

Thx Mark


(Udo Hartmann) #625

Do you really want to write your Data to the cloud? The whole point of openHAB is, to be independent to the internet connection.


(Rich Koshak) #626

Just to elaborate on Udo’s response. In order for the hosted Grafana to work it will need access to your InfluxDB or what ever database you are using. This means also hosting your persistence database in the could somewhere or opening a port on your firewall to expose your database to the Internet.

Given that a whole lot of information can be gained by looking at the sorts of data stored in your OH persistence, do you really want to expose that to the Internet or entrust it to some company?


(Tom) #627

Hi, backup setup for thinks, rules etc is easy. But how to backup influxDB easy way? Do you just backup some files or need some export?
thnx a lot


(John Schmitz) #628

There’s some documentation here on backup: https://docs.influxdata.com/influxdb/v1.6/administration/backup_and_restore/
Looks like you could use this:

influxd backup -portable <path-to-backup>

I’m planning on moving my database so I’ll be trying this soon.