On a maintenant :
- Une table TBIPPORT qui contient toutes les tentatives de connexions, dont les IP
- Une table TBIPCOUNTRY vide.
Il faut la remplir au fur et à mesure de la venue de nouvelles IP et renseigner les champs COUNTRYCODE et COUNTRYLIB.
Géolocaliser sous-entant, interroger un service web pour chaque ip : ce peut-être un processus long donc on ne va pas intégrer cette fonctionnalité au service. Mais on va utiliser un nouveau script qui sera lancé régulièrement en crontab.
1ère étape : compléter au fur et à mesure la liste des IP uniques ayant chercher à étable un contact avec notre serveur.
Il faut réaliser un INSERT des IP qu’on n’a pas encore. Et il faut qu’aucune IP ne soit doublé (par le champs IP est uné clé primaire). Ce code doit pouvoir être relancé de manière identique à chaque passage du traitement, même si on a déjà certaines IP.
En SQL c’est très simple à faire : un INSERT par SELECT avec un jointure gauche et un GROUP BY
INSERT INTO MYTCPSTAT.TBIPCOUNTRY (IP) SELECT A.IP FROM MYTCPSTAT.TBIPPORT AS A LEFT JOIN MYTCPSTAT.TBIPCOUNTRY AS B ON A.IP=B.IP WHERE B.IP IS NULL GROUP BY A.IP;
2ième étape : identifier les cas : IP locales ou externes ?
Maintenant que TBIPCOUNTRY contient toutes les IP, il faut les géolocaliser. Deux cas sont possibles :
- soit on a affaire à une IP externe (publique) : et on la géolocalisera
- soit on a affaire à une IP sur le réseau locale et on ne pourra pas la géolocaliser. Pour celles-ci je vais considérer que leur pays est le même que celui de l’IP publique du serveur.
Donc d’abord je vais trouver l’IP publique de mon serveur , il existe de nombreuses façons de la faire, en voici une :
ippublic=$(curl ipinfo.io/ip) echo $ippublic 77.206.127.130
Je dois savoir maintenant si les IP contenues dans TBIPCOUNTY sont locales ou externes. Il y a bien une définition stricte, mais j’ai décider de ne pas me prendre la tête : j’ai choisis de prendre celles dont les 2 &ers chiffres sont les mêmes que l’adresse locale de chaque interface.
Donc il faut faut l’adresse locale de chacune des interfaces actives dans ma base de données, pour cela :
- on décharge le contenu de la table TBIFACE là ou ACTIF=’Y’ dans un fichier
- on lit se fichier ligne à ligne et pour chaque interface, on interroge ifconfig pour obtenir l’ip locale
- et stocker tout cela dans un nouveau fichier en y ajoutant (parce que c’est facile à ce niveau), le début de l’ip (A.B. pour A.B.C.D)
rm -f /tmp/listeifacetcpstat.txt sql="SELECT IFACE FROM MYTCPSTAT.TBIFACE WHERE ACTIF='Y' INTO OUTFILE '/tmp/listeifacetcpstat.txt'" mysql --user="$mysqluser" --password="$mysqlpass" -e "$sql"
Lecture ligne à ligne
touch /tmp/listeifacetcpstatlocal.txt
while IFS="|" read myiface
do
iplocal=$(ifconfig "$myiface" | grep -m1 inet | awk '{printf $2}')
ret=$?
if [ $ret -eq 0 ]; then
sublocal=$(echo $iplocal | cut -d. -f1-2)
echo "$myiface""|""$iplocal""|""$sublocal""." >> /tmp/listeifacetcpstatlocal.txt
fi
done < /tmp/listeifacetcpstat.txt
/tmp/listeifacetcpstat.txt contient 3 colonnes et séparées par un pipe (|) et autant de ligne que d’interfaces, ces 3 colonnes sont :
- nom de l’interface (ex : eth0 ou enp2s0)
- sont ip locale (192.168.20.254 par exemple)
- le début de l’ip locale (ici 192.168) : « sublocal »
Pour chaque IP de TBIPCOUNTRY, si elle commence par « sublocal » alors je dit que c’est un ip locale et je mettrai le même Pays que celui de l’ip publique de mon serveur
3ième étape : géolocaliser son propre serveur :
Ca peut paraître idiot, mais si j’installe le même service sur un serveur à l’étranger, ça m’évitera d’oublier un paramétrage. Et puis c’est un bon exercice pour la suite.
Donc pour ça, on a besoin de geoiplookup qui s’installe partout, très simplement
si ippublic contient mon ip public, voici l’ordre à exécuter :
mycountry=$(geoiplookup $ippublic) echo $mycountry
On obtient donc en sortie : un libellé et une valeur, séparés par « : » ==> on va utiliser cut avec délimiteur « : »
La valeur contient un espace, le code pays, une virgule, un espace et le nom de pays ==> on va utiliser cut avec délimiteur, mais comme il y a aussi des espaces, on va ajouter xargs pour s’en débarrasser :
mycountry=$(geoiplookup $ippublic|cut -d: -f2 | xargs) echo $mycountry FR, France mycountrycode=$(echo $mycountry | cut -d, -f1 | xargs) echo $mycountrycode FR mycountrylib=$(echo $mycountry | cut -d, -f2 | xargs) echo $mycountrylib France
Il faut penser aussi à ce qui se passe si geoiplookup ne trouve pas :
geoiplookup 193.169.252.69| cut -d: -f2 | xargs IP Address not found
Donc si on tombe là-dessus, il ne faudra pas chercher à mettre à jour notre table.
4ième étape : mettre le pays pour les ip locales
Donc si l’IP est locale, je mets le Pays de l’ip publique de mon serveur. L’ip locale va dépendre de l’interface que je traite donc je fais ce traitement pour chaque ligne de /tmp/listeifacetcpstatlocal.txt.
Pour trouver les ip qui commence par xxxx, on fait WHERE IP LIKE ‘xxxx%’ (le % final est impératif !)
while IFS="|" read myiface myip mysub do sql="UPDATE MYTCPSTAT.TBIPCOUNTRY SET COUNTRYCODE='""$mycountrycode""', COUNTRYLIB='""$mycountrylib""' WHERE IP LIKE '""$mysub""%';" mysql --login-path=local -e "$sql" done < /tmp/listeifacetcpstatlocal.txt
Ca y est, mes IP locales sont géolocaliser, je n’ai plus qu’à m’occuper des autres : celles que je n’ai pas encore géolocalisé (donc celles dont COUNTRYCODE est NULL)
5ième étape : mettre le pays pour les ip externes
On va mettre dans un fichier, toutes les IP de TBIPCPOUNTRY pour lesquelles COUNTRYCODE est NULL
rm -f /tmp/listeifacetcpstatexterne.txt sql="SELECT IP FROM MYTCPSTAT.TBIPCOUNTRY WHERE COUNTRYCODE IS NULL INTO OUTFILE '/tmp/listeifacetcpstatexterne.txt'" mysql --login-path=local -e "$sql" ret=$? if [ $ret -ne 0 ]; then echo "Pb ""$sql" exit 99 fi
On va lire ce fichier ligne à ligne, géolocaliser chaque IP pour mettre à jour les champs de TBIPCOUNTRY COUNTRYCODE et COUNTRYLIB
while iFS= read myip
do
mycountry=$(geoiplookup $myip |cut -d: -f2 | xargs)
ret=$?
notfound=$(echo "$mycountry" | grep -i " not ")
if [ $ret -eq 0 -a "$notfound" == "" ]; then
mycountrycode=$(echo $mycountry | cut -d, -f1 | xargs)
mycountrylib=$(echo $mycountry | cut -d, -f2 | xargs)
sql="UPDATE MYTCPSTAT.TBIPCOUNTRY SET COUNTRYCODE='""$mycountrycode""', COUNTRYLIB='""$mycountrylib""' WHERE IP='""$myip""';"
mysql --login-path=local -e "$sql"
fi
done < /tmp/listeifacetcpstatexterne.txt