Visualizando direcciones IP en un mapamundi con Grafana

/ / , , , , , , , , , , , , , , , /

Bueno, este documento es algo particular, ya que tenía en una tabla de MySQL una serie de direcciones IPs públicas, que me gustaría visualizarlo en Grafana. Básicamente os hablo de las visitas del blog 🙂 Tengo un WordPress y no tengo en mi poder la base de datos ya que está en un hosting externo… os dejo los pasos que seguí para conseguirlo, espero que os pueda servir para otras ideas.

Lo dicho, tengo una tabla de una base de datos donde tengo unas IPs públicas, que es el WordPress de este blog, pero vaya no tengo acceso directo a la BD ya que está en un hosting, ¿no? Tenía varias opciones, me imagino que WordPress tiene algún API que puedo atacar directamente o hacerlo de una manera más manual pero que me pueda servir para otras tablas que tengo por ahí.

Lo que me interesa únicamente es una tabla que tiene las IPs de los visitantes del blog, eso en WordPress se almacena si en wp_statistics_visitor si es que tenemos instalado en WordPress el Plugin ‘WP Statistics’. La única manera que se me ocurrió para exportar esos datos, fue… desde el propio hosting (tiene SSH habilitado),  crear un script y dejarlo en una tarea programada (mediante cron) que se ejecute a diario (sobre las 23:30). Este script como veis deja en un fichero txt (separado por comas) la IP pública y la fecha. Os dejo saca_visitantes.sh:

echo "SELECT IP, LAST_COUNTER FROM wp_statistics_visitor where LAST_COUNTER = CURDATE( );" | mysql -uUSUARIO -pCONTRASEÑA NOMBRE_BD | sed 's/\t/,/g' > web/bujarra.com/public_html/temp/direcciones_ip.txt

Un minuto más tarde, en otra máquina lo que hago es recoger ese fichero de texto e importarlo en una base de datos MySQL local, donde ya puedo jugar con esos datos. ¿A qué me refiero con jugar? Pues que gracias a ipstack podemos obtener la localización de las IPs públicas, ipstack nos da un API KEY gratuito de hasta 10.000 consultas al mes, por lo que de algo nos vale, o como es mi caso podemos generar tantas cuentas en ipstack hasta cubrir vuestra demanda 😉

Por tanto, con el siguiente script (que admito que se puede hacer mejor, pero es a lo que llega mi conocimiento…) podremos añadir en dos columnas adicionales a la IP, los campos de Latitud y Longitud. Así que por lo que podemos interpretar en el script, primero nos descargamos el fichero txt, luego lo importamos en MySQL; y al final lo que haremos es gracias a dicha API modificar cada línea importada y añadirle los campos Latitud & Longitud. Por si os sirve de algo, os dejo importa_direcciones_ip.sh:

#!/bin/bash
/usr/bin/curl 'http://www.bujarra.com/temp/direcciones_ip.txt' > /home/hector/bujarra_direcciones_ip.txt
mysqlimport --ignore-lines=1 --fields-terminated-by=, --verbose --local -uUSUARIO -pCONTRASEÑA -h SERVIDOR_MYSQL NOMBRE_BD bujarra_direcciones_ip.txt
TOT_LINEAS=`echo "SELECT COUNT(*) FROM bujarra_ips WHERE fecha = CURDATE();" | mysql -h SERVIDOR_MYSQL -uUSUARIO -pCONTRASEÑA NOMBRE_BD`
TOT_LINEAS=${TOT_LINEAS:8}
TOT_LINEAS=$(( $TOT_LINEAS - 1 ))

while [ $TOT_LINEAS -gt -1 ]
do
  IP=`echo "SELECT ip FROM bujarra_ips WHERE fecha = CURDATE() order BY IP desc LIMIT $TOT_LINEAS,1;" | mysql -h SERVIDOR_MYSQL -uUSUARIO -pCONTRASEÑA NOMBRE_BD`
  IP=${IP:3}
  echo "IP: $IP"
  curl "http://api.ipstack.com/$IP?access_key=NUESTRO_TOKEN&%20fields=latitude,longitude&format=2" > direcciones_ip.txt
  LATITUD=`cat direcciones_ip.txt | grep -o -P '(?<=latitude":).*(?=,"longitude)'`
  LONGITUD=`cat direcciones_ip.txt | grep -o -P '(?<=longitude":).*(?=})'`
  echo "UPDATE bujarra_ips SET latitud=$LATITUD, longitud=$LONGITUD WHERE IP = '$IP';" | mysql -h SERVIDOR_MYSQL -uUSUARIO -pCONTRASEÑA NOMBRE_BD
  echo "TOT_LINEAS: $TOT_LINEAS"
  TOT_LINEAS=$(( $TOT_LINEAS - 1 ))
done

Bueno, por si alguien se está preguntando qué formato tiene la tabla destino… es muy simple, tiene 5 campos, IP, fecha, latitud, longitud y timestamp, si necesitáis el código para crear la tabla os dejo este código create:

CREATE TABLE `bujarra_ips` (
  `IP` CHAR(20) NOT NULL,
  `fecha` CHAR(20) NOT NULL,
  `latitud` CHAR(20) NOT NULL,
  `longitud` CHAR(20) NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

Ahora que ya tenemos los datos cerca de nuestro Grafana lo tenemos chupado, ¿no? Tendremos ya un conector o Data Source creado contra la BD de MySQL y también deberemos tener instalado el Worldmap Panel, que como vemos en la pestaña de Installation lo instalamos con un simple comando. Tras estas premisas ya podremos crear dentro de un Dashboard un Panel, seleccionaremos hacer una Query o Consulta contra el DataSource que conecta con la BD de MySQL donde almacenamos las IPs con su Latitud y Longitud. Esta Query nos puede valer:

SELECT
UNIX_TIMESTAMP(`timestamp`) as time_sec,
  `latitud` as latitude,
  `longitud` as longitude,
  COUNT(`ip`) AS value,
  `ip` as name
FROM bujarra_ips
WHERE $__timeFilter(`timestamp`)
GROUP BY `ip`
ORDER BY `timestamp` ASC

En las opciones de Visualización indicaremos que el «Location Data» es table y más abajo deberemos indicar los campos que proporcionarán los datos de Latitud y Longitud, que en esta tabla son latitude & longitude. Cogeremos como campo de métrica la columna value, que en la Query si os fijáis, es la suma de las veces que dicha IP sale en la tabla. Si queremos, podremos definir el tamaño del círculo, o en «Threshold Options» indicar qué color pintará dependiendo de las visitas que nos haya hecho.

Si todo os ha ido bien os habrá pintado en un mapa la posición de cada IP pública que tengáis en la tabla, ahora podréis jugar con las fechas para visualizar más o menos datos. Entiendo que es un post como os dije antes algo particular, pero al menos es para poder daros ideas de cómo explotar IPs públicas que tengamos almacenadas en tablas de algún servicio de base de datos como es SQL, MySQL, MariaDB… como siempre espero que os haya resultado interesante!

Posts recomendados:

Héctor Herrero
Últimas entradas de Héctor Herrero (ver todo)