Visitors volume geomap from web server log

Grafana Geomap Panel
1

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!

2

Welcome,

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:

image
image1864×702 137 KB

Ping back if you have any questions.

3

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?

4

I notice video has country_code as legend?

image

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

5

@arminus You may get some hints from the blog post

And Video tutorial

1 Like
6

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)

PieChart
PieChart1072×569 53.9 KB

Table
Table1069×593 52.3 KB

7

Great :slight_smile: The answer to my issue starts at 8:33 in https://youtu.be/cgu3wMOo03w?t=513 - 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!

2 Likes