Grafana InfluxDB - Help with queries and creating graphs and charts

Add-ons Other
,
6

Okay, now you’ve done it: You made me migrate my old Grafana chart to my OH3 instance (which was recreated from scratch) and Influxdb2 (and also upgrade Grafana from v5 to v8).

Since Influxdb2 uses “Flux” as query language, it works quite differently from what I described above for InfluxDB versions <1.8. Since I won’t remember how to do it in a few weeks, I decided to write it down for myself and others :slight_smile:

How to create a chart in Grafana with multiple items automatically fetched from Openhab by name:

My example uses a “State timeline” type chart to display the Online state (0/1) of some Wifi Devices, using the persisted state of some Openhab switch items. The items are identified through a naming scheme: All item names have the suffix “_DeviceState” (e.g. Wifiplug01_DeviceSatate, Wifilight04_DeviceState, …).

We will let Grafana find the items automatically using the common suffix. If an item with the same suffix is added in Openhab it will automatically be picked up by Grafana.

  1. Create a new Dashboard in Grafana
  2. Click the “Gears” icon top right to “open dashboard settings”
  3. Click “Variables” and “Add” to add a new variable
  4. Fill out the form like shown in the screenshot. Replace “Name” and “Label” with whatever suits you.
    Use the bucket name for your influx database instead of “openhab”.
    grafik
    grafik814×870 70.1 KB
  5. The “Regex” field is where you select your Openhab items. Here, it is a regular expression (indicated by the ‘/’ an beginning and end) to select all words ending in “_DeviceStatus”.
    At the bottom of the screen you will see the result as a preview if you click outside the field you are editing. (Here: EspGarageDoor_DeviceStatus, …)
  6. Click “Save” or “Update” once the preview shows the desired result.
  7. Go back to the dashboard and add a new panel. I used the type chart type “State timeline”, but "Time series should work, as well.
  8. Edit the chart panel (click the down arrow to the right of the panel title). Here you need to enter the query which retrieves the data from Influxdb. The query (in Flux language syntax) needs to look like this:
from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) => r._measurement =~ /^${AllDeviceStates:regex}$/ and
     r._field == "value"
  )

replace “openhab” with your Influxdb bucket name and “AllDeviceStates” with your variable name.
The 3. line is where the query differs from what Grafana has as a template: instead of == we use =~, instead of putting the search string in quotes we put it between slashes, to indicate it is a regular expression. Finally, the variable name is appended with :regex to tell Grafana that it shall “interpret” this variable as a regular expression, not as a list of words. (Note: The leading caret ^ and trailing $ are only there to make sure we compare complete words and not substrings. Usually not necessary, but for completeness’ sake…)

  1. The preview of the graph should already show your items. To make it look a bit nicer, we can now adjust some settings in the right-hand panel as shown in the next two screenshots. Note that some of this is specific to the “State timeline”, but here we can “play around” with the settings and just see what happens.
    grafik
    grafik459×792 38 KB
    grafik
    grafik345×509 35.3 KB

    Of particular interest is the field “Display name”. This is what is displayed in the legend of the graph. We want to show the Openhab label for each item here.
    Only needed if you want to show switches in a “State timeline” (as in this example) are the fields “Color scheme” and “Value mappings”. We need to set both as shown here to tell Grafana which color should be drawn for “ON” (1) and “OFF” (0) states.

The result looks like this:
grafik

The nice thing about this technique is that Grafana shows a popup menu where we can select which of the items we want to show. And if we add an item to Openhab it will automatically appear in Grafana.

Final note: If we don’t want the popup menu for selecting the items and instead want Grafana to always show all items, we can simply omit steps 2-7 and instead use a qurey string like this:

 from(bucket: "openhab")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) => r._measurement =~ /.*_DeviceStatus/ and
     r._field == "value"
  )

Instead of giving the variable name between the slashes we can enter a regular expression directly. If a selection menu is not required, this makes everything quite a bit simpler.

Hope this helps somebody. Particularly this application of showing several (many) Openhab items in one chart, generated automatically, is something I believe the built-in charts of Openhab ManuUI cannot (yet) do. Grafana doesn’t make it simple (IMHO), but at least it makes it possible.

Jens

3 Likes