tcpdump : construire les stats

cderenne

Inutile d’utiliser aussi vnStat pour avoir les statistiques horaires, quotidiennes et mensuelles puisque TCPDUMP nous donne déjà le niveau le plus fin, il suffit de faire des regroupements.

Pour me simplifier la vie dans le code php, déjà je créer une vue du niveau de détail le plus fin qui me donnera en plus des lignes de TBIPPORT, le pays de l'IP et le nom du service :

CREATE VIEW VIPPORT AS
SELECT A.IDIPPORT,
A.TS,
A.IFACE,
A.IP,
B.TYPEIP,
B.COUNTRYCODE,
B.COUNTRYLIB,
A.PORT,
C.SERVICENAME,
A.RX,
A.TX,
A.DMAJ
FROM TBIPPORT AS A
LEFT JOIN TBIPCOUNTRY AS B ON B.IP=A.IP
LEFT JOIN TBSERVICENAME AS C ON C.PORT=A.PORT;

Mais attention, comme je l’ai construit, le niveau le plus fin (TCPIPPORT) contient autant de lignes que de connexion par IP/Port chaque minutes et il y a 1440 minutes par jours.

Avec 10 connexions de type distinctes par jour (et c’est peu) par jour, ça donne déjà 14 400 lignes par jour.

Il ne faudra pas ni faire exploser la base de données ni ralentir son fonctionnement. D’un autre côté qui a besoin d’autant de détails dans l’historique ? Qui aurait besoin de retrouver l’IP qui s’est connectée sur le port 137 il y a 6 mois ? On peut en avoir besoin jusqu’à un mois mais guerre plus.

En se cas, il y a une solution. Elle consiste à :

  • Au fur et à mesure que le temps passe, calculer les regroupements et les stocker dans des tables prévues à cet effet
  • Purger ensuite régulièrement le niveau le plus fin de détail.

Réfléchissons aux besoins : il me faut :

  • le total des quantités de données émises et reçues par heures (quelque soit l’ip et le port) + le nombre de connexions
  • le total des quantités de données émises et reçues par heures et par ip (quelque soit le port) + le nombre de connexions
  • le total des quantités de données émises et reçues par heures par port (quelque soit l’ip) + le nombre de connexions
  • le total des quantités de données émises et reçues par jours (quelque soit l’ip et le port) + le nombre de connexions
  • le total des quantités de données émises et reçues par jours et par ip (quelque soit le port) + le nombre de connexions
  • le total des quantités de données émises et reçues par jours par port (quelque soit l’ip) + le nombre de connexions
  • le total des quantités de données émises et reçues par mois (quelque soit l’ip et le port) + le nombre de connexions
  • le total des quantités de données émises et reçues par mois et par ip (quelque soit le port) + le nombre de connexions
  • le total des quantités de données émises et reçues par mois par port (quelque soit l’ip) + le nombre de connexions

J’ai donc 3 regroupements temporelles : heures/ jours/mois et pour chacun :  total par ip , total par port et total tout ip, tout port.

Il y a de multiples solutions, l’une d’elle consiste à garder le niveau le plus fin (ip + port) et à sommer par heure dans une table puis par jours dans une autre et enfin par mois dans une 3ième.

Le stockage peut-être déclenché : à la détection du changement d’heures, de jours, de mois (ou pas si on fait attention à ne pas réinsérer des lignes qu’on a déjà).

C’est bien évidement un script lancé en crontab qui fera le job, il pourrait tourner toutes les 30 minutes (pour voir le remplissage de ces tables assez tôt).

On va en profiter pour purger en même temps le niveau de détail le plus fin (TBIPPORT)  en gardant à minimum le précédent mois en totalité.

On va commencer par les 3 regroupements les plus fin : on garde l’IP et le port mais on regroupe d’abord par heure, puis par jour puis par mois.

Pour les 2 1ères (heures et jours)la structure peut-être identique à TBIPPORT en ajoutant le nombre de connexion si DT (le timestamp) s’arrête à l’heure ou à la date. Pour le cas du mois on peut aussi faire pareil soit en prenant le 1er jour du mois ou le dernier (mais je préfère le dernier).

 

