Bo, aquest document és una cosa particular, ja que tenia en una taula de MySQL una sèrie d'adreces IPs públiques, que m'agradaria visualitzar-lo a Grafana. Bàsicament us heu adopat de les visites del blog 🙂 Tinc un WordPress i no tinc en el meu poder la base de dades ja que està en un hosting extern… us deixo els passos que seguiu per aconseguir-ho, espero que us pugui servir per a altres idees.

El que s'ha dit, tinc una taula d'una base de dades on tinc unes IPs públiques, que és el WordPress d'aquest blog, però vagi no tinc accés directe a la BD ja que està en un hosting, ¿no? Tenia diverses opcions, m'imagino que WordPress té algun API que puc atacar directament o fer-ho d'una manera més manual però que em pugui servir per a altres taules que tinc per aquí.

El que m'interessa únicament és una taula que té les IPs dels visitants del blog, això a WordPress s'emmagatzema si a wp_statistics_visitor si és que tenim instal·lat a WordPress el Plugin 'WP Statistics'. L'única manera que se'm va ocórrer per exportar aquestes dades, va ser… des del mateix hosting (té SSH habilitat), crear un script i deixar-lo en una tasca programada (mitjançant cron) que s' executi a diari (sobre les 23:30). Aquest script com veieu deixa en un fitxer txt (separat per comes) la IP pública i la data. Us deixo saca_visitantes.sh:

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

Un minut més tard, en una altra màquina el que faig és recollir aquest fitxer de text i importar-lo en una base de dades MySQL local, on ja puc jugar amb aquestes dades. A què em refereixo amb jugar? Doncs que gràcies a la localització de les IPs públiques, ipstack ens dóna un API KEY gratuït de fins a 10.000 consultes al mes, per la qual cosa d'alguna cosa ens val, o com és el meu cas podem generar tants comptes en ipstack fins a cobrir la vostra demanda 😉

Per tant, amb el següent script (que admeto que es pot fer millor, però és al que arriba el meu coneixement…) podrem afegir en dues columnes addicionals a la IP, camps de Latitud i Longitud. Així que pel que podem interpretar en l'script, primer ens descarreguem el fitxer txt, després ho importem a MySQL; i al final el que farem és gràcies a aquesta API modificar cada línia importada i afegir-hi els camps Latitud & Longitud. Per si us serveix d'alguna cosa, us deixo 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=, --verbse --local -uUSUARI -pCONTRASENYA -h SERVIDOR_MYSQL NOMBRE_BD bujarra_direcciones_ip.txt TOT_LINEAS='echo "SELECT COUNT(*) FROM bujarra_ips WHERE data = CURDATE();" | mysql -h SERVIDOR_MYSQL -uUSUARI -pCONTRASENYA 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 data = CURDATE() order BY IP desc LIMIT $TOT_LINEAS,1;" | mysql -h SERVIDOR_MYSQL -uUSUARI -pCONTRASENYA 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 -uUSUARI -pCONTRASENYA NOMBRE_BD echo "TOT_LINEAS: $TOT_LINEAS"
  TOT_LINEAS =$(( $TOT_LINEAS - 1 ))
Fet

Bo, per si algú s'està preguntant quin format té la taula destí… és molt simple, té 5 camps, IP, data, latitud, longitud i timestamp, si necessiteu el codi per crear la taula us deixeu aquest codi:

CREATE TABLE 'bujarra_ips' (
  'IP' CHAR(20) NOT NULL,
  'data' 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
;

Ara que ja tenim les dades a prop del nostre Grafana el tenim xutat, ¿no? Tindrem ja un connector o Data Source creat contra la BD de MySQL i també haurem de tenir instal·lat el Worldmap Panel, que com veiem a la pestanya d'Installation l'instal·lem amb un simple comandament. Després d'aquestes premisses ja podrem crear dins d'un Dashboard un Panell, seleccionarem fer una Query o Consulta contra el DataSource que connecta amb la BD de MySQL on emmagatzemem les IPs amb la seva Latitud i Longitud. Aquesta Query ens pot 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 les opcions de Visualització indicarem que el “Location Data” és table i més avall haurem d'indicar els camps que proporcionaran les dades de Latitud i Longitud, que en aquesta taula són latitude & longitude. Agafarem com a camp de mètrica la columna value, que en la Query si us fixeu, és la suma de les vegades que aquesta IP surt a la taula. Si volem, podrem definir la mida del cercle, o en “Threshold Options” indicar quin color pintarà depenent de les visites que ens hagi fet.

Si tot us ha anat bé us haurà pintat en un mapa la posició de cada IP pública que teniu a la taula, ara podreu jugar amb les dates per visualitzar més o menys dades. Entenc que és un post com us dieu abans una cosa particular, però almenys és per poder donar-vos idees de com explotar IPs públiques que tinguem emmagatzemades en taules d'algun servei de base de dades com és SQL, MySQL, MariaDB… com sempre espero que us hagi resultat interessant!