Visitors volume geomap from web server log

I’m trying to replicate the visitors map which was demoed in this youtube video, unfortunately, this was done for an older Grafana version and I can’t get it to work with 9.4.7.

Here’s an example of the preprocessed log lines as they get dumped into Loki:

2023-04-15T18:04:56.574000 x.x.x.x FR France GET /index.php/topic,3074.40.html status:200

Here’s the query I’ve constructed based on the video:

sum by (country_code) (count_over_time({job="myjob"} | regexp "^(?P<date>\\d+-\\d+-\\d+T\\d+:\\d+:\\d+.\\d+) (?P<ip>.+?) (?P<country_code>.+?) (?P<country>.+?) (?P<method>GET|POST) (?P<url>.*?) status:(?P<status>.*)" [$__interval]))

With this, I can build a nice pie chart, so the overall input data seems to be ok.

But: I don’t get how I tie this into the geomap settings.

  • What’s the Data selection supposed to be? I get a list of Query: A (size: 5) Time, Value there in that dropdown?
  • Location Mode: set to Lookup - but the Lookup field dropdown says “No strings fields found” - what’s supposed to be in there?

I did try to understand the doc which refers to “the lookup field from your database query” - which would be country_code I suppose - but that doesn’t appear in the dropdown?

Appreciate any help!


The datasource might be different but the principle is the same. Here is an image to explain what I did using mock data with infinity plugin. Yes your data source is Loki but at the end you need the country code so that Lookup will have something to lookup :wink:

Ping back if you have any questions.

Thanks for the quick response! Maybe I should then just transform my logfile into a CSV with some Python code :wink:

Seriously: I just can’t wrap my head around the query I was copying from the video (which I admit don’t understand completely). On one hand it does include the country codes in the result data - but I can’t directly reference it from that lookup. Do I need a 2nd query? Or do I need to construct that query differently?

I notice video has country_code as legend?


i think you need to convert your query so that country_code is also returned by the query? I am not familiar with the Loki query language

Let me spin up a loki podman and see if I can emulate your data. might take a while though

@arminus You may get some hints from the blog post

And Video tutorial

1 Like

Well, that’s what confuses me. Apparently, the query does return country_code, otherwise the pie chart wouldn’t work (but I will have a look at the blog post below)

Great :slight_smile: The answer to my issue starts at 8:33 in How to build Website Analytics dashboard in Grafana | Step by step tutorial | Loki data source - YouTube - the data as returned by the query needs to be flattened which is done by a data transformation “Series to rows” plus a Group by metric / Value calculate total

Works like a charm, thanks for your help, really appreciate it!