New to Influxdb Grafana query

Grafana InfluxDB
,
1

I’m trying to figure out how to set a query to display a kWh usage over a period of time or the time period set. I have a cumulative Wh graph using this query but would like a single number of usage over that period. Basically the finish cumulative minus the start cumulative.

The graph is using this query

from(bucket: "buckett")
  |> range(start: v.timeRangeStart , stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "kwh")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> yield(name: "last")
2

Welcome @myozone

You can do this by creating two tables: one with the first (i.e. start) value, one with the last (i.e. finish) value.

Something along the lines of this:

First = from(bucket: "FirstBucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "kwh")
  |> first()
  |> yield(name: "First")

Last = from(bucket: "FirstBucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "kwh")
  |> last()
  |> yield(name: "Last")

union(tables: [First, Last])
|> difference()
1 Like
3

Also checkout spread()

1 Like
4

After I posted, I thought “there has to be a function for this”. Thanks @yosiasz

2 Likes
5

Yeah avoids 2 queries performance but then again lets wait and see what op says, might not work for @myozone

1 Like
6

@myozone

Also possible to use a Grafana transformation. There are many to choose from, incl. the difference between the first and last values. The “range” also might work.

image

2 Likes
7

First off thank you for all the replies, I’m really new Flux queries and cheated when I discovered with my example Influxdb’s ‘Script editor’ copied into Grafana.

I couldn’t get @grant2 first example to work, generated error ‘not found: failed to initialize execute state: could not find bucket “FirstBucket”’ which I change to my bucket confusing I called it buckett had the error not found: failed to initialize execute state: could not find bucket “Buckett”. :confused:

However, I did get Transform to work ! Is there anyone way of showing yesterday and the day before etc in a fixed stat box?

UPDATE
I got @grant2 example to work - case sensitive but came up with 0 as it looks like it taking one from the other at the same time period. I’m not sure about Firstbucket and Lastbuckett where those would come from.

8

what did you find for this in the documentation? and what have you tried?

9

I only tried @grant2 examples and looked at the docs but didn’t know how to implement your spread link. It took me a few days even to get Node-red to send data to Influxdb and I’ve been confused with the ‘Random walk’ data that appears in Grafana thinking it was my data :grinning:

10

those are just examples he is using. you would have to plugin your bucket names, not just copy/pasta and hoping that it works :wink:

11

I did :flushed:, The bucket I’m using is buckett and the measurement is kWh so just one bucket name rather than two names

I did say I was really new to Flux :grinning:

12

please go to influxdb query on port 8086 and run the same query there and post back what you see.

And check the following out, you will get a lot more mileage from taking this awesome course.

1 Like
13

How do I do a query on port 8086 docker, portainer, telnet ?

14

that would be something unrelated to grafana but more of a docker/networking question?

Check this out

15

Sorry, I think you misunderstood me, I’m using Grafana and Influxdb on Docker already just how do I do the port 8086 query

16

Ip.address.of.influx:8086

In your browser

17

I misunderstood entirely, I assumed a cli query rather than the gui. The result of the query below gives in Influxdb ‘No Results’ as it’s taking the same value from each other hence the ‘No Results’

First = from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "kwh")
  |> first()
  |> yield(name: "First")

Last = from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "kwh")
  |> last()
  |> yield(name: "Last")

union(tables: [First, Last])
|> difference()
18

I thought your bucket name was buckett?

19

Also, try performing each separate query in Influx Data Explorer to make sure it works before doing the union.

20

And expand the time range thats another gotcha in the influxdb explorer GUI