WorldMap - w/ MySQL DB

Hi all,

I'm trying to get my DB stats on the wroldmap plugin, but i'm using MySQL query.

AS you can see my query :

SELECT
UNIX_TIMESTAMP(when) as time_sec,
(
SELECT FORMAT( COUNT( * ) , 0 ) AS count FROM logged
) as value,
gateway as metric
FROM logged
WHERE $__timeFilter(when)
ORDER BY when ASC

I do a count on my table logged to get all user connected but i don't have column to identify the location however i've "gateway" column which identify the gateway where user is logged but doesn't work.
How i can use my column gateway as location (France, Espagne,...)

Thanks in advance.

[daniellee]

what does the response look like, is there data for the gateway as metric field?

You can see the response and the generated SQL on the metrics tab:

I Changed my SQL request just with a count

SELECT FORMAT( COUNT( * ) , 0 ) AS count, gatewayas metric FROM logged;

[daniellee]

@SlodeSoft I don't understand your query at all - that returns just a single value? Where is the field named "metric" that you have specified as Table Label Field? That is not timeseries data as it has no datetime field.

For i resume i get a count from my table which means a number of user logged !
I've a column in the table which identify the gateway where the user is logged, i want use this column to identify the countires or city, but maybe it's the wrong way !

[daniellee]

Ok, I read through everything you wrote again.

What sort of data does your gateway column hold? Is it a string like a country code (for example FR for France) or is it a geohash?

Gateway column holds Geohash values

If it is a geohash, then you can use "Format as Table". I have not tested this with MySQL but should work - see here for docs.

Gateway column holds string values

If it is a string then it needs to be translated into a location (latitude and longitude). There is no magic here. There are some built-in ways to map the string to a location: country codes (with 2 letters and 3 letters) and states.

You would use Timeseries format in this case.

You need to return 3 fields from your mysql query:

• time_sec
• value
• metric

An example query (although you will probably want to use a query with a group by):

SELECT
  UNIX_TIMESTAMP(`timestamp`) as time_sec,
  `value` as value,
  country_code as metric
FROM worldmap
WHERE $__timeFilter(`timestamp`)
ORDER BY `timestamp` ASC

And the options:

What sort of data does your gateway column hold? Is it a string like a country code (for example FR for France) or is it a geohash?

This is the name of the gateway where the user is logged. As gateway1 or gateway2...

[daniellee]

And do you have coordinates for the gateways - latitude and longitude?

No it's just the name of the gateway, it's not possible to translate the name as a city or Country directly in grafana ?

[daniellee]

It is possible but you will have to do the translation - either programatically or manually.

For example, the plugin includes some built-in translations for country codes and US States which I generated programatically and with some regex to create json files. It also includes a probes json file (that we used to use for our worldPing app) that I created by googling city names and getting their latitude and longitude.

There are some API's and location databases that can maybe do this automatically (this is not built into the plugin):

• https://www.maxmind.com/en/geoip2-databases
• https://developers.google.com/maps/documentation/javascript/geocoding

Thanks you so much for you help Danielle. i will read thoses documentations.

have a good week-end !

Oh god, i forgot i 've a table with a column country

MySQL Request

SELECT
UNIX_TIMESTAMP(when) as time_sec,
(SELECT COUNT(DISTINCT country) as value),
country as metric
FROM login_old
WHERE $__timeFilter(when)
ORDER BY when ASC

You need to return 3 fields from your mysql query:

time_sec
value
metric

My Columns :

when : 2017-10-21 12:53:00
value : i need to use a count, it's right
metric : my column country

Hi daniellee,

My other query with a real column "country"

But on the map i don't have any point :(

With a Time Series :

[daniellee]

It matches on Country Code not name. Can you join to a table with Country Codes (or add a column with country codes)? The plugin has built in support for 2 or 3 letter codes.