Scripts SQL simples

Scripts SQL simples jpp ven 05/08/2016 - 19:59

Quelques scripts permettant de répondre à quelques questions simples sur ce qui "passe" sur votre réseau.

Qu'a fait l'adresse XXX.XXX.XXX.XXX ?
Avec appoint de la table personnelle IPV4_DOMAINE, cf en fin de page pour le téléchargement des scripts).

SELECT FW.INTERFACE_ID NIC,inet_ntoa(FW.IP_SRC_ADDR) SRCIP ,FW.L4_SRC_PORT SPORT, inet_ntoa(FW.IP_DST_ADDR) DSTIP,FW.L4_DST_PORT DPORT,
FW.PROTOCOL TCPIP, FW.IN_BYTES, FW.OUT_BYTES,FW.PACKETS,
DATE_FORMAT(FROM_UNIXTIME(FW.FIRST_SWITCHED), '%H:%i:%s %d-%m-%Y') DEBUT,
DATE_FORMAT(FROM_UNIXTIME(FW.FIRST_SWITCHED), '%H:%i:%s %d-%m-%Y') FIN,
FW.INFO,DO.CODPAYS,DO.SRV,DO.DOM,DO.COMPAGNIE
FROM ntopng.flowsv4 FW
left join ntopng.IPV4_DOMAINE DO on ( FW.IP_SRC_ADDR = DO.IP_V4 or FW.IP_DST_ADDR = DO.IP_V4=
where (    FW.IP_SRC_ADDR = inet_aton('XXX.XXX.XXX.XXX')
        or FW.IP_DST_ADDR = inet_aton('XXX.XXX.XXX.XXX')
       )
and FW.L4_DST_PORT != 123
order by 10
limit 32

Bien sûr la clause "limit 32" peut être modifiée ! Pour voir plus de 32 heureux sélectionnés.

Plus simple une statistique par jour.heure :

select (DATE_FORMAT(FROM_UNIXTIME(FIRST_SWITCHED),'%Y-%m-%d')) jour,
(DATE_FORMAT(FROM_UNIXTIME(FIRST_SWITCHED),'%H')) heure,count(*)
from ntopng.flowsv4
group by 1,2

Plus drôle (??), quels sont les ports des visiteurs classés en décroissant :

SELECT FW.L4_SRC_PORT, count(*)
FROM ntopng.flowsv4 FW
where FW.IP_SRC_ADDR <> inet_aton('127.0.0.1')
and FW.IP_SRC_ADDR not between inet_aton('192.168.1.0') and inet_aton('192.168.3.255')
and FW.IP_DST_ADDR not between inet_aton('224.0.0.1') and inet_aton('224.255.255.255')
and FW.IP_DST_ADDR not between inet_aton('239.0.0.1') and inet_aton('239.255.255.255')
and FW.IP_DST_ADDR <> inet_aton('255.255.255.255')
group by 1
order by 2 desc

Celui-ci utilise aussi la table "IPV4_DOMAINE" que j'ai créée pour conserver les renseignements sur les IP que l'on retrouve dans les DNS et avec "geoiplookup". (Des liens de téléchargement des procédures en fin de la page suivante) :

select inet_ntoa(FW.IP_DST_ADDR) DSTIP,FW.L4_DST_PORT DPORT,inet_ntoa(FW.IP_SRC_ADDR) SRCIP,FW.INTERFACE_ID ETH,
DATE_FORMAT(FROM_UNIXTIME(FW.FIRST_SWITCHED), '%Y-%m-%d') DEBUT,FW.INFO,DO.CODPAYS,
sum(FW.IN_BYTES) INB,sum(FW.OUT_BYTES) OUTB,sum(FW.PACKETS) PAQ,
DO.CODPAYS,DO.SRV,DO.DOM,DO.COMPAGNIE
from ntopng.flowsv4 FW
   left join ntopng.IPV4_DOMAINE DO on FW.IP_DST_ADDR = DO.IP_V4
where FW.IP_DST_ADDR not between inet_aton('127.0.0.1') and inet_aton('127.255.255.255')
and   FW.IP_DST_ADDR not between inet_aton('192.1.0.1') and inet_aton('192.168.3.255')
and   FW.IP_DST_ADDR not between inet_aton('224.0.0.1') and inet_aton('224.255.255.255')
and   FW.IP_DST_ADDR not between inet_aton('239.0.0.1') and inet_aton('239.255.255.255')
and      FW.IP_DST_ADDR <> inet_aton('255.255.255.255')
group by 1,2,3,4,5,6
order by IP_DST_ADDR,L4_DST_PORT,3

Pour rendre les listes plus lisibles ajoutez une table "PAYS" :

CREATE TABLE PAYS (
CODPAYS        char(2)     NOT NULL    DEFAULT '',
CODPAYS_3        char(3)     DEFAULT NULL,
ENG_NAME        varchar(40) DEFAULT NULL,
FR_NOM           varchar(40) DEFAULT NULL,
PRIMARY KEY (CODPAYS)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Cette table est disponible en téléchargement pour MySQL/MariaDB.

Allez, cela suffit pour aujourd'hui !