Geomap panel, how to join queries

I’m working on my dashboard for one week and I’m almost satisfied.
I’m trying to display the coordinates on Geomap but unfortunately it does not work as I want.
My config :
Grafana 9.4.7
Influx DB 1.8.10
Telegraf 1.26.0

I’m trying to display a map to show if some equipment are “pingable” or not.
I used the telegraf plugin (“[[]]”) to store the ping result in influxdb, and I created a measurement table with coordinates (series : url, description, latitude, longitude).

In influxdb :

show tag keys from coordonnees
name: coordonnees

show tag keys from ping
name: ping

In grafana, I created 2 queries :
A - SELECT “lat”, “lon”, “descr” FROM “coordonnees” WHERE $timeFilter
B - SELECT last(“ttl”) FROM “ping” WHERE $timeFilter

I tried to use “outer join” function with no success. The function don’t find the tag key “url”.

I’m sorry if my English is not perfect, I’m french.


Tu dois avoir un field commun, dans ton cas url, dans les 2 query pour accomplir un union. Just like any query of database there needs to be a common field to join on

Hi Yosiasz,

Thank you for your reply.
I have a common field,“url”, wich is present on the 2 tables, but the Transform “join by field” / “outer” just show 2 options: Time and TTL. It doesn’t show “url”.

The queries :

The transform function :

are you sure? I am not seeing it in the image? maybe too early and need caffeine

It’s my first transform function, I’m really dumb.
Do you mean that the “url” key must be in the “select” of my queries if I want to use it in the transform function ?

1 Like

You are not dumb. Yes ut has tonbe selected otherwise it cant know about it

Also you can union in the query itself instead of deux different queriee

Also url seems such an odd column to join on. Do you not have another value like deviceid or something?

Ok I think that I got the problem.
The “url” in the table “coordonnees” is a field key.
The "url in the table “ping” is a tag key.

The problem is that the function “[[]]” of the telegraf agent is not editable.
How can I convert a tag key to a field key with grafana ? An other transform function ?

it works !
The prebuild “[[]]” function of telegraf is not editable, so I wrote an other script.

For others who would like to do the same : The script make a “ping” every 1 minute (via cron) and push the results (fields “url” and “status”) to the influxdb database.
The 2 measurements, “coordinates” and “status” have now 1 common field key : url.
An outer join in grafana is now possible with this common field.

Many thanks to yosiasz for his advices :slight_smile: :+1:

Tres bien champion. Envois moi une douzaine d’eclair

1 Like