Alors que le développement du site qui exploite les données se termine, et que je m’apprêtai à réaliser l’article de présentation, il m’a parru évident qu’il lui faut 2 autres fonctionnalités en plus .
- Une répartition globale par Pays
- Une répartition globale part Port
En effet : comme je l’ai dit, TCPDUMP intercepte es trames avant le pare-feu, j’y vois donc toutes les tentatives de connexions, y compris, celles refusées par le pare-feu.
Mon serveur est destiné à un usage :
- en réseau local
- en web mais exclusivement de France (j’admets que Google : 8.8.8.8 ou un autre moteur de recherche en scanne les dossiers)
Les services ouverts sont :
- le Web (80 et 443)
- Le FTP
Regardons donc les IP et ports qui ont tenté de s’y connecter le 01/02 entre 9h et 9h59 (ci-dessous) : Vous voyez que 95% des tentatives ne concernent pas l’utilisation normale du serveur mais des tentatives de piratages !!. Et j’en ai des pages et des pages comme cela. D’où l’absolue nécessité d’avoir au moins un pare-feu voir un fail2ban en plus.
Donc deux nouvelles tables seront remplis par le traitement qui historise déjà les autres données
| Répartition par Pays et par mois | Répartition par Ports et par mois |
CREATE TABLE MYTCPSTAT.TBCOUNTRYMONTH ( IDCOUNTRYMONTH bigint(20) NOT NULL AUTO_INCREMENT, IFACE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, TS datetime NOT NULL, COUNTRYCODE char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, COUNTRYLIB` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 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 (IDCOUNTRYMONTH), UNIQUE KEY IX0COUNTRYMONTH (IFACE,TS,COUNTRYCODE), INDEX IX1CONTRYMONTH (COUNTRYCODE ASC) VISIBLE ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; ; |
CREATE TABLE MYTCPSTAT.TBPORTMONTH ( IDPORTMONTH bigint(20) NOT NULL AUTO_INCREMENT, IFACE varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, TS datetime NOT NULL, PORT int(11) NOT NULL, SERVICENAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 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 (IDPORTMONTH), UNIQUE KEY IX0PORTMONTH(IFACE,TS,PORT), INDEX IX1PORTMONTH (PORT ASC) VISIBLE ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; ; |
C’est deux tables seront alimentées par le script qui historise déjà les données :
| Répartition par Pays et par mois | Répartition par Ports et par mois |
INSERT INTO MYTCPSTAT.TBCOUNTRYMONTH
(IFACE,TS,COUNTRYCODE,COUNTRYLIB,RX,TX,DMAJ,NBECONNEXION)
SELECT IFACE,TS,COUNTRYCODE,COUNTRYLIB,RX,TX,DMAJ,NBECONNEXION
FROM
(
SELECT A.IFACE,A.TS,
COALESCE(B.COUNTRYCODE,'') AS COUNTRYCODE,
COALESCE(B.COUNTRYLIB,'Inconnu') AS COUNTRYLIB,
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.IFACE,A.TS,COALESCE(B.COUNTRYCODE,'') ,COALESCE(B.COUNTRYLIB,'Inconnu')
) AS M
ON DUPLICATE KEY UPDATE
RX=M.RX, TX=M.TX,NBECONNEXION=M.NBECONNEXION,DMAJ=M.DMAJ;
|
INSERT INTO MYTCPSTAT.TBPORTMONTH
(IFACE,TS,PORT,SERVICENAME,RX,TX,DMAJ,NBECONNEXION)
SELECT IFACE,TS,PORT,SERVICENAME,RX,TX,DMAJ,NBECONNEXION
FROM
(
SELECT A.IFACE,
A.TS,
A.PORT,
COALESCE(B.SERVICENAME,'Inconnu') AS 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.IFACE,A.TS,A.PORT ,COALESCE(B.SERVICENAME,'Inconnu')
) AS M
ON DUPLICATE KEY UPDATE
RX=M.RX, TX=M.TX,NBECONNEXION=M.NBECONNEXION,DMAJ=M.DMAJ;
|