Well, This document is something particular, since it had a series of public IP addresses in a MySQL table, that I would like to visualize it in Grafana. Basically I'm talking about the visits to the blog 🙂 I have a WordPress and I don't have the database in my possession since it's on an external hosting… I leave you the steps I followed to achieve it, I hope it can be useful for other ideas.

What I said, I have a table in a database where I have some public IPs, what is the WordPress of this blog?, but hey, I don't have direct access to the DB since it's on a hosting, ¿No? I had several options, I imagine that WordPress has some API that I can attack directly or do it in a more manual way but that can be useful for other tables that I have out there.

What interests me is only a table that has the IPs of the visitors of the blog, that in WordPress is stored if in wp_statistics_visitor if we have the 'WP Statistics' Plugin installed in WordPress. The only way I could think of to export that data, Was… from the hosting itself (has SSH enabled), Create a script and leave it in a scheduled task (via cron) that runs daily (on the 23:30). This script as you can see leaves in a txt file (separated by commas) the public IP and the date. I leave you saca_visitantes.sh:

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

One minute later, on another machine what I do is pick up that text file and import it into a local MySQL database, Where can I play with that data. What do I mean by playing? Well, thanks to ipstack we can obtain the location of public IPs, ipstack gives us a free API KEY of up to 10.000 Monthly consultations, so it is of some use to us, Or as is my case, we can generate as many accounts in ipstack to meet your demand 😉

Therefore, with the following script (Which I admit can be done better, But that's what my knowledge comes to…) we can add in two additional columns to the IP, the Latitude and Longitude fields. So from what we can interpret in the script, First we download the txt file, then we import it into MySQL; and in the end what we will do is thanks to this API modify each imported line and add the Latitude fields & Longitude. In case it helps you, I'll leave you 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 -uUSER -pPASSWORD -h SERVIDOR_MYSQL NOMBRE_BD bujarra_direcciones_ip.txt TOT_LINEAS='echo "SELECT COUNT(*) FROM bujarra_ips WHERE date = CURDATE();" | mysql -h SERVIDOR_MYSQL -uUSER -pPASSWORD 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 date = CURDATE() order BY IP desc LIMIT $TOT_LINES,1;" | mysql -h SERVIDOR_MYSQL -uUSER -pPASSWORD 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 LATITUDE='cat direcciones_ip.txt | grep -o -P '(?≪=latitude":).*(?=,"Longitude)'`
  LONGITUD=`cat direcciones_ip.txt | grep -o -P '(?≪=lengthe":).*(?=})'`
  echo "UPDATE bujarra_ips SET latitude=$LATITUD, longitud=$LONGITUD WHERE IP = '$IP';" | mysql -h SERVIDOR_MYSQL -uUSERNAME -pPASSWORD NOMBRE_BD echo "TOT_LINEAS: $TOT_LINEAS"
  TOT_LINEAS=$(( $TOT_LINEAS - 1 ))
Done

Well, in case anyone is wondering what format the destination table has… It's very simple, Has 5 Fields, IP, date, latitude, Length and timestamp, If you need the code to create the table I leave you this create code:

CREATE TABLE 'bujarra_ips' (
  'IP' CHAR(20) NOT NULL,
  'date' CHAR(20) NOT NULL,
  'latitude' CHAR(20) NOT NULL,
  'length' CHAR(20) NOT NULL,
  'timestamp' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

Now that we have the data near our Grafana, we have it sucked, ¿No? We will already have a connector or Data Source created against the MySQL DB and we must also have the Worldmap Panel, which as we see in the Installation tab we install it with a simple command. After these premises, we will be able to create a Panel within a Dashboard, we will select to make a Query or Query against the DataSource that connects to the MySQL DB where we store the IPs with their Latitude and Longitude. This Query can be useful to us:

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

In the Display options we will indicate that the “Location Data” is table and below we must indicate the fields that will provide the Latitude and Longitude data, which in this table are latitude & Longitude. We will take the value column as the metric field, that in the Query if you look, is the sum of the times that the IP appears in the table. If we want, we can define the size of the circle, or in “Threshold Options” indicate what color you will paint depending on the visits you have made to us.

If everything has gone well for you, it will have painted on a map the position of each public IP you have in the table, Now you can play with the dates to visualize more or less data. I understand that it is a post as I told you before something particular, but at least it is to give you ideas of how to exploit public IPs that we have stored in tables of a database service such as SQL, MySQL, MariaDB… As always, I hope you found it interesting!