TBIPPORTHOUR 
CREATE TABLE MYTCPSTAT.TBIPPORTHOUR (
  IDIPPORTHOUR bigint(20) NOT NULL AUTO_INCREMENT,
  TS datetime NOT NULL,
  IFACE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin 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.000000',
  TX decimal(24,6) NOT NULL DEFAULT '0.000000',
  DMAJ datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  NBECONNEXION BIGINT(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (IDIPPORTHOUR),
  UNIQUE KEY IX0IDIPPORTHOUR (IFACE,TS,IP,PORT),
  KEY IXPORTIPPORTHOUR (PORT),
  KEY IXIPIPPORTHOUR (IP)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

 

TBIPPORTDAY
 

CREATE TABLE MYTCPSTAT.TBIPPORTDAY (
  IDIPPORTDAY bigint(20) NOT NULL AUTO_INCREMENT,
  TS datetime NOT NULL,
  IFACE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin 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.000000',
  TX decimal(24,6) NOT NULL DEFAULT '0.000000',
  DMAJ datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  NBECONNEXION BIGINT(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (IDIPPORTDAY),
  UNIQUE KEY IX0IDIPPORTDAY (IFACE,TS,IP,PORT),
  KEY IXPORTIPPORTDAY (PORT),
  KEY IXIPIPPORTDAY (IP)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

 

TBIPPORTMONTH 
CREATE TABLE MYTCPSTAT.TBIPPORTMONTH (
  IDIPPORTMONTH bigint(20) NOT NULL AUTO_INCREMENT,
  TS datetime NOT NULL,
  IFACE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin 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.000000',
  TX decimal(24,6) NOT NULL DEFAULT '0.000000',
  DMAJ datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  NBECONNEXION BIGINT(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (IDIPPORTMONTH),
  UNIQUE KEY IX0IDIPPORTMONTH (IFACE,TS,IP,PORT),
  KEY IXPORTIPPORTMONTH (PORT),
  KEY IXIPIPPORTMONTH (IP)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

On commencera par l’insertion au niveau le plus fin (TBIPPORTHOUR) en regroupant par heure et en s’assurant que la ligne n’est pas déjà présente, sinon la mettre à jour.

On peut se limiter au mois en cours.

INSERT INTO MYTCPSTAT.TBIPPORTHOUR
(IFACE,TS,IP,PORT,RX,TX,DMAJ,NBECONNEXION)
SELECT X.IFACE, X.TS, X.IP, X.PORT, X.RX, X.TX, X.DMAJ, X.NBECONNEXION
FROM (
SELECT
    A.IFACE,
    DATE_SUB(A.TS, INTERVAL MINUTE(A.TS) MINUTE) AS TS,    
    A.IP,
    A.PORT,
    SUM(A.RX) AS RX,
    SUM(A.TX) AS TX,
    MAX(A.DMAJ) AS DMAJ,
    COUNT(*) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORT AS A
WHERE DATE(A.TS)>=CONCAT(YEAR(CURDATE()),"-",LPAD(MONTH(CURDATE()),2,"0"),"-01")
GROUP BY A.IFACE, DATE_SUB(A.TS, INTERVAL MINUTE(A.TS) MINUTE), A.IP,A.PORT
) AS X
ON DUPLICATE KEY UPDATE RX=X.RX, TX=X.TX, DMAJ=X.DMAJ, NBECONNEXION=X.NBECONNEXION;

Ensuite, on peut partir de cette table pour remplir le niveau jour, il suffit de retirer les heures et de regrouper de la même manière.

INSERT INTO MYTCPSTAT.TBIPPORTDAY
(IFACE,TS,IP,PORT,RX,TX,DMAJ,NBECONNEXION)
SELECT X.IFACE, X.TS, X.IP, X.PORT, X.RX, X.TX, X.DMAJ, X.NBECONNEXION
FROM (
SELECT
A.IFACE,
DATE_SUB(A.TS, INTERVAL HOUR(A.TS) HOUR) AS TS,
A.IP,
A.PORT,
SUM(A.RX) AS RX,
SUM(A.TX) AS TX,
MAX(A.DMAJ) AS DMAJ,
SUM(A.NBECONNEXION) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORTHOUR AS A
WHERE DATE(A.TS)>=CONCAT(YEAR(CURDATE()),"-",LPAD(MONTH(CURDATE()),2,"0"),"-01")
GROUP BY A.IFACE, DATE_SUB(A.TS, INTERVAL HOUR(A.TS) HOUR), A.IP,A.PORT
) AS X
ON DUPLICATE KEY UPDATE RX=X.RX, TX=X.TX, DMAJ=X.DMAJ, NBECONNEXION=X.NBECONNEXION;

Enfin, on peut partir de cette table pour remplir le niveau mois, il suffit de retirer les heures et de regrouper de la même manière, mais déterminant le dernier mois du jour

INSERT INTO MYTCPSTAT.TBIPPORTMONTH
(IFACE,TS,IP,PORT,RX,TX,DMAJ,NBECONNEXION)
SELECT X.IFACE, X.TS, X.IP, X.PORT, X.RX, X.TX, X.DMAJ, X.NBECONNEXION
FROM (
SELECT
A.IFACE,
DATE_ADD(DATE_SUB(A.TS, INTERVAL DAY(A.TS) DAY), INTERVAL 1 MONTH) AS TS, 
A.IP,
A.PORT,
SUM(A.RX) AS RX,
SUM(A.TX) AS TX,
MAX(A.DMAJ) AS DMAJ,
SUM(A.NBECONNEXION) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORTDAY AS A
WHERE DATE(A.TS)>=CONCAT(YEAR(DATE_SUB(A.TS, INTERVAL 2 MONTH)),"-",LPAD(MONTH(DATE_SUB(A.TS, INTERVAL 2 MONTH)),2,"0"),"-01")
GROUP BY A.IFACE, DATE_ADD(DATE_SUB(A.TS, INTERVAL DAY(A.TS) DAY), INTERVAL 1 MONTH), A.IP,A.PORT
) AS X
ON DUPLICATE KEY UPDATE RX=X.RX, TX=X.TX, DMAJ=X.DMAJ, NBECONNEXION=X.NBECONNEXION;

Si Juste là, tout s’est bien passé alors on peut supprimer les lignes de détails le plus fin qui date de plus de 2 mois par exemple en ne gardant que ce qui débute au 1er jour du mois précédent.

DELETE FROM MYTCPSTAT.TBIPPORT
WHERE TS < DATE_SUB(DATE_SUB(CURDATE(), INTERVAL -1+DAY(CURDATE()) DAY ), INTERVAL 1 MONTH)

Pour les regroupements par IP ou par Port,  on peut créer des vues qui liront les précédentes tables en en profitant pour récupérer la géolocalisation ou le nom du service :

regroupement par IP et par heure sans distinction de port regroupement par IP et par heure sans distinction de port 
CREATE VIEW VIPHOUR AS
SELECT
A.TS,
A.IFACE,
A.IP,
B.COUNTRYCODE,
B.COUNTRYLIB,
B.TYPEIP,
SUM(A.RX) AS RX,
SUM(A.TX) AS TX,
MAX(A.DMAJ) AS DMAJ,
SUM(A.NBECONNEXION) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORTHOUR AS A
LEFT JOIN MYTCPSTAT.TBIPCOUNTRY AS B
ON A.IP=B.IP
GROUP BY A.TS,A.IFACE,B.COUNTRYCODE,B.COUNTRYLIB;
 
CREATE VIEW VPORTHOUR AS
SELECT
A.TS,
A.IFACE,
A.PORT,
B.SERVICENAME,
SUM(A.RX) AS RX,
SUM(A.TX) AS TX,
MAX(A.DMAJ) AS DMAJ,
SUM(A.NBECONNEXION) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORTHOUR AS A
LEFT JOIN MYTCPSTAT.TBSERVICENAME AS B
ON A.PORT=B.PORT
GROUP BY A.TS,A.IFACE,A.PORT,B.SERVICENAME;
 
CREATE VIEW VIPDAY AS
SELECT
A.TS,
A.IFACE,
A.IP,
B.COUNTRYCODE,
B.COUNTRYLIB,
B.TYPEIP,
SUM(A.RX) AS RX,
SUM(A.TX) AS TX,
MAX(A.DMAJ) AS DMAJ,
SUM(A.NBECONNEXION) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORTDAY AS A
LEFT JOIN MYTCPSTAT.TBIPCOUNTRY AS B
ON A.IP=B.IP
GROUP BY A.TS,A.IFACE,B.COUNTRYCODE,B.COUNTRYLIB;
 
CREATE VIEW VPORTDAY AS
SELECT
A.TS,
A.IFACE,
A.PORT,
B.SERVICENAME,
SUM(A.RX) AS RX,
SUM(A.TX) AS TX,
MAX(A.DMAJ) AS DMAJ,
SUM(A.NBECONNEXION) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORTDAY AS A
LEFT JOIN MYTCPSTAT.TBSERVICENAME AS B
ON A.PORT=B.PORT
GROUP BY A.TS,A.IFACE,A.PORT,B.SERVICENAME;
 
CREATE VIEW VIPMONTH AS
SELECT
A.TS,
A.IFACE,
A.IP,
B.COUNTRYCODE,
B.COUNTRYLIB,
B.TYPEIP,
SUM(A.RX) AS RX,
SUM(A.TX) AS TX,
MAX(A.DMAJ) AS DMAJ,
SUM(A.NBECONNEXION) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORTMONTH AS A
LEFT JOIN MYTCPSTAT.TBIPCOUNTRY AS B
ON A.IP=B.IP
GROUP BY A.TS,A.IFACE,B.COUNTRYCODE,B.COUNTRYLIB;
 
CREATE VIEW VPORTMONTH AS
SELECT
A.TS,
A.IFACE,
A.PORT,
B.SERVICENAME,
SUM(A.RX) AS RX,
SUM(A.TX) AS TX,
MAX(A.DMAJ) AS DMAJ,
SUM(A.NBECONNEXION) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORTMONTH AS A
LEFT JOIN MYTCPSTAT.TBSERVICENAME AS B
ON A.PORT=B.PORT
GROUP BY A.TS,A.IFACE,A.PORT,B.SERVICENAME;

Pour les regroupements tout IP tout Port (et obtenir uniquement des débits,  on peut créer de vrai tables, elles seront bien plus petites :

 

TBHOUR 
CREATE TABLE MYTCPSTAT.TBHOUR (
  IDHOUR bigint(20) NOT NULL AUTO_INCREMENT,
  TS datetime NOT NULL,
  IFACE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  RX decimal(24,6) NOT NULL DEFAULT '0.000000',
  TX decimal(24,6) NOT NULL DEFAULT '0.000000',
  DMAJ datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  NBECONNEXION BIGINT(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (IDHOUR ),
  UNIQUE KEY IX0HOUR (IFACE,TS)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
TBDAY 
CREATE TABLE MYTCPSTAT.TBDAY (
  IDDAY bigint(20) NOT NULL AUTO_INCREMENT,
  TS datetime NOT NULL,
  IFACE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  RX decimal(24,6) NOT NULL DEFAULT '0.000000',
  TX decimal(24,6) NOT NULL DEFAULT '0.000000',
  DMAJ datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  NBECONNEXION BIGINT(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (IDDAY ),
  UNIQUE KEY IX0DAY (IFACE,TS)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
TBMONTH 
CREATE TABLE MYTCPSTAT.TBMONTH (
  IDMONTH bigint(20) NOT NULL AUTO_INCREMENT,
  TS datetime NOT NULL,
  IFACE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  RX decimal(24,6) NOT NULL DEFAULT '0.000000',
  TX decimal(24,6) NOT NULL DEFAULT '0.000000',
  DMAJ datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  NBECONNEXION BIGINT(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (IDMONTH ),
  UNIQUE KEY IX0MONTH (IFACE,TS)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Et pour les remplir on fera comme pour les précédentes. Je ne pense pas qu’une suppression régulière soit réellement indispensable.

INSERT INTO MYTCPSTAT.TBHOUR
(IFACE,TS,RX,TX,DMAJ,NBECONNEXION)
SELECT X.IFACE, X.TS, X.RX, X.TX, X.DMAJ, X.NBECONNEXION
FROM (
SELECT
    A.IFACE,
    A.TS,    
    SUM(A.RX) AS RX,
    SUM(A.TX) AS TX,
    MAX(A.DMAJ) AS DMAJ,
    COUNT(*) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORTHOUR AS A
GROUP BY A.IFACE, A.TS
) AS X
ON DUPLICATE KEY UPDATE RX=X.RX, TX=X.TX, DMAJ=X.DMAJ, NBECONNEXION=X.NBECONNEXION;
INSERT INTO MYTCPSTAT.TBDAY
(IFACE,TS,RX,TX,DMAJ,NBECONNEXION)
SELECT X.IFACE, X.TS, X.RX, X.TX, X.DMAJ, X.NBECONNEXION
FROM (
SELECT
A.IFACE,
A.TS, 
SUM(A.RX) AS RX,
SUM(A.TX) AS TX,
MAX(A.DMAJ) AS DMAJ,
COUNT(*) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORTDAY AS A
GROUP BY A.IFACE, A.TS
) AS X
ON DUPLICATE KEY UPDATE RX=X.RX, TX=X.TX, DMAJ=X.DMAJ, NBECONNEXION=X.NBECONNEXION;

 

INSERT INTO MYTCPSTAT.TBMONTH
(IFACE,TS,RX,TX,DMAJ,NBECONNEXION)
SELECT X.IFACE, X.TS, X.RX, X.TX, X.DMAJ, X.NBECONNEXION
FROM (
SELECT
A.IFACE,
A.TS, 
SUM(A.RX) AS RX,
SUM(A.TX) AS TX,
MAX(A.DMAJ) AS DMAJ,
COUNT(*) AS NBECONNEXION
FROM MYTCPSTAT.TBIPPORTMONTH AS A
GROUP BY A.IFACE, A.TS
) AS X
ON DUPLICATE KEY UPDATE RX=X.RX, TX=X.TX, DMAJ=X.DMAJ, NBECONNEXION=X.NBECONNEXION;

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.