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, ho una tabella di un database dove ho alcuni IP pubblici, che è il WordPress di questo blog, ma non ho accesso diretto al database dato che è in un hosting, No? Avevo diverse opzioni, immagino che WordPress abbia qualche API a cui posso accedere direttamente, oppure farlo in un modo più manuale che mi possa servire per altre tabelle che ho lì.

Ciò che mi interessa unicamente è una tabella che contiene gli IP dei visitatori del blog, questo in WordPress è memorizzato in wp_statistics_visitor se abbiamo installato il plugin 'WP Statistics'. L'unico modo che mi è venuto in mente per esportare quei dati, è stato… dal proprio hosting (ha SSH abilitato), creare uno script e lasciarlo in un'attività pianificata (mediante cron) che venga eseguito quotidianamente (sulle 23:30). Questo script come vedete lascia in un file txt (separato da virgole) l'IP pubblico e la data. Vi lascio saca_visitantes.sh:

ECO "SELECT IP, LAST_COUNTER FROM wp_statistics_visitor where LAST_COUNTER = CURDATE( );" | mysql -uUTENTE -pPASSWORD NOME_DB | sed 's/\t/,/g' ≫ web/bujarra.com/public_html/temp/direcciones_ip.txt

Un minuto più tardi, su un'altra macchina quello che faccio è raccogliere quel file di testo e importarlo in un database MySQL locale, dove posso già giocare con quei dati. A cosa mi riferisco con giocare? Bene grazie a ipstack possiamo ottenere la localizzazione degli IP pubblici, ipstack ci fornisce una chiave API gratuita fino a 10.000 Consultazioni mensili, quindi ci è utile, 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” indicare quale colore dipingerà a seconda delle visite che ci ha fatto.

Se tutto è andato bene, vi avrà dipinto su una mappa la posizione di ogni IP pubblico che avete nella tabella, ora potrete giocare con le date per visualizzare più o meno dati. Capisco che è un post, come vi ho detto prima, un po' particolare, ma almeno serve per darvi idee su come sfruttare gli IP pubblici che abbiamo memorizzati nelle tabelle di qualche servizio di database come SQL, MySQL, MariaDB… come sempre spero che vi sia risultato interessante!