tcpdump : charger le fichier de dump dans MySQL

cderenne

A la précédente étape on a vu comment obtenir de tcpdump un fichier de trace et le formater sous la forme d’un fichier texte séparé par des point-virgules dont les données sont :

  • heure
  • ipsource
  • portsource
  • ipdest
  • portdest
  • type (udp/tcp)
  • taille

Pour mon projet personnel, je n’ai pas besoin de savoir si la trame est udp ou tcp, mais j’ai besoin de stocker le nom de l’interface réseau ainsi que la date.

Pour limiter la quantité de données, je souhaite aussi regrouper les lignes par minutes (si j’ai plusieurs lignes identiques dans la même minute, je veux sommer et n’en stocker qu’une seule dont la taille de données sera la somme des lignes). Je pense qu’en shell il doit être possible de faire des regroupements. Je m’y connais un peu en shell mais pas suffisamment pour ça. Alors je vais passer par ce que je connais le mieux : le SQL et comme ne ne veux pas que cela augmente trop vite la taille de ma base de données, je passerai par une table temporaire.

Pour la date, il y en a un qui va me dire « ça tombe entre ce traitement et le précédent, on a passé minuit tout juste : le changement de jour va mettre un bordel monstre ». Il faut y penser, la solution est assez simple : si l’heure lue dans le fichier est > à l’heure actuelle, c’est que cela concerne la journée d’hier. Ce qui est pratique c’est que l’heure 24 n’existe pas : c’est l’heure 0.

Trêve de plaisanterie donc j’ai besoin d’une table temporaire , je vais l’appeler TMPIPPORT qui contient :

  • L’interface
  • La date + l’heure de la trame (à la seconde prêt)
  • l’ip source
  • le port source
  • l’ip destination
  • le port destination
  • la taille

De la remplir puis de l’utiliser pour remplir une vrai table physique.

Cette table je vais l’appeler TBIPPORT elle doit contenir :

  • L’interface
  • La date et l’heure de la trame (à la minute prêt seulement)
  • une IP (source ou destination)
  • un Port (source ou destination)
  • Le total des données émises
  • Le total des données reçues
  • La date et l’heure de la dernière trame de ce type dans cette minute.

Rappel important concernant les tables temporaires :

  • Elles ne sont plus visibles si on se déconnecte de la base de données.
  • Sauf option particulière, en fin de transaction, on les perd
  • Il ne peut y avoir ni commit ni rollback sur une table temporaire (sauf SGBD particulier avec des options particulières sur DB2 et Oracle)
  • Dans deux sessions différentes et exécutées en même temps, on peut utiliser le même nom de table temporaire pour des traitement distincts.

Donc dans un seul et même script SQL il va falloir :

  • Créer la table temporaire
  • Insérer les données dedans
  • Les utiliser pour remplir la table TBIPPORT

Petit aparté : C’est drôle, en écrivant tout ça, je m’aperçois qu’aujourd’hui, je ferai différemment de ce que j’ai réalisé au départ : je sous-traiterai plus d’opérations au SQL aujourd’hui (alors que dans mes scripts, il y a plus de Shell)

Script de création des tables :

Ma base de données associées à vnStat se nommait MYIFSTAT, ici je vais l’appeler MYTCPSTAT.

Pour ceux qui utilisent MySQL 5.7 ou inférieur, remplacez utf8mb4 par utf8

CREATE DATABASE MYTCPSTAT DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

 

