I am running Influx 1.8 (and Grafana 7) on a Raspberry PI 4. Among other things, Influx receives datapoint from our electric vehicle when it drives or is getting charged. Influx receives data into various ‘measurements’, for example one measurement (with timestamp) each for latitude, longitude, odometer, speed, energy used, and charge added (while charging).
I would like to create a table of locations where I charged, with the total sum of energy charged at each respective locations, to be displayed in Grafana. The procedure I have in mind is
In the charge_added measurement, look for zeros (because every charge starts at zero) and then take the previous value > 0 (which was the charge added the last time). Unfortunately, the charge_added measurement receives multiple values per charge process, one per minute.
Get the lat/lng values with the same timestamp (rounded to 1min possibly) as the charge_added value.
Sum up the charge_added values, grouped by lat/lng values.
(possibly) label the lat/lng pairs in the table. Or use the map plugin and display the values on a map.
I think the main question is how to separate (group) the charge_added values by zero values, instead of time range. If I just “GROUP BY (time)” the charge_added, and then take last() or max(), I will miss multiple charge actions.
Nice to meet you digitally and an interesting use case btw. So let’s break the problem down and see what we can achieve together. Firstly I think the function you are looking for can be achieved in Flux and this is called cumulativesum. Before we get to that point we need to prepare your data.
I have made a crude attempt at recreating some sample data of your use case. From this data I then produced this flux query:
First I perform a pivot to sort our data into three separate columns (lat, long, power).
Since per route the robot will be stopping to charge at unique locations we can use these values to group our data (time is of no use to us here). I would also advise adding another field that contains a unique identifier for the job/task the robot was doing or it could just be the total times it’s taken this route. You could use this as part of the group (this would stop us from grouping previous runs together).
Next as you suggested we can take the last known value from that location which should be the total amount of power taken from that area.
Lastly we group our groups back into one table. Essentially a table of the last values for each group. We then perform the cumulativesum on the power column. This will provide the total power of all areas with the last row of the table.
I also highly recommend checking out the geo package for flux. This will help you group your coordinates even when they fluctuate.
and - wow, thank you for your elaborate answer. I really need to get into Flux, it seems to be much more than just a query language.
Currently I use InfluxQL mostly from Grafana, and this is my first problem: how do I get Flux support installed? I use Grafana 7.0.3, do I need to upgrade?
No problem at all. We like an interesting use case here at Influx so this is really took my fancy . So yes for your combination you would need to update to Grafana 7.1. Your Influx version is fine but Flux query needs to be enabled. To do this follow these instructions.
Can I suggest another route before we get to this point however? Since we are still unsure whether the Flux query solves your issue it seems silly to go to all this effort to upgrade. If you download the latest InfluxDB OSS or create an InfluxDB cloud account (the free tier will be absolutely fine as we are only experimenting with our data). Then load a sample of your data and run some tests through the Data Explorer. This acts as a great tool for learning and experimenting with Flux.
Actually, changing “sleep 1” to “sleep 5” helped do the trick as well. Thank you!
Now I can configure Flux connections to InfluxDB and these are successful.
However, no Flux query - even the simplest example queries - return any results in Grafana’s Data Explorer. There is an error if I stop influxdb, so I know the connection works. But nothing is returned.
What Flux query could I use for testing that definitively works on ALL databases regardless of structure and content, and returns someting non-null that I can verify?
I am ever so sorry for not getting back to you sooner. I missed the notification for your last message. Have you checked in your Grafana instance that you are using Flux? Please see below when creating Data Source: