Hi, I am using a worldmap panel on Grafana v5.4.2, my data source is a mysql database, and there I have some ip address, city and location string with latitude and longitude, I would like to display the city name of the ip addresses, the value would be the ip address count belongs to a city, the following is my sql:
SELECT
any_value(city.city) as metric,
count(ip.loc) as value,
CAST(substring(ip.loc, 1, INSTR(ip.loc, “,”) - 1) AS DECIMAL(10,6)) as latitude,
CAST(substring(ip.loc, INSTR(ip.loc, “,”) + 1) AS DECIMAL(10,6)) as longitude,
UNIX_TIMESTAMP(now()) as time_sec
FROM ip
inner join city on city.ctid = ip.ctid
WHERE ip.iptype & 2
group by ip.loc
I can get response with the sql query, the response is something like the following, but nothing display on the map:
xhrStatus:“complete”
request:Object
method:“POST”
url:“api/tsdb/query”
data:Object
from:“1541896435220”
to:“1547534884686”
queries:Array[1]
response:Object
results:Object
A:Object
refId:“A”
meta:Object
series:Array[516]
0:Object
1:Object
2:Object
3:Object
name:“巴西 巴西 value”
points:Array[1]
0:Array[1,1547534884000]
4:Object
5:Object
6:Object
7:Object
…
the following is the ruling result from mysql:
±-------------------------------------------------±-------±-------------------±-------------------------±-------------------------------+
| metric | value | latitude | longitude | time_sec |
±-------------------------------------------------±------±--------------------±-------------------------±-------------------------------+
| 巴西 巴西 | 1 | -14.242900 | -54.387798 | 1547535460 |
| 巴西 圣保罗州 圣保罗 | 1 | -23.550520 | -46.633309 | 1547535460 |
| 新加坡 新加坡 | 16 | 1.352083 | 103.819836 | 1547535460 |
| 越南 越南 | 1 | 10.768451 | 106.694363 | 1547535460 |
| 柬埔寨 柬埔寨 | 7 | 12.547730 | 104.983856 | 1547535460 |
…
can anyone address me what’s going on?