Bene, Questo documento è qualcosa di particolare, poiché aveva una serie di indirizzi IP pubblici in una tabella MySQL, che mi piacerebbe visualizzarlo in Grafana. Fondamentalmente sto parlando delle visite al blog 🙂: ho un WordPress e non ho il database in mio possesso visto che è su un hosting esterno… Vi lascio i passaggi che ho seguito per realizzarlo, Spero possa essere utile per altre idee.

Cosa ho detto, 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:

ECO "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 Consultazioni mensili, por lo que de algo nos vale, o como es mi caso podemos generar tantas cuentas en ipstack hasta cubrir vuestra demanda 😉

Pertanto, 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 "SELEZIONA CONTEGGIO(*) 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}
  ECO "IP: $IP"
  curl "Protocollo HTTP://api.ipstack.com/$IP?access_key=NUESTRO_TOKEN&%20fields=latitude,Longitudine&format=2" ≫ direcciones_ip.txt
  LATITUD=`cat direcciones_ip.txt | grep -o -P '(?≪=latitude":).*(?=,"Longitudine)'`
  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 ))
fatto

Bene, por si alguien se está preguntando qué formato tiene la tabla destino… es muy simple, Ha 5 Campi, IP, dattero, 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) NON NULLO,
  `fecha` CHAR(20) NON NULLO,
  `latitud` CHAR(20) NON NULLO,
  `longitud` CHAR(20) NON NULLO,
  `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`) Quindi 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 & Longitudine. 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. Se vogliamo, podremos definir el tamaño del círculo, o in “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!