World Map Panel: Show only last location

Hello guys,

I use an InfluxDB time series database and I’m trying to set up a World Map that displays the last location of some devices, but it shows every location recorded over time.
Unbenannt 1
As you can see on the screenshots I set FORMAT AS to Table and in the World Map Settings I set it to Table and current.

PS: I also added the “last()” Selector to the longitude and latitude field

Thank you in advance

Best regards Tobias

I tried a lot in the past few weeks to get it work and noticed something interesting.
For testing purposes, I tried to get the last location from only one device but there is no point on the map showing up.

As mentioned before I use the “last()” selector in all fields but then multiple points from one device gets displayed. Now i used the reduce transformation with last(not null).


As you can see in the image the data in the panel is correct, but it dont get displayed.

Does anyone has an idea why this is like it is and if I maybe forgot something?

If I try this and deactivate the transformation still multiple points on the map are getting displayed but I want only the last point to show on the map

@alsargent, any ideas?

Thanks for tagging me @dianapayton.

@tobias2001 Influxer here, though I’m just an InfluxQL beginner. One of my mantras when working on technical issues is, “try a lot of stuff”. In that vein, can you please share what you get when you use last() on just one of each of the columns (lat, long, error, and device)? You can also try last(*).

When sharing, please share both the query and a snippet of results as text, rather than an image. That makes it easier for me to share with my teammates for ideas.

In case you haven’t already studied the doc on last(), here you go: https://docs.influxdata.com/influxdb/v1.8/query_language/functions/#last

If you’re open to using Flux, it has a last() function that works well: https://docs.influxdata.com/influxdb/cloud/reference/flux/stdlib/built-in/transformations/selectors/last/

@alsargent & @dianapayton

Thank you for your help and quick response

If I try last(lat)

SELECT last(“location_lat”) AS “latitude”, “location_lon” AS “longitude”, “_65226_0_0” AS “error”, “device_id” AS “ID” FROM “consumer” WHERE (“topic” =~ /^device/) AND timeFilter GROUP BY time(__interval) fill(null)

or last(lon)

SELECT “location_lat” AS “latitude”, last(“location_lon”) AS “longitude”, “_65226_0_0” AS “error”, “device_id” AS “ID” FROM “consumer” WHERE (“topic” =~ /^device/) AND timeFilter GROUP BY time(__interval) fill(null)

I get 11 points on the map.

With last() on each column I get 43 points.
So a little better I guess :smiley:

When I use last(*) nothing gets displayed probably because there are more columns than I used

SELECT last(*) FROM “consumer” WHERE (“topic” =~ /^device/) AND timeFilter GROUP BY time(__interval) fill(null)

Tobias, thanks for iterating. To continue in that vein:

The snippet AND timeFilter seems odd to me. There’s no expression along the lines of AND timeFilter = something. What happens when you remove AND timeFilter?

Also, what happens when you remove the WHERE clause, when you remove the GROUP BY clause, and when you remove both WHERE and GROUP BY?

The bigger idea here is to start with a simpler query and work up from there.

Separate topic: regexes are slow at scale. So, if you can add a tag to remove the need for a regex, that will help if you have a lot of data.

I tried the things you wrote and multiple different combinations, but nothing worked. :frowning:
Im not sure if I get this error because the query is wrong, but maybe because my world map settings are not right.

Is it correct or am I overlooking something?


Hi Tobias,

A couple of ideas:

  1. Is your query delivering expected results?

– If not, go into InfluxDB and make sure your query is correct (delivers expected results) before using that query in Grafana
– If yes, then I’m guessing the issue is due to the map settings. (Sorry, I’m not a Grafana expert so I can’t comment on the screenshots you shared).

  1. If you need help tuning your InfluxQL queries, reach out to the InfluxDB community at influxdata.com/slack/ and https://community.influxdata.com/

I think i got a solution.

I changed the text edit mode an tried some things.

SELECT last(“location_lat”) AS “latitude”, last(“location_lon”) AS “longitude”, last(“location_alt”) AS “metric”, last(“device_id”) AS “name” FROM “consumer” WHERE (“topic” = ‘/devices/’) AND timeFilter GROUP BY time(__interval) fill(null)

With this query I get multiple points on the map as mentioned before.

If I delete everything after devices

SELECT last(“location_lat”) AS “latitude”, last(“location_lon”) AS “longitude”, last(“location_alt”) AS “metric”, last(“device_id”) AS “name” FROM “consumer” WHERE (“topic” = ‘/devices/’)

I get only the last location by time.