Discussion :

[Korben-Dallas]

Bonjour,

Je souhaiterai rechercher les adresses réseaux non déclarées dans une plage d'adresses mais je ne sais pas comment faire.

En fait, voilà ce que j'ai :
t_reseaux avec comme champs :
- AdresseDebutPlage
- AdresseFinPlage

t_Clients avec comme champs : - AdresseIP

J'arrive à récupérer le réseaux de l'adresse IP
mais je n'arrive pas à savoir si ce réseau existe dans les plages réseaux

En gros, si j'ai comme adresse t_Clients.adresseIP = 10.10.10.10
Comment faire pour savoir si celle-ci existe ou non dans la table t_reseaux
dans t_reseaux on a :
AdresseDebutPlage = 10.10.8.0
AdresseFinPlage = 10.10.20.255

Donc on peut remarquer que l'adresse 10.10.10.10 est bien incluse dans cette plage 10.10.8.0 à 10.10.20.255

En fait, jusqu'à maintenant j'arrive juste à vérifier si t_Clients.AdresseIP fait parti de la liste t_reseaux.AdresseDebutPlage ou t_reseaux.AdresseFinPlage.
Et dans ce cas précis (l'exemple), l'adresse n'en fait pas parti alors que dans la réalité ça devrait être le cas.

Comment faire ??
Merci pour aide.

Au fait !! Petite précision, tous les camps sont des VARCHAR(20)

[Korben-Dallas]

Voici par exemple ce qui permet de récupérer l'adresse réseau des clients. (récupération des 3 premiers digits.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SELECT DISTINCT
  LEFT(`t1`.`AdresseIP`, LOCATE('.', `t1`.`AdresseIP`, (CHAR_LENGTH(`t1`.`AdresseIP`) - 3))) AS `AdresseIP`,
FROM
  `t_Clients` `t1`
WHERE
  (`t1`.`AdresseIP` LIKE '%.%.%.___')
UNION
SELECT DISTINCT
  LEFT(`t1`.`AdresseIP`, LOCATE('.', `t1`.`AdresseIP`, (CHAR_LENGTH(`t1`.`AdresseIP`) - 2))) AS `AdresseIP`,
FROM
  `t_Clients` `t1`
WHERE
  (`t1`.`AdresseIP` LIKE '%.%.%.__')
UNION
SELECT DISTINCT
  LEFT(`t1`.`AdresseIP`, LOCATE('.', `t1`.`AdresseIP`, (CHAR_LENGTH(`t1`.`AdresseIP`) - 1))) AS `AdresseIP`,
FROM
  `t_Clients` `t1`
WHERE
  (`t1`.`AdresseIP` LIKE '%.%.%._')

Il y a probablement mieux mais celle-ci fonctionne et permet de transformer les adressesIP de type "10.111.122.23" en "10.111.122."

Que conseillez-vous ?
Merci pour l'aide. En espérant avoir été assez clair dans ma demande.

[JeitEmgie]

MySQL supporte les fonctions inet_*

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
mysql> select inet_aton('10.10.5.2'), inet_ntoa(168428802) ;
+------------------------+----------------------+
| inet_aton('10.10.5.2') | inet_ntoa(168428802) |
+------------------------+----------------------+
|              168428802 | 10.10.5.2            | 
+------------------------+----------------------+
1 row in set (0.00 sec)
 
 
mysql> select (inet_aton('10.10.10.10') >= inet_aton('10.10.8.0') and (inet_aton('10.10.10.10') <= inet_aton('10.10.20.255') ))  as isInRange ;
+-----------+
| isInRange |
+-----------+
|         1 | 
+-----------+
1 row in set (0.00 sec)

[Korben-Dallas]

OK Merci pour l'info.
Mais je ne sais pas comment je peux m'en servir.

En fait, je n'arrive pas à créer de requêtes permettant de me remonter t_Clients.AdresseIP si celles-ci ne sont pas comprises entre t_reseaux.AdressesDebutPlage et t_reseaux.AdressesFinPlage

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT C1.AdresseIP
FROM t_Clients AS C1, 
       t_Reseaux AS R1
WHERE ( inet_aton(C1.AdresseIP) >= inet_aton(R1.AdresseDebutPlage))  
  AND (inet_aton(C1.AdresseIP) <= inet_aton(R1.AdresseFinPlage))

Ceci est-ce correct ?? Et si c'est le cas, celle-ci permettrait de remonter les clients dont l'adresseIP est comprise dans la plage mais je voudrais remonter celles qui ne sont pas comprises dans les plages.
Comment faire ??
Merci pour l'aide.

[JeitEmgie]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
NOT( (X >= DEBUT) AND (X <= FIN) ) 
 
<=>
 
NOT(X >= DEBUT) OR NOT(X <= FIN)
 
<=>
 
(X < DEBUT) OR (X > FIN)

[Korben-Dallas]

Désolé mais là je n'ai pas tout compris.
Peux-tu développer svp ?
Merci.

[Korben-Dallas]

Voici la requête que j'ai faite mais celle-ci ne fonctionne pas. Elle me retourne même des clients qui se trouvent dans une des plages réseaux de la table t_reseaux.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT C1.AdresseIP
FROM t_Clients AS C1, 
       t_Reseaux AS R1
WHERE NOT((inet_aton(C1.AdresseIP) >= inet_aton(R1.AdresseDebutPlage)) AND (inet_aton(C1.AdresseIP) <= inet_aton(R1.AdresseFinPlage))) 
UNION
SELECT C1.AdresseIP
FROM t_Clients AS C1, 
       t_Reseaux AS R1
WHERE NOT(inet_aton(C1.AdresseIP) >= inet_aton(R1.AdresseDebutPlage)) OR NOT(inet_aton(C1.AdresseIP) <= inet_aton(R1.AdresseFinPlage))
UNION
SELECT C1.AdresseIP
FROM t_Clients AS C1, 
       t_Reseaux AS R1
WHERE (inet_aton(C1.AdresseIP) < inet_aton(R1.AdresseDebutPlage)) OR (inet_aton(C1.AdresseIP) > inet_aton(R1.AdresseFinPlage))

Merci pour votre rapide aide.
Là, je suis perdu.

[Korben-Dallas]

Bon j'ai cette requête qui semble fonctionner mais par contre elle est d'une énorme lenteur lorsqu'on a bcp de clients et réseaux.
Sur environ 50 Réseaux et 150 Clients elle m'affiche bien ce que je veux en moins d'1s mais là j'ai fait le test avec la base complète (50000 Clients et environ 1800 Réseaux) là ça fait 1/2h qu'elle tourne sans résultats.

Que faire ??
Merci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT DISTINCT CONCAT(LEFT(`C1`.`AdresseIP`, LOCATE('.', `C1`.`AdresseIP`, (CHAR_LENGTH(`C1`.`AdresseIP`) - 3))),0) AS `AdresseIP`
FROM t_Clients AS C1
WHERE (C1.AdresseIP) NOT IN 
      (
        SELECT DISTINCT C1.AdresseIP                                                  
        FROM t_Clients AS C1, 
             t_Réseaux AS R1
        WHERE ((inet_aton(C1.AdresseIP) >= inet_aton(R1.AdresseDebutPlage)) 
           AND (inet_aton(C1.AdresseIP) <= inet_aton(R1.AdresseFinPlage)))
      )

[Korben-Dallas]

Bonjour,

Voici une nouvelle correction de requête mais qui est super méga lente hélas.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT DISTINCT C1.Domaine, C1.Ordinateur, C1.AdresseIP, C1.Gestionnaire
FROM v_listeClients AS C1,
     v_Plages_Reseaux_OUS AS R1
WHERE C1.Gestionnaire = R1.Gestionnaire
  AND C1.Serveur = R1.Serveur
  AND ((C1.Gestionnaire, C1.Serveur, C1.AdresseIP) NOT IN 
      (
        SELECT DISTINCT C1.Gestionnaire, C1.Serveur, C1.AdresseIP                                                  
        FROM v_ListeClients AS C1, 
             v_Plages_Reseaux_OUS AS R1
        WHERE  (
                       C1.Gestionnaire = R1.Gestionnaire
                   AND C1.Serveur = R1.Serveur
                   AND (inet_aton(C1.AdresseIP) >= inet_aton(R1.AdresseDebutPlage)) 
                   AND (inet_aton(C1.AdresseIP) <= inet_aton(R1.AdresseFinPlage))
               )
      ))

Que faire ??
Merci pour votre aide, en espérant en avoir.

[JeitEmgie]

Query rapide =>

a. éviter les DISTINCT inutiles
b. avoir des index sur les champs principaux du critère si la quantité de données est importante (dans votre cas ce sont les champs avec les adresses IPs)

en principe

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select c.* from t_clients c, t_reseaux r where not(c.adresseIP between r.adresseDebutPlage and  r.adresseFinPlage)

devrait vous sortir le produit des 2 tables avec seulement les lignes t_clients qui n'ont pas d'adresse IP dans les plages de t_reseaux

(produit <==> une ligne par couple (client, réseau) qui correspond au critère "n'est pas dans le réseau"…)

et donc

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select distinct c.adresseIP from t_clients c, t_reseaux r where not(c.adresseIP between r.adresseDebutPlage and  r.adresseFinPlage)

devrait sortir rien que les adresses IP qui ne sont pas dans un réseau…

[Korben-Dallas]

Bonjour,

En fait, je suis parti de ces requêtes à la base et ceci me donne aussi les AdressesIP qui existent bien dans les plages réseaux
C'est bien ça le pb hélas.

[JeitEmgie]

Bonjour,

En fait, je suis parti de ces requêtes à la base et ceci me donne aussi les AdressesIP qui existent bien dans les plages réseaux
C'est bien ça le pb hélas.

c'est vos données qui sont mal rentrées alors…

probablement parce que vous avez mis les adresses IP sous forme de texte dans la DB…

les adresses doivent être stockées sous forme de "int(10) unsigned"
les inet_* ne servent qu'à convertir pour l'humain…

les comparaisons textuelles n'ont pas du tout le même sens…

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
 
mysql> select '10.10.10.10' < '10.9.10.10' ;
+------------------------------+
| '10.10.10.10' < '10.9.10.10' |
+------------------------------+
|                            1 | 
+------------------------------+
1 row in set (0.00 sec)
 
mysql> select inet_aton('10.10.10.10') < inet_aton('10.9.10.10') ;
+----------------------------------------------------+
| inet_aton('10.10.10.10') < inet_aton('10.9.10.10') |
+----------------------------------------------------+
|                                                  0 | 
+----------------------------------------------------+
1 row in set (0.00 sec)

[Korben-Dallas]

ha ok ok
en effet, dans mes tables, les adresses IP sont indiquées comme des VARCHAR(20)
Il faut donc que je modifie cela pour les mettre en INT UNSIGNED.
Mais le INT UNSIGNED est un nombre non ?? alors que les adresses sont constituées de "Nbre.Nbre.Nbre.Nbre" Il y a les "." entre chaque nombre.
Comment cela se traduit-il ?

Je viens d'essayer de voir pour modifier la structure des tables avec phpMyAdmin et je ne trouve pas le INT UNSIGNED.
Quelle est son nom exact svp ?
Ou sinon en écrivant une simple requête ALTER TABLE mais là non plus je ne connais pas ce paramètre.
Merci pour l'aide.

OK C'est bon, je viens de trouver comment modifier avec la requête ALTER TABLE INT(20) UNSIGNED

[Korben-Dallas]

Je viens de modifier une table pour que l'adresseIP ne soit pas du VARCHAR(20) mais du INT(11) UNSIGNED et là c'est encore pire, toutes les adresses sont transformées.
Par exemple si l'adresse était "10.22.145.125"
Celle-ci est devenue "1022"
J'ai donc remis en VARCHAR(20)

[Korben-Dallas]

J'ai aussi créé cette requête afin de trouver comment résoudre le pb.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT 
       C1.AdresseIP,
       inet_aton(C1.AdresseIP) AS 'AdresseIP',
       R1.AdresseDebutPlage,
       inet_aton(R1.AdresseDebutPlage) AS 'AdresseDebutPlage',
       R1.AdresseFinPlage,
       inet_aton(R1.AdresseFinPlage) AS 'AdresseFinPlage'
FROM        
     t_Client AS C1, 
     t_Reseaux AS R1
WHERE NOT  
      (inet_aton(C1.AdresseIP) BETWEEN inet_aton(R1.AdresseDebutPlage) AND inet_aton(R1.AdresseFinPlage))

Celle-ci me montre donc toutes les infos, et j'ai donc pu remarquer
que chaque C1.AdresseIP est comparée à la table R1.
Ce qui remonte donc que chaque adresse est au moins 1x non présente dans une des plages réseaux de la table t_Reseaux.
D'où le fait que j'ai aussi les remontées des adresses qui sont normalement dans une des plages réseaux.
En espérant avoir été clair dans ces explications et que ceci aidera aussi à résoudre le pb.

[JeitEmgie]

Je viens de modifier une table pour que l'adresseIP ne soit pas du VARCHAR(20) mais du INT(11) UNSIGNED et là c'est encore pire, toutes les adresses sont transformées.
Par exemple si l'adresse était "10.22.145.125"
Celle-ci est devenue "1022"
J'ai donc remis en VARCHAR(20)

évidemment…

il faut travailler en 2x :
créer 1 nouveau champ int par varchar(20)
faire un update pour initialiser les int avec inet_aton(ancien_champ_varchar_correspondant)
et ensuite supprimer les champs varchar qui ne servent plus à rien…

(et au passage en profiter pour ajouter des index sur les champs int…)

et finalement refaire les query sur les nouveaux champs…

[Korben-Dallas]

Que pensez-vous de cette requête ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT DISTINCT CONCAT(LEFT(C1.AdresseIP, LOCATE('.', C1.AdresseIP, (CHAR_LENGTH(C1.AdresseIP) - 3))), '0') AS `AdresseIP`
FROM t_Clients AS C1
WHERE NOT (C1.AdresseIP) IN
        (
          SELECT C1.AdresseIP
          FROM t_Clients AS C1, 
                 t_Serveurs AS R1
          WHERE inet_aton(C1.AdresseIP) BETWEEN inet_aton(R1.AdresseDebutPlage) AND inet_aton(R1.AdresseFinPlage)
        )

J'ai l'impression qu'elle fonctionne mais ne suis pas sûr, en fait je l'ai testé sur une table contenant peu de clients mais si je le fais sur la table contenant tous les clients, là ça risque d'être autres choses, je pense

[Korben-Dallas]

il faut travailler en 2x :
créer 1 nouveau champ int par varchar(20)
faire un update pour initialiser les int avec inet_aton(ancien_champ_varchar_correspondant)
et ensuite supprimer les champs varchar qui ne servent plus à rien…

(et au passage en profiter pour ajouter des index sur les champs int…)

et finalement refaire les query sur les nouveaux champs…

OK Je vais essayer de voir comme ça.
Si je comprends bien, il faut que je continu à importer comme actuellement mais lors de l'import il faut que je rajoute le traitement supplémentaire permettant de faire la modification dans la BDD au niveau des AdressesIP.
C'est bien ça ?

Par contre, je n'ai pas encore compris comment fonctionnent les index exactement.
J'ai beau lire les doc, ça ne reste pas encore très clair.
Serait-il possible d'avoir des explications supplémentaires à ce sujet ?
Merci

[JeitEmgie]

OK Je vais essayer de voir comme ça.
Si je comprends bien, il faut que je continu à importer comme actuellement mais lors de l'import il faut que je rajoute le traitement supplémentaire permettant de faire la modification dans la BDD au niveau des AdressesIP.
C'est bien ça ?

Par contre, je n'ai pas encore compris comment fonctionnent les index exactement.
J'ai beau lire les doc, ça ne reste pas encore très clair.
Serait-il possible d'avoir des explications supplémentaires à ce sujet ?
Merci

techniquement un index est simplement une table inverse triée qui contient la clé indexée et pointe vers le record original…

ce qui compte pour vous c'est qu'un index permet (en tous cas pour ce que vous devez faire ici…) des query plus rapides dès que le champ indexé est partie des critères dans la clause WHERE…
donc ici si vous indexez les champs IP au moins dans la table "réseaux" vous obtiendrez une amélioration des performances… du moins si la quantité de données est suffisante pour voir cette amélioration… çà ne changera pas grand chose sur quelques dizaines de records… sur quelques centaines çà se verra…

[Korben-Dallas]

OK Alors je viens de modifier ma base de données pour mettre ces nouveaux champs.
J'ai fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
ALTER TABLE `t_Clients` 
ADD `AdresseIP_Traitee` INT( 11 ) UNSIGNED NOT NULL ,
ADD INDEX ( `AdresseIP_Traitee` ) ;
 
ALTER TABLE `t_Reseaux` 
ADD `AdresseDebutPlage_Traitee` INT( 11 ) UNSIGNED NOT NULL ,
ADD `AdresseFinPlage_Traitee` INT( 11 ) UNSIGNED NOT NULL ,
ADD INDEX ( `AdresseDebutPlage_Traitee` ),
ADD INDEX ( `AdresseFinPlage_Traitee` ) ;

Et ensuite :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
UPDATE t_Clients
SET AdresseIP_Traitee = inet_aton(AdresseIP);
 
UPDATE t_Reseaux
SET AdresseDebutPlage_Traitee = inet_aton(AdresseDebutPlage),
    AdresseFinPlage_Traitee = inet_aton(AdresseFinPlage);

Les valeurs sont donc correctement mises en place.
Et maintenant je vais utiliser la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT DISTINCT CONCAT(LEFT(C1.AdresseIP, LOCATE('.', C1.AdresseIP, (CHAR_LENGTH(C1.AdresseIP) - 3))), '0') AS `AdresseIP`
FROM t_Clients AS C1
WHERE NOT (C1.AdresseIP) IN
        (
          SELECT C1.AdresseIP
          FROM t_Clients AS C1, 
                 t_Serveurs AS R1
          WHERE C1.AdresseIP_Traitee BETWEEN R1.AdresseDebutPlage_Traitee AND R1.AdresseFinPlage_Traitee
        )

Suis-je sur la bonne voie ?