Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

SQL Server : Un exemple pratique, trouver le pays d’un utilisateur par son IP (1 sur 3)

Voici un exemple intéressant, car complexe à plusieurs titres :

  • Les sources sont « compliquées » à trouver et pas forcément formatées de manière standard
  • Les données sont changeantes et doivent être rafraîchies, et le process d'alimentation doit en tenir compte
  • C'est assez compliqué à requêter

C'est pourquoi je vous propose en 3 parties de passer ces différents sujets.

Une adresse IP (v4 pour rester simple, même si comme on le verra passer en IP v6 sera très simple) est une succession de nombre qui représente votre adresse « unique » sur Internet. Unique pas vraiment pour tous les cas, car il y a tout un tas d'exception, mais pour ce qui concerne les adresses publiques c'est très certainement le cas.

En effet pour s'assurer de cette unicité des organismes sont chargés de la distribution de ces adresses IP. Elles ont été distribuées grossièrement à quelques très grosses entreprises, puis par continents. Chaque zone est chargée de l'enregistrement et de la distribution de ces plages d'adresses.

Comment peut-on connaître la localisation de l'adresse ?

C'est assez simple, les organes d'enregistrement de ces adresses tiennent des bases de données, avec les plages d'IP, les entreprises les ayant demandés et la zone géographique concernée. Même s'il est possible d'aller au détail de la localisation, nous nous arrêterons au pays. L'une des raisons à cela est que les bases de données de ces organes ne sont pas homogènes sur les formats de données.

Où trouver l'information ?

Ces informations ont les trouve via les serveurs Whois. On pourrait alors interroger les serveurs Whois directement. Oui, mais si on se place du côté serveur on n'aura pas forcément toujours une connexion à Internet et imaginez si un million d'adresse à traiter, le trafic réseau que cela va engendrer (et j'ai toutes les chances de me faire bannir du service en passant !).

L'idée est de constituer une table locale contenant ces informations (IP, Pays). Mais le problème c'est que ces données sont changeantes et que les modifications ont lieues tous les jours. Donc il va falloir gérer le rafraichissement de ces données !

Maintenant où va-t-on trouver ces informations sans passer par les Whois ? Eh bien tout simplement par les sources de données de ces Whois, ou plutôt une synthèse de ces derniers. Nous aurons besoins des sources des principales zones géographiques, c'est-à-dire :

  • Ripencc : Europe (je crois)
  • Apnic : Asie / Pacifique
  • Lacnic : Amérique Latine/ Caraïbes
  • Afrinic : Afrique
  • Arin : Amérique du Nord

Cette liste devrait combler plus de 99% de nos besoins. Pour les puristes, il existe d'autres organismes qui gèrent certaines plages d'IP originalement attribués à des grosses entreprise, donc à vous de chercher le 1% restant !

Maintenant au niveau des fichiers sources, ces organismes se copies leurs données à des fins de redondance. On a alors plus qu'à se connecter chez l'un d'entre eux en FTP pour récupérer les fichiers qui nous intéressent :

  • ftp.ripe.net
    • /pub/stats/ripencc/delegated-ripencc-latest
    • /pub/stats/apnic/delegated-apnic-latest
    • /pub/stats/lacnic/delegated-lacnic-latest
    • /pub/stats/arin/delegated-arin-latest
    • /pub/stats/afrinic/delegated-afrinic-latest

Les sources…

Dans ces fichiers la structure est la suivante :

On a des lignes de commentaires précédées de #

A la suite on a 4 lignes d'en-tête qui contiennent la date de modification des données, le nombre de ligne par type d'information présentes dans le fichier.

Pour les données proprement dites, séparées par le symbole « | » et la ligne terminé par un simple LF.

Pour les colonnes cela donne :

  • Nom de l'organisme (Texte)
  • Code pays ISO sur 2 lettres (Texte)
  • Type d'info : asn, ipv4, ipv6 (Texte)
  • Valeur, pour l'adresse de début de la plage (Texte)
  • Range, pour l'adresse IP le masque qui sert à déterminer la taille de la plage d'adresse (Entier)
  • Date d'attribution : AAAAMMJJ (Texte)
  • Status (Texte)

Le principal problème avec ces fichiers c'est l'entête qui peut être d'une longueur variable et les premières lignes qui ne contiennent pas un même nombre de colonnes.

… La destination

Dans quoi va-t-on ranger ces données ? La structure de la table sera assez simple… On a une IP dont on doit vérifier qu'elle est située entre 2 bornes (déduit par le couple IP + Range/Masque), et on le pays… En option je rajouterais la date de création de la zone, mais ça n'est pas obligatoire.

L'IP est un nombre de 4 octets. Ceci tient sans problèmes dans une colonne de type int, certes ce n'est pas la représentation la plus lisible, mais c''est de loin la plus efficace. En effet ils nous faudra être capable de trier sur ces colonnes (eh oui les index ne sont pas loin) et de plus cela limite la taille de la table à son minimum.

On aura alors :

CREATE TABLE dbo.IPv4(

    Country char(2) NOT NULL,

    IpDate date NULL,

    IPStart int NOT NULL,

    IPEnd int NOT NULL

)

 

Notez que je suis sous SQL Server, j'ai donc le type date, sous une version précédente, vous pouvez prendre un smalldate qui aura une plage de date plus que suffisante pour notre cas.

Voilà pour cette première partie, dans la suivante on va se charger de créer un lot SSIS pour intégrer ces données dans notre table.

Bon chargement…

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié jeudi 10 juin 2010 09:00 par christian
Classé sous : ,

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01