TMPIPPORT 
DROP TEMPORARY TABLE IF EXISTS MYTCPSTAT.TMPIPORT;
CREATE TEMPORARY TABLE MYTCPSTAT.TMPIPORT (
IFACE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
DT datetime NOT NULL, 
IPSOURCE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PORTSOURCE int(11) DEFAULT NULL,
IPDEST varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PORTDEST int(11) DEFAULT NULL,
TAILLE INT(11) NOT NULL DEFAULT 0 );
TBIPPORT 
CREATE TABLE MYTCPSTAT.TBIPPORT(
IDIPPORT BIGINT(20) NOT NULL AUTO_INCREMENT,
IFACE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
DT datetime NOT NULL,
IP varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
PORT int(11) NOT NULL ,
RX DECIMAL(24,6) NOT NULL DEFAULT 0,
TX DECIMAL(24,6) NOT NULL DEFAULT 0,
DMAJ DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (IDIPPORT ),
UNIQUE KEY IX0IDIPPORT (IFACE,DT,IP,PORT)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Bien entendu TBIPPORT devra être créé dans la base de données et y rester (donc à créer une seule fois et endehors du script=, tandis que TMPIPPORT devra être créé à chaque passage du script.

Script de création du fichier SQL de chargement :

D’abord créer un fichier qui contiendra les ordres SQL :

rm -f fichier.sql
touch fichier.sql

Puis y mettre la création de la table temporaire :

echo "DROP TEMPORARY TABLE IF EXISTS MYIFSTAT.TMPIPORT;" >> fichier.sql
echo "CREATE TEMPORARY TABLE MYIFSTAT.TMPIPORT (" >> fichier.sql
echo "IFACE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL," >> fichier.sql
echo "DT datetime NOT NULL, "
echo "IPSOURCE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, " >> fichier.sql
echo "PORTSOURCE int(11) DEFAULT NULL, " >> fichier.sql
echo "IPDEST varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, " >> fichier.sql
echo "PORTDEST int(11) DEFAULT NULL," >> fichier.sql
echo "TAILLE INT(11) NOT NULL DEFAULT 0 ); " >> fichier.sql

Supposons que notre fichier texte de données issues de tcpdump (après les 1ers traitements) se nomme fichier.txt on va le lire ligne par ligne et traiter chaque ligne pour compléter fichier.sql

ymd=$(date +"%Y-%m-%d")
ymdhier=$(date -d "$dataset_date - 1 days" +%Y-%m-%d)
monheure=$(date +"%H")

while IFS=";" read heure ipsource portsource ipdest portdest type taille
do
    traiter chaque ligne
done < fichier.txt
rm -f fichier.txt

Détail du traitement de chaque ligne : if faut insérer les données (donc déterminer la date, voilà pourquoi j’ai mis en variable la date d’aujourd’hui, celle d’hier et l’heure actuelle)

hh=$(echo "$heure" | cut -d: -f1)      #hh est 1ère partie du contenu de heure en utilisant le séparateur :
if [ $(echo "$hh <= $monheure" | bc) -ge 1 ]; then
   dt=$ymd
else
   dt=$ymdhier 
fi

Compléter dt (datetime) par la partie heure et la partie minute + seconde

dt="$dt"" ""$(echo "$heure" | cut -d: -f1-3 | cut -d. -f1)"   #l'heure est le field1, les minutes le field2 et les secondes le field3 sans la partie milliseconde

Créer la ligne sql (iface doit être fourni en entrée, c’est le nom de la carte réseau : eth0 ou autre)

sql="INSERT INTO MYIFSTAT.TMPIPPORT(IFACE,DT,IPSOURCE,PORTSOURCE,IPDEST,PORTDEST,TAILLE) VALUES "
sql="$sql""('"$iface""','""$dt""','""$ipsource""',""$portsource"",'""$ipdest""',""$portdest"",""$taille"");"

Mettre cette ligne dans le fichier sql

echo "$sql" >> fichier.sql

Après la  boucle de lecture du fichier texte, il faut encore ajouter dans le fichier sql, l’ordre qui va mettre le contenu de TMPIPPORT dans TBIPPORT.

Mais là, j’ai besoin de savoir si c’est de l’émission ou de la réception. Pour cela, c’est assez simple : on récupère l’ip locale de l’interface. Si elle est dans ipdest, c’est de la réception, sinon c’est de l’émission. Cette récupération de la valeur de l’ip locale doit se faire avant la boucle et une seule fois (l’ip locale ne change pas)

Comme je peux avoir plusieurs interface à traiter et que chacune à son ip locale propre, j’utilise ifconfig.

Si vous tapez en ligne de commande ipconfig suivit du nom de l’interface, vous avez des informations la concernant :

[root@pntserv bash]# ifconfig enp2s0
enp2s0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.200  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::dacb:8aff:fe71:a75d  prefixlen 64  scopeid 0x20<link>
        ether d8:cb:8a:71:a7:5d  txqueuelen 1000  (Ethernet)
        RX packets 19385524  bytes 15893422926 (14.8 GiB)
        RX errors 0  dropped 13568  overruns 0  frame 0
        TX packets 16886385  bytes 7578752149 (7.0 GiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
        device interrupt 19

l’IP locale est juste après le premier mot clé « inet » et tout est séparé par des espaces (ou des tabulations). Donc d’abord j’utilise grep avec l’option -m1 pour n’avoir que la 1ère ligne contenant inet

[root@pntserv bash]# ifconfig enp2s0 | grep "inet" -m1
        inet 192.168.1.200  netmask 255.255.255.0  broadcast 192.168.1.255

De cela, je le passe à awk pour avoir le 2ième argument (print $2)

iplocal=$(ifconfig enp2s0 | grep "inet" -m1 | awk '{print $2}')
echo $iplocal
192.168.1.200

Maintenant je peux faire la requête SQL

echo "INSERT INTO MYTCPSTAT.TBIPPORT(IFACE,DT,IP,PORT,RX,TX,DMAJ)  " >> fichier.sql
echo "SELECT A.IFACE, A.DT, " >> fichier.sql
echo "CASE WHEN A.IPDEST<>'""$iplocal""' THEN A.IPDEST ELSE A.IPSOURCE END AS IP,  " >> fichier.sql
echo "CASE WHEN A.IPDEST<>'""$iplocal""' THEN A.PORTDEST ELSE A.PORTSOURCE END AS PORT,  " >> fichier.sql
echo "ROUND(SUM(CASE WHEN A.IPDEST='""$iplocal""' THEN A.TAILLE ELSE 0 END)/1024,3) AS RX,  " >> fichier.sql
echo "ROUND(SUM(CASE WHEN A.IPSOURCE='""$iplocal""' THEN A.TAILLE ELSE 0 END)/1024,3) AS TX,  " >> fichier.sql
echo "MAX(A.DMAJ) AS DMAJ  " >> fichier.sql
echo "FROM " >> fichier.sql
echo "( " >> fichier.sql
echo "SELECT  IFACE,DATE_SUB(DT, INTERVAL SECOND(DT) SECOND) AS DT, IPSOURCE,PORTSOURCE,IPDEST,PORTDEST,SUM(TAILLE)  AS TAILLE, MAX(DT) AS DMAJ " >> fichier.sql
echo "FROM MYTCPSTAT.TMPIPORT " >> fichier.sql
echo "GROUP BY IFACE,DATE_SUB(DT, INTERVAL SECOND(DT) SECOND), IPSOURCE,PORTSOURCE,IPDEST,PORTDEST " >> fichier.sql
echo ") AS A " >> fichier.sql
echo "LEFT JOIN MYTCPSTAT.TBIPPORT AS B  " >> fichier.sql
echo "ON A.IFACE=B.IFACE  " >> fichier.sql
echo "AND A.DT=B.TS   " >> fichier.sql
echo "AND CASE WHEN A.IPDEST<>'""$iplocal""' THEN A.IPDEST ELSE A.IPSOURCE END=B.IP   " >> fichier.sql
echo "AND CASE WHEN A.IPDEST<>'""$iplocal""' THEN A.PORTDEST ELSE A.PORTSOURCE END=B.PORT   " >> fichier.sql
echo "WHERE B.IFACE IS NULL   " >> fichier.sql
echo "GROUP BY A.IFACE, A.DT, " >> fichier.sql
echo "CASE WHEN A.IPDEST<>'""$iplocal""' THEN A.IPDEST ELSE A.IPSOURCE END,  " >> fichier.sql
echo "CASE WHEN A.IPDEST<>'""$iplocal""' THEN A.PORTDEST ELSE A.PORTSOURCE END " >> fichier.sql

Vous remarquez que j’aime bien mettre des quotes (« ) en fait Linux déteste qu’un espace se trouve dans une valeur, sauf si on lui met des  » ou des {}. Je m’arrange toujours pour qu’il n’y en ai pas (quand c’est possible). Mais si ce type de syntace doit être réutiliser dans un autre contexte où il est normal d’avoir un espace dans une chaîne de caractère, il est bon de partir sur des bases saines.

Vous remarquez aussi que quelques fois je mets des ‘ et d’autre fois non : les ‘ entourent les chaînes de caractères et les dates. Mais je n’en mets pas s’il s’agit de valeurs numériques.

Je ne précise pas de valeur pour IDIPPORT, alors que cette colonne n’est pas nullable ! Et c’est normal : IDIPPORT est un autoincrément, MySQL va se débrouiller tout seul pour mettre la bonne valeur en l’incrémentant au fur et à mesure. Et DMAJ a une valeur par défaut : CURRENT_TIMESTAMP donc MySQL mettra cette valeur si je ne la précise pas.

Dans DMAJ je mets la date-heure-minute-seconde de la dernière trame de la même trame et dans DT, seulement la partie DATE – HEURE – MINUTE

La jointure gauche (LEFT JOIN) sert à m’assurer que la ligne n’existe pas déjà (respect de l’indexe unique présent dans TBIPPORT).

Il y a deux SELECT : un 1er SELECT de la table temporaire qui regroupe les données de la même minute en réalisant une somme et un SELECT de ces données qui éclate la TAILLE en RX/TX et toujours en regroupant les données.

Ne reste plus qu’à demander à MySQL d’exécuter cette requête :

mysql --login-path -e fichier.sql

ou avec Mariadb :

mysql -u username -p password -e fichier.sql

Remarques :

Je suis sur qu’il y en a un qui se dit :

« Maintenant qu’on a le niveau le plus fin, on peut se passer complètement du précédent traitement à l’aide de vnstat puisque par simple regroupement par jour ou mois sur notre nouvelle table, on peut avoir toutes les informations qu’on veut ».

La réponse est : Oui effectivement.

Alors « Pourquoi avoir parler de vnstat ? »

Parce que je voulais expliquer historiquement comment d’un simple besoin en statistiques mensuelles et sans connaitre tcpdump), j’en suis arriver à utiliser tcpdump car  la curiosité de connaitre les ip et port de connexion a été plus forte que la flemme. Et puis parce que certains d’entre vous ne voudrons que des stats simples pour lesquelles il est inutile d’utiliser tcpdump.

Un autre se dit surement

« Mais pendant que le traitement s’occupe de MySQL, il n’analyse plus les trames ! ».

Oui si on fait cela séquentiellement mais en utilisant un script maître qui va lancer tcpdump puis chaque fois lancer un autre script qui chargera MySQL dans un autre thread (grâce à nohup) on résout ce problème.

Enfin, la question qui vient aussi est :

« Comment le script maître va-t-il se déclencher, on ne peut pas utiliser une crontab ! et en plus on a autant de script à lancer que d’interfaces réseaux ! »

Exact !! Donc on va créer un service par interface réseau et c’est ce service qui lancera le script maître, c’est l’objectif du prochain article.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.