J’ai donc maintenant une table TBIPPORT qui contient pour chaque connexion, par minutes, la date, l’heure à la minute prêt, les quantités de données émises et reçues, l’IP du partenaire et le port utilisé.

J’avais dis qu’ j’aimerai afficher  nom du service associé au port. Mais je n’avais pas envie de demander à tcpdump de le faire car j’aurai eu en sortie plusieurs fois les mêmes nom de services (car je peux avoir plusieurs fois le même port).

Et puis les noms de services sont toujours les mêmes pour les services connus du moins. Je ne m’intéresse qu’au 8192 premiers ports. On trouve sur le web, la liste des services par port. Je m’en suis fait un fichier téléchargeable ici : tbservicename

Et j’utilise les possibilité du SQL pour afficher le nom du service si je l’ai grâce à une jointure gauche (LEFT JOIN), ainsi, pas besoin de demander quoi que ce soit à TCPDUMP.

Voici le code de création de la table TBSERVICENAME :

CREATE TABLE MYTCPSTAT.TBSERVICENAME (
PORT int(11) NOT NULL,
SERVICENAME varchar(128) CHARSET=utf8mb4 COLLATE=utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (PORT)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Et pour afficher le service si je l’ai il suffira de faire :

SELECT A.PORT, B.SERVICENAME 
FROM MYTCPSTAT.TBIPPORT AS A
LEFT JOIN MYTCPSTAT.TBSERVICENAME AS B
ON A.PORT=B.PORT;

Dans ce genre de circonstance, en théorie, tout PORT présent dans TBIPPORT devrait exister dans TBSERVICENAME et je devrai créer une clé étrangère. Dans notre cas, ce ne serait pas une bonne idée car tcpdump me donne d’abord les trames, que je possède le nom du service associé ou pas donc cela ne fonctionnerait pas.

Par contre, ne rien faire ferait rapidement chuter les performances de MySQL sur cette jointure. Alors il nous faut un créer un index (surtout pas unique) sur la colonne PORT de TBIPPORT, ceci rendra l’exécution de cette requête bien plus rapide.

ALTER TABLE MYTCPSTAT.TBIPPORT
ADD INDEX IXPORTIPPORT (PORT ASC) VISIBLE;