Lookup of device IDs for legend

Grafana MySQL
, ,
1

Grafana version: 8.3.4, OS: Debian Bullseye

We have a bunch of sensors (e.g. 25-30 ESPs running Tasmota) sending different measurements to MQTT - Influx - Grafana; works real nice. In the legend in the moment the ID of the devices is displayed:

TasmotaLookup1
TasmotaLookup11011×384 64.9 KB

TasmotaLookup2
TasmotaLookup21021×286 21.3 KB

My end users would like to see a more human readable description like “machine x/hall y” instead of “DF6B09”. Of course I could to the lookup on the import side but that would store a lot of redundant text for millions of measurements and arises problems if one of the descriptions will change (which for sure will be happen…).

I tried to add a mysql table with the mappings to Grafana (works), to define a variable (works; let’s me choose between “all devices” and a selection of devices using the human readable description) but I have no idea how to replace the IDs in the legend with the lookup.

Any help would be appriated. :slight_smile:

2

Welcome to the Grafana forum.

Have you considered using overrides to simplify the display names, like this:

image
image1694×1106 159 KB

1 Like
3

Thank you Grant, works like a charme. :slight_smile: Next step: it would be a real burden to do that manually in many dashboards/panels for dozens of sensors - particularly because I could to all the mappings automatically. So I dig “a little bit” into the Grafanas data structures and made a proof of concept Python-script which I could/would extend to automize the process of defining the overrides. If anybody is interested:

import sqlite3, json

con = sqlite3.connect ("/var/lib/grafana/grafana.db")
cur = con.cursor ()

for row in cur.execute ("SELECT data FROM dashboard WHERE slug=\"tasmota-esps\""):
	# print ("row:", row)
	dashboard = json.loads (row [0])
	# print ("Dashboards:", len (dashboard))
	# print (json.dumps (dashboard, indent=2, sort_keys=True))
	cnt = 0

	for panel in dashboard ["panels"]:
		# print (json.dumps (panel, indent=2, sort_keys=True))
		# print ("panel", cnt, panel.keys ())
		# print ("fieldConfig", cnt, panel ["fieldConfig"].keys ())
		print ("overrides", cnt, panel ["fieldConfig"] ["overrides"])
		cnt += 1

	print ("Panel 4:")
	print (dashboard ["panels"] [4] ["fieldConfig"] ["overrides"] [2])
	print (dashboard ["panels"] [4] ["fieldConfig"] ["overrides"] [2] ["properties"] [0] ["value"])
	dashboard ["panels"] [4] ["fieldConfig"] ["overrides"] [2] ["properties"] [0] ["value"] = "Test Rechteck"
	print (dashboard ["panels"] [4] ["fieldConfig"] ["overrides"] [2] ["properties"] [0] ["value"])

	# print ("OLD:", row)
	# newrow = (json.dumps (dashboard, separators = (",", ":")).encode ("utf-8"), )
	newrow = json.dumps (dashboard, separators = (",", ":")).encode ("utf-8")
	# print ("NEW:", newrow)

	con.execute ("UPDATE dashboard SET data = ? WHERE slug=\"tasmota-esps\" AND data = ?", (newrow, row [0]))

con.commit ()
con.close ()

Or do you have a better (and more “Grafana-like”) idea? Tnx!

4

That looks brilliant. Thanks for sharing.

I have seen other questions on this forum asking for an “automated” way of renaming their series to more “human friendly” text. I will see if I can find them and link to your solution.

1 Like
5

I’ve created a small Github-project with some additional information/ressources and the code: https://github.com/msedv/Grafana

I hope I’ll find the time to extend it to a generic version in the next days/weeks.

6

Hi @msedv it looks like you found a good solution! If you are still interested in dynamically mapping device IDs to device names stored in a SQL database or similar, that is also possible. I wrote a thread on it here: Using SQL data to label time series (via v7 transformations?). Although the last post in that thread is about me struggling to get it to work, it does actually work these days since the bug that I mentioned was fixed some time ago.

2 Likes
7

@svetb: thank you, I’ll have a look at it

@all who stumble about this post: in the already mentioned Github-project GitHub - msedv/Grafana you can now find two tools:

showgrafana.py: formatted output of the Grafana-Dashboard-definitions (JSON) to ease analyzing it.

parsegrafana.py: changing the labels/ids based on a lookup-table stored in parsegrafana.csv (sample provided). Could maybe also useful for other tasks where the Grafana-configuration should be changed programmatically.

Grafana Overrides.md: a more detailed and illustrated description of the overrides-definition lined out by Grant.

1 Like