[php/mysql] landen van bezoekers achterhalen

[marty]

Voor een logscript bepaal ik het land van de bezoeker aan de extentie van z'n ip.
.nl = nederland, etc.

Het enige nadeel is, is dat ca 25% .net is, ca 20% .com en ca 20% unknown. dat is bij elkaar 2/3 van m'n bezoekers.

Nu heb ik al eens zitten zoeken op internet hoe ik dat beter kon krijgen en kwam via een post hier bij tweakers geloof (weet niet zeker meer) bij de RIPE database terecht. Daar heb ik nu een bestand van 270MB vandaan met alle ip-ranges en het land waartoe het behoort, maar ook nog een heleboel andere onzin.
Voorbeeldje:

inetnum: 80.16.151.184 - 80.16.151.191
netname: NETECONOMY-MG41731
descr: TELECOM ITALIA LAB SPA
country: IT
admin-c: TILS2-RIPE
tech-c: TILS2-RIPE
status: ASSIGNED PA
notify: [e-mail]
mnt-by: INTERB-MNT
changed: [e-mail] 20010824
source: RIPE

Wat zou nu een slimme manier zijn om verder te gaan?
1. een script schrijven wat alles in een tabel gooit die er zo uit ziet
id | land | ip_min | ip_max

zodat ik telkens tussen ip_min en ip_max m'n waarde kan matchen en het land terug krijg?

en is het dan slim bij het loggen van de user die tabel aan te spreken voor het land zodat ik die gelijk mee op kan slaan, of is dat te traag in jullie ogen? Ik zou het desnoods 1x kunnen doen (1e keer) en dan in de sessie op slaan (zowie een slim plan natuurlijk mocht ik voor deze optie gaan)

Of bestaat er nog een makkelijkere manier?
(ik weet dat scriptrequests hier uit den boze zijn, maar heeft iemand dit enorme bestand misschien al eens op een soortgelijke wijze geconvert en dat script nog liggen?)

p.s. als ik voor de optie ga van in een database gooien kom ik zowiezo met het probleem dat het derde groepje cijfers in de min en max ook kan verschillen...dus dat wordt nog lastig om precies te bepalen waar zo'n ip tussen valt.

[ Voor 8% gewijzigd door marty op 06-01-2003 04:57 ]

[RobIII]

Waar heb je dat bestand precies vandaan (linkje). Misschien dat ik kan helpen alles in een DB te gooien...

edit:
En als je de IP's opslaat als een LONG (4 bytes) dat kun je gewoon zoeken met iets als "Where (Bla < Bla) and (Bla > Bla)". Dat moet een redelijk snelle match opleveren, EN zit altijd in de range...Lijkt me.....)

edit:
Edit2: Onzin... Dat wat ik hierboven zeg over die ranges werkt natuurlijk niet <dom dom>, maar dan kun je je IP nog altijd verdelen over 4 velden en daar de range in zoeken..

[ Voor 84% gewijzigd door RobIII op 06-01-2003 05:04 ]

[Verwijderd]

Interessant. Waar staat dat bestand?

[marty]

hieroh:
ftp://ftp.ripe.net/ripe/dbase/split/ripe.db.inetnum.gz

[Stoffel]

hmm, dat bestand in een DB gooien zou inderdaad heel interessant zijn, ik zie dat er namelijk nog veel meer info dan alleen het land in staat.
eventjes in de gaten houden, anders probeer ik het vanavond zelf

[RobIII]

wordt nu gedownload

[Verwijderd]

Ideaal zeg! Alleen na invoer in een tabel, hoe groot wordt deze tabel dan?

[ Voor 75% gewijzigd door Verwijderd op 06-01-2003 11:28 ]

[RobIII]

Groot waarschijnlijk Ben nu effe proberen 't spul in SQL te knallen, kan effe duren daar ik een brak (lees: snel in mekaar gelfanst) tooltje heb gebouwd om 't spul te importeren in SQL.

[martinvw]

Wat voor tooltje *geinteresseerd is*

[ACM]

Ik heb deze database ooit es in een postgresql database gestopt. Postgresql heeft een aantal zeer handige opslagvelden hiervoor (ipadressen/netwerken+subnetten) en een stel functies waarmee je heel makkelijk kan controleren of een bepaald ip in een zeker subnet ligt etc.

In mysql zul je dat zelf moeten bouwen, volgens mij is een ip-range altijd een oplopende integer (als je het dus omzet naar een long/unsigned integer) en moet je wel met een
"where selected_ip_in_int_format BETWEEN range_start AND range_end" aardig verkomen met zoeken (vergeet geen index op je range_start en end te leggen, met een 200MB db zal dat vrij sloom te doorzoeken zijn )

Zelf had ik de database nog wat verder samengevoegd door alle aansluitende ranges ook samen te voegen tot grotere ranges, dat klusje kostte wel dik 90 minuten op mijn server toen en deed ik volledig met de postgresql functies.

[ Voor 3% gewijzigd door ACM op 06-01-2003 12:28 ]

[Joozt]

Je kunt ook op een andere manier de herkomst van je bezoekers achterhalen...

ik ben gisteren toevallig bezig geweest met een internetpagina in meerdere talen. nu kwam ik op een asp scriptje uit waarmee je de herkomst kunt bekijken.

zie: http://www.asptutorial.info/sscript/languages.asp
en http://www.asptutorial.info/sscript/languageslist.asp

[ACM]

Joozt schreef op 06 januari 2003 @ 12:28:
Je kunt ook op een andere manier de herkomst van je bezoekers achterhalen...

Volgens jouw script ben ik engels

[ Voor 39% gewijzigd door ACM op 06-01-2003 12:29 ]

[martinvw]

Dit is begonnen als php topic en jij komt met asp aan. Zoiets soortgelijks is natuurlijk ook met php mogelijk maar hiermee achterhaal je de taal en de taal verschilt toch regelmatig van herkomst.

[ Voor 27% gewijzigd door martinvw op 06-01-2003 12:32 ]

[RobIII]

De eerste 300392 records zitten erin, en running
Heb nu zo'n 109 Mb geparsed

[ThaDaNo]

offtopic:
[quote] Page into English, my defauld language [/quote]
woei professioneel script

[Stoffel]

maar er is nog steeds interesse in dat tooltje hoor

[RobIII]

Dat tooltje heb ik niet gesaved... * RobIII saved tijdelijke zooi nooit
Heb ondertussen wel de DB gevuld, 726.878 records in totaal. (Waren stuk of 4 records brak, die zijn misgegaan... )

DB is nu 19 Mb groot....Wie heeft er effe wat webspace waar ik 'm op kan pleuren?

[sjroorda]

sql-file bedoel je, of DB zelf?
(ofwel: heb je DB nodig?)

In geval van SQL-file: ok, ingeval van DB, ook ok, maar dan doe ik het hier lokaal (100MBit-verbinding), maar mijn compu staat niet altijd aan (vaak wel, maar Xitami werkt niet altijd naar behoren )

[ Voor 87% gewijzigd door sjroorda op 06-01-2003 13:35 ]

[RobIII]

Heb een MDF van Microsoft SQl server. Maar kan ook een export maken in XML of whatever. Een tekstfile-tje is ook zo gemaakt... U roept, wij draaien ;-)

[Stoffel]

een dump voor mysql?

[sjroorda]

heb hier een MySQL-db draaien, als je naar een serie SQL-queries kan converten wil ik die hier wel neerzetten, weet eerlijk gezegd niet of PHPMyAdmin met XML kan omgaan...

Misschien het handigst om gewoon de MDF neer te zetten, dan kan iedereen ermee doen wattie wil?

Ik heb wel wat ruimte daarvoor

[ Voor 6% gewijzigd door sjroorda op 06-01-2003 13:41 ]

[marty]

Ik heb eventueel ook wel beschikking over een server waar ik die dumpfile een tijdje neer kan zetten.

acm volgens mij is een ip-range ook een oplopende integer, alleen zit je met het probleem dat niet ieder ip een gelijk aantal cijfers heeft. 193.158.120.111 heeft er 12 en 80.84.112.4 heeft er maar 8. dus dat wordt nog lastig om daar een passende query voor te verzinnen volgens mij

[sjroorda]

marty schreef op 06 januari 2003 @ 13:51:
acm volgens mij is een ip-range ook een oplopende integer, alleen zit je met het probleem dat niet ieder ip een gelijk aantal cijfers heeft. 193.158.120.111 heeft er 12 en 80.84.112.4 heeft er maar 8. dus dat wordt nog lastig om daar een passende query voor te verzinnen volgens mij

Als je een IP-adres omzet naar hex waarden, heb je gewoon een int! Eventueel kan je ook een decimale int maken.

[hobbit_be]

en 80.84.112.4 schrijven als 080.084.112.004 werkt ook natuurlijk ... (niet dat dit een valid IP is maar snel genoeg gemaakt... ) Hex vindt ik idd ook best leuk.

Ook ik kan beschikken over een T3 server die nog wel een paar jaar online gaat staan. Dus voor die file effe te dumpen laat me iets weten [e-mail]. Maak ik ineens een webpagina voor de GoT ers om het te downloaden (in welk formaat ook)...

[marty]

dat gaat wel lukken ja ;-)

moet je alleen wel alles in je database in stukken hakken en nullen toevoegen. of niet?

[ Voor 87% gewijzigd door marty op 06-01-2003 14:06 . Reden: laatste post nog niet gezien ]

[sjroorda]

Even duidelijker (en het wordt steeds meer offtopic):

- reken de 4 stukjes IP om naar hex, en zet deze achter elkaar: 255.255.255.0 wordt FF FF FF 00 en dit kan je als 'gewone' hex-integer verwerken; deze kan je evt ook omrekenen naar decimaal
- of maak er een decimale int van door te 'schalen' (of hoe je dat dan ook wilt noemen): 123.45.67.89 wordt

code:

(123 * 256^3) + (45 * 256^2) + (67 * 256^1) + (89 * 256^0) = 2.066.563.929
( == (123 * 256^3) + (45 * 256^2) + (67 * 256) + 89)

[RobIII]

Ik heb ze er nu zo in zitten:
ipfrom0, ipfrom1, ipfrom2, ipfrom3, ipto0, ipto1, ipto2, ipto3, ipfroms, iptos, country

waar:
* ipfrom0..3 is byte 0..3 van het begin ip adres
* ipto0..3 is byte 0..3 van het eind ip adres
* ipfroms en iptos de strings van de ip's zijn (dus 80.16.151.184)
* en country een 2-char brede country naam is

Ik dacht effe snel een query in mekaar te flansen om die ip's ook nog effe als long op te slaan (eigenlijk wordt dat een bigint...) maar dat viel dus effe tegen...
Ben er nog mee bezig. Als ik klaar ben zal ik hobbit_be mailen en op zijn T3 laten zetten...

edit:
de ellende is dat 'ie een overflow gooit als de MSB > 7F. Dat klopt ook wel, omdat het een SIGNED int is waar 'ie mee rekent, maar het veld waar het naar toe moet is een bigint. Dat zou moeten passen . Ik weet effe niet zo snel hoe ik 'm nou dwing er een bigint van te maken...

dit heb ik nu geprobeerd:
update tbl_ripedata set ipfrom = (ipfrom0 * 16777216) + (ipfrom1 * 65536) + (ipfrom2 * 256) + ipfrom3
from tbl_ripedata

iemand enig idee?

[ Voor 29% gewijzigd door RobIII op 06-01-2003 14:16 ]

[marty]

sjroorda schreef op 06 januari 2003 @ 14:08:
Even duidelijker (en het wordt steeds meer offtopic):

welnee, we zijn toch nog steeds bezig met als doel de landen van de bezoekers te achterhalen

[sjroorda]

Da's waar En het was ook niet zo bot bedoeld als het wellicht overkwam, zie ik nu het het overlees

[RobIII]

Iemand een idee voor mijn "probleem"? Zie edit vorige post van moi...

[marty]

[rob] en als je het veldtype ff tijdelijk wijzigt naar varchar?

[LuCarD]

RobIII schreef op 06 januari 2003 @ 14:10:
Ik heb ze er nu zo in zitten:
ipfrom0, ipfrom1, ipfrom2, ipfrom3, ipto0, ipto1, ipto2, ipto3, ipfroms, iptos, country

edit:
de ellende is dat 'ie een overflow gooit als de MSB > 7F. Dat klopt ook wel, omdat het een SIGNED int is waar 'ie mee rekent, maar het veld waar het naar toe moet is een bigint. Dat zou moeten passen . Ik weet effe niet zo snel hoe ik 'm nou dwing er een bigint van te maken...

dit heb ik nu geprobeerd:
update tbl_ripedata set ipfrom = (ipfrom0 * 16777216) + (ipfrom1 * 65536) + (ipfrom2 * 256) + ipfrom3
from tbl_ripedata

iemand enig idee?

INET_NTOA(expr)
Given a numeric network address (4 or 8 byte), returns the dotted-quad representation of the address as a string:
mysql> SELECT INET_NTOA(3520061480);
-> "209.207.224.40"
INET_ATON(expr)
Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4 or 8 byte addresses:
mysql> SELECT INET_ATON("209.207.224.40");
-> 3520061480
The generated number is always in network byte order; for example the above number is calculated as 209*256^3 + 207*256^2 + 224*256 +40.

hint?

[RobIII]

duh.. maar in MSSQL gaat dit niet lukken met een query dus...
Ik kan er wel effe weer een "tooltje" of "scriptje" voor in elkaar butsen die dat weer doet, maar ik zou het zo graag sql zelf laten doen. Is altijd sneller dan 726.878 records zelf doorfietsen.
En ik gebruik MSSQL...

[ Voor 14% gewijzigd door RobIII op 06-01-2003 14:48 . Reden: typo ]

[Sn3akz]

edit:
Mijn hulp werdt niet gewaardeerd

[ Voor 194% gewijzigd door Sn3akz op 06-01-2003 15:00 ]

[RobIII]

Da's waar Maar nu wil ik toch mijn query aan de pruttel hebben (en ik vind het zonde er een nieuw topic aan te besteden)

[marty]

Sn3akz schreef op 06 January 2003 @ 14:42:
Dit is makkelijker hoor
[...]

Moet je nog eens goed mijn allereerste post lezen

[Sn3akz]

marty schreef op 06 januari 2003 @ 14:47:
[...]
Moet je nog eens goed mijn allereerste post lezen

Scheld je mij nou uit voor koe?

Maar je heb gelijk.. als 2/3 van je bezoeker .net en .com is heb je er weinig aan... mijn excuses... Zal het weghalen voor meer overzicht

[marty]

geen schelden hoor
zocht een passend icoontje en vond die koe wel een leuke uitdrukking op z'n bakkes hebben

[marty]

rob, je kan 'm ook zo aan hobbit_be geven. ik heb wel mysql, zet ik dat laatste wel om met die functies van lucard. of is dat je eer te na?
btw, die suggestie het veld-type ff tijdelijk op varchar te zetten werkte ook niet?

moet trouwens wel over een klein uurtje de deur uit. dus dat wordt dan vanavond pas

[RobIII]

376 minuten resterend voor doorlopen van records Had ik het nou maar meteen gedaan

Maar we komen er wel

[FendtVario]

ripe-dbase-3.0.0.tar.gz is een server programma voor de whois, in de dir bin/load zit volgens mij een tool om de ripe.db naar een MySQL db te porten.

Misschien dat dat een paar van jullie problemen oplost, hij laat echter wel alle data in de db. Er staat ook al een db voor ipv6, deze is echter nog geen 400 kb groot.

Met php is het toch ook mogelijk om programma's uit te voeren op de server, is het dan niet mogelijk om de whois client uit te voeren en de uitvoer te parsen naar een html?

[Verwijderd]

- De van-tot IP's opslaan als integers m.b.v. ip2long en een index eroverheen leggen. Met BETWEEN de juiste range eruit pikken. Het IP nummer terugvertalen doe je met en long2ip.
- Er zijn IP ranges die over meerdere landen liggen. IP adressen kunnen in meerdere ranges voorkomen. Je moet de kleinste range hebben.

[Verwijderd]

marty schreef op 06 januari 2003 @ 04:54:
... bij de RIPE database terecht. Daar heb ik nu een bestand van 270MB vandaan met alle ip-ranges en het land waartoe het behoort...

De DB van RIPE is 1 van de 3 die je nodig hebt:


RIPE (http://www.ripe.net/) is the Reseaux IP Europeens (more commonly called the Regional Internet Registry for Europe). Located in Amsterdam, The Netherlands, RIPE provides support to ISPs, located in Europe, Middle East, and parts of Asia and Africa (check out http://www.ripe.net/ripencc/mem-services/general/index.html to see all the countries).

APNIC (http://www.apnic.net/) is the Asian Pacific Network Information Center. Located in Tokyo, Japan, APNIC provides support for all Asian countries. Currently there is no list of every individual country that falls under APNIC.

ARIN (http://www.arin.net/) is the American Registry for Internet Numbers. Located in Chantilly, VA, ARIN supports everybody else, including North and South America, the Caribbean, and the sub-Sahara Africa. Currently there is no list of every individual country that falls under ARIN.

[Anoniem: 75071]

http://www.location.com.my/products.asp - als je lui bent.

[RobIII]

Heb de DB van Ripe geconverteerd en terug geexporteerd naar een Comma Separated bestand. Gezipt is dat 12,5 Mb. Dus wie wil 'm nog hebben?

Verwijderd schreef op 06 januari 2003 @ 17:56:
[...]


De DB van RIPE is 1 van de 3 die je nodig hebt:
...

Dat meen je niet

[ Voor 43% gewijzigd door RobIII op 07-01-2003 12:14 ]

[marty]

RobIII schreef op 07 januari 2003 @ 12:04:
Heb de DB van Ripe geconverteerd en terug geexporteerd naar een Comma Separated bestand. Gezipt is dat 12,5 Mb. Dus wie wil 'm nog hebben?

IK!

Dat meen je niet

Aan het werk borst!

[Sebastiaan|IA2]

Is de file al ergens te downen ? Ik ben nl ook wel geinteresseerd

[Dryw.Filtiarn]

RobIII schreef op 07 januari 2003 @ 12:04:
Heb de DB van Ripe geconverteerd en terug geexporteerd naar een Comma Separated bestand. Gezipt is dat 12,5 Mb. Dus wie wil 'm nog hebben?

Ik wil hem ook wel hebben... maar misschien kan iemand hem ergens hosten?

[RobIII]

Um... Ja.. Ben net pas weer thuis. Heb niks meer gehoord van hobbit_be om 'm te hosten...Iemand met webspace over? Dan zal ik 'm er morgenvroeg opzetten.

* RobIII gaat eens slapen voor de verandering

[marty]

uhmmm... kun jij tijdelijk iets van een ftp openzetten waar ik 'm af kan halen. Het probleem is dat meerdere mensen gebruik maken van die server waar ik het op wil zetten en kan je daar geen login voor geven omdat ik anders ruzie met ze krijg. Heb al een uploadscriptje proberen neer te zetten, maar da wilde ook niet werken omdat ie niets groter dan 2MB toestaat.

en anders hopen dat je nog iets van hobbit hoort.
(of iemand anders)

[sjroorda]

ik heb wel webspace over; mail maar op [e-mail], dat praat wat makkelijker en iets meer on-topic

[djluc]

Marty: in PHP.INI kun je de grootte heel eenvoudig instellen. KAn dus ook met ini_set() enz.

[marty]

djluc schreef op 08 januari 2003 @ 15:55:
Marty: in PHP.INI kun je de grootte heel eenvoudig instellen. KAn dus ook met ini_set() enz.

da werkt op een of andere manier niet. hij staat dan een minuutje of twee te laden en zegt uiteindelijk dat de pagina niet beschikbaar is.

uploaden van kleinere bestanden lukt wel. dus het script werkt opzich goed

[djluc]

Kun je niet bij php.ini?

[marty]

nopes, het is mijn server niet

[ Voor 72% gewijzigd door marty op 08-01-2003 16:30 ]

[marty]

Rob, ik ben nog steeds heel erg geinteresseerd in die dump
Is er nog iemand die je benaderd heeft om 'm te hosten? Zoja, zou je mij kunnen vertellen waar ik 'm kan downen?
Ik wil 'm ook best hosten, alleen moet ik er dan wel even (tijdelijk) bij kunnen op 1 of andere manier

[Verwijderd]

http://www.phpdesk.nl/temp/afkomst.php

Misschien is dit iets wat je zoekt? Ik heb 'em nog niet veelvuldig getest, maar voor zover werkt ie prima.

Met dit scriptje wordt er nl met de whois van ripe.net geconnect en vanuit daar wordt de landcode eruit gestript.

Mits het aan de eisen doet, wil ik je de source wel vrijgeven...

[martinvw]

ja kewl, wil wel zien

[Spider.007]

Verwijderd schreef op 11 januari 2003 @ 16:52:
http://www.phpdesk.nl/temp/afkomst.php

Misschien is dit iets wat je zoekt? Ik heb 'em nog niet veelvuldig getest, maar voor zover werkt ie prima.

Met dit scriptje wordt er nl met de whois van ripe.net geconnect en vanuit daar wordt de landcode eruit gestript.

Mits het aan de eisen doet, wil ik je de source wel vrijgeven...

Naast het feit dat veel mensen alsnog de database zullen willen hebben zoals door RobIII gemaakt (deze bevat namelijk meer waardevolle info) is het denk voor de topicstarter (en de rest van GoT) hardstikke interessant als je de source even ergens neerzet

[marty]

vaag script
hij zet m'n (in php taal) HTTP_X_FORWARDED_FOR neer en daarachter dat het NL is
maar m'n HTTP_X_FORWARDED_FOR = een lokaal (intranet) ip en dat zou qua internet ip in amerika moeten liggen.
Dus hoewel ie het goede resultaat geeft (NL) klopt dat niet helemaal

Rob, leef je trouwens nog?

[Verwijderd]

marty schreef op 11 januari 2003 @ 20:59:
vaag script
hij zet m'n (in php taal) HTTP_X_FORWARDED_FOR neer en daarachter dat het NL is
maar m'n HTTP_X_FORWARDED_FOR = een lokaal (intranet) ip en dat zou qua internet ip in amerika moeten liggen.
Dus hoewel ie het goede resultaat geeft (NL) klopt dat niet helemaal

Deze IP grab functie zal toch wel goed zijn...:

PHP:

function ip()
{
    if(getenv('HTTP_X_FORWARDED_FOR'))
    { 
        return getenv('HTTP_X_FORWARDED_FOR'); 
    }
    else if(getenv('HTTP_CLIENT_IP'))
    {
        return getenv('HTTP_CLIENT_IP');
    }
    else
    { 
        return getenv('REMOTE_ADDR'); 
    }
}

Dacht ik tenminste

Spider.007 schreef op 11 januari 2003 @ 19:05:
is het denk voor de topicstarter (en de rest van GoT) hardstikke interessant als je de source even ergens neerzet

Ik moet de source nog wat aanpassen, maar mits ie echt klaar is zal ik 'em hier posten. Morgen oid

[marty]

Verwijderd schreef op 11 januari 2003 @ 21:58:
[...]
Deze IP grab functie zal toch wel goed zijn...:

PHP:

function ip()
{
    if(getenv('HTTP_X_FORWARDED_FOR'))
    { 
        return getenv('HTTP_X_FORWARDED_FOR'); 
    }
    else if(getenv('HTTP_CLIENT_IP'))
    {
        return getenv('HTTP_CLIENT_IP');
    }
    else
    { 
        return getenv('REMOTE_ADDR'); 
    }
}

Dacht ik tenminste

Ik zou de volgorde omdraaien. Anders krijg je bij mensen die in een netwerkje zitten de hun lokale ip te zien en dat wil je niet. REMOTE_ADDR geeft het ip van de bezoeker op internet weer

[Verwijderd]

marty schreef op 11 January 2003 @ 23:20:
[...]
Ik zou de volgorde omdraaien. Anders krijg je bij mensen die in een netwerkje zitten de hun lokale ip te zien en dat wil je niet. REMOTE_ADDR geeft het ip van de bezoeker op internet weer

Ben niet anders gewend geweest, maar zal 't wel even veranderen

[Spider.007]

marty schreef op 11 januari 2003 @ 23:20:
[...]
Ik zou de volgorde omdraaien. Anders krijg je bij mensen die in een netwerkje zitten de hun lokale ip te zien en dat wil je niet. REMOTE_ADDR geeft het ip van de bezoeker op internet weer

Maar bij proxies geeft REMOTE_ADDR het adres van de proxy aan, en dat probeert dit script juist te omzeilen

[marty]

tja...liever het adres van de proxy dan een lokaal ip lijkt me. dat komt altijd nog dichter in de buurt.

[Verwijderd]

Hmmm, das ook weer een kwestie. Wat wil je liever, het lokaal of het proxy IP adres...

[marty]

Verwijderd schreef op 12 January 2003 @ 09:36:
Hmmm, das ook weer een kwestie. Wat wil je liever, het lokaal of het proxy IP adres...

In dit geval denk ik proxy. Je bent immers landen aan het achterhalen (zie topic title). Een proxy zit in de meeste gevallen in hetzelfde land. Een lokaal ip adres kan echter, als het een netwerk ip betreft, van alles zijn

[Verwijderd]

marty schreef op 12 January 2003 @ 13:59:
In dit geval denk ik proxy. Je bent immers landen aan het achterhalen (zie topic title). Een proxy zit in de meeste gevallen in hetzelfde land. Een lokaal ip adres kan echter, als het een netwerk ip betreft, van alles zijn

Ik heb daar niet bijster veel verstand van, dus ik neem dat maar van je aan

[edie]

Hoeveel records moeten in het bestandje van RIPE staan? Ik heb nl een import scriptje in Perl geschreven (die er 10 min over deed) en hij heeft er 365.126 records ingezet, zo'n 53.5 MB.

Lijkt me een beetje weinig op een tekstbestand van 253 MB. Maar het kan aan mij liggen.

[marty]

dat klopt wel aardig hoor eddie
dit is de informatie die je per ip-range ziet:

inetnum: 80.16.151.184 - 80.16.151.191
netname: NETECONOMY-MG41731
descr: TELECOM ITALIA LAB SPA
country: IT
admin-c: TILS2-RIPE
tech-c: TILS2-RIPE
status: ASSIGNED PA
notify: [e-mail]
mnt-by: INTERB-MNT
changed: [e-mail] 20010824
source: RIPE

en daar kun je dus een hoop van wegflikkeren

[edie]

Maar ik heb alles geimporteerd; de volledige 253 MB dus (tenminste, dat was de bedoeling ).

Maargoed. Het zal wel kloppen.

[Anoniem: 75849]

RobIII schreef op 07 January 2003 @ 12:04:
Heb de DB van Ripe geconverteerd en terug geexporteerd naar een Comma Separated bestand. Gezipt is dat 12,5 Mb. Dus wie wil 'm nog hebben?


[...]

Dat meen je niet

Hebben? Ja tuurlijk man ! ik ben er al een hele tijd naar op zoek hoe kunnen we contacten?

FoX

[marty]

Ik heb rob een aantal dagen terug al ge-emailed, maar geen respons.
Aangezien ie hier nog wel berichten post op het forum en dus niet afwezig is, denk ik dat ie er gewoon geen zin meer in heeft.
Erg jammer, we waren al zo'n end.

[RobIII]

* RobIII is nog alive, alleen op een laag pitje....
Ik heb nog steeds geen account waar ik naartoe kan uploaden... Eerste die mij plek en username met password mailt wint

edit:

moet wel zeggen: Heb er al wat IP-tjes op losgelaten, maar er vallen er idd nogal wat buiten het "bekende" bereik.... Er is dus nog een DB-tje of 2 extra nodig?

[ Voor 36% gewijzigd door RobIII op 16-01-2003 10:29 ]

[sjroorda]

Doe ik nu...

Maarreh... wat is dan je mailadres???

Laat maar... al gevonden

[ Voor 87% gewijzigd door sjroorda op 16-01-2003 11:10 ]

[RobIII]

Is ge-uploaaaad
Sjroorda verstrekt de rest van de gegevens?

[sjroorda]

RobIII schreef op 16 January 2003 @ 12:20:
Is ge-uploaaaad
Sjroorda verstrekt de rest van de gegevens?

Dan is er iets misgegaan: ik heb een bestand van 3157 kb, en dat is een zipje dat niet kan worden gelezen?

[sjroorda]

Daar issie dan!

Hier en hier is het zipbestand (12.5 Mb) te downloaden.

Het is een CSV-file; voor degenen die geen zin hebben in code schrijven hieronder (werkende) code:

PHP:

    $f = fopen("ripedata", "r");

    // Eerste (dummy-)regel uitlezen
    $contents = fgetcsv($f, 100);

    // Rest van file uitlezen
    while ($contents = fgetcsv($f, 100))
    {
        $ipfrom  = $contents[1];
        $ipto    = $contents[2];
        $ipfroms = $contents[3];
        $iptos   = $contents[4];
        $country = $contents[5];
        $query = "INSERT INTO ips (ipfrom, ipto, ipfroms, iptos, country)
                  VALUES ($ipfrom, $ipto, '$ipfroms', '$iptos', '$country')");
    }

    fclose($f);

De dbtabel zelf kan je aanmaken bijvoorbeeld op deze manier (direct copy-paste uit PHPAdmin):

code:

CREATE TABLE `ips` (
    `ipr_id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `ipfrom` INT( 10 ) NOT NULL ,
    `ipto` INT( 10 ) NOT NULL ,
    `ipfroms` VARCHAR( 15 ) NOT NULL ,
    `iptos` VARCHAR( 15 ) NOT NULL ,
    `country` VARCHAR( 2 ) NOT NULL
);

Met dank aan RobIII voor het vervaardigen van het CSV'tje

[ZjieB]

Las het topic en heb ook wat lopen vogelen. Heb in ASP een scriptje gemaakt
dat het volgens mij ook doet. Ik doe het volgens mij een beetje op zijn Jan-
Boeren-Fluitjes, maar als het goed is, werkt het. Check hier voor een werkend
voorbeeld.

Ik laat hem kijken naar remote_addr en de database van RIPE inlezen (leest
hem direct van hun site, inclusief die van APNIC en ARIN...)

ASP:

<%
strIPBezoeker = Request.ServerVariables("REMOTE_ADDR")
Set objXmlHttp = Server.CreateObject("Msxml2.ServerXMLHTTP")
objXmlHttp.open "GET", "http://www.ripe.net/perl/whois?form_type=advanced&full_query_string=&searchtext=" & strIPBezoeker & "&do_search=Search&inverse_attributes=None&ip_search_lvl=Default&recursive=ON&domain_name_referral=ON&alt_database=ALL&object_type=All", False
objXmlHttp.send
strHTML = objXmlHttp.responseText
Set objXmlHttp = Nothing

strOutPut = Server.HTMLEncode(strHTML)
strCCheck = InStr(strOutPut,"country:")
strStaat = InStr(strOutPut,"status:")

do while strCCheck > 0
    strOudGetal = strCCheck + 1
    strOudStaat = strStaat + 1
    strBeginGetal = strCCheck + 14
    strBeginStaat = strStaat + 14
    strLand = Mid(strOutPut, strBeginGetal,2)
    strStutje = Mid(strOutPut, strBeginStaat,11)
        if strStutje = "ALLOCATED U" or strStutje = "UNSPECIFIED" then
        strWatBenIk = 0
        else
        strWatBenIk = 1
        strWaarVanDaan = strLand
        end if
    strCCheck = InStr(strOudgetal, strOutPut,"country:")
    strStaat = InStr(strOudStaat, strOutPut,"status:")
loop
response.write(strIPBezoeker & " - " & strWaarVanDaan)
%>

Wat ik hier aan het doen ben, kan dat ook zonder XML?

[ Voor 32% gewijzigd door ZjieB op 20-01-2003 01:57 ]

[marty]

wat heel erg kewl!!!
Ik flikker 'm vanavond ff in m'n db. Hoe groot wordt die dan ongeveer? (zit met m'n website helaas aan een quota - moet ff gaan slijmen of die niet omhoog mag )

Ik zal vanavond ook eens achter die andere 2 DBs aan gaan waar eerder over gesprokene werd. Eens kijken hoe we die kunnen bemachtigen..

[RobIII]

...
...

... graag gedaan

[Kees]

Overigens is die DB niet echt geoptimaliseerd
1. er zitten dubbele "ipto's" in
2. er zitten achtereenvolgende ranges in die naar hetzelfde land verwijzen

Als iemand belangstelling heeft heb ik een geoptimaliseerde database, welke niet over de hierbovengenoemde fouten beschikt

[ Voor 68% gewijzigd door Kees op 19-01-2003 02:06 ]

[Suepahfly]

Ik heb niet het hele topic door gelezen, maar voor zo ver ik het door heb is het de bedoeling om de het land aan de hand van de extentie te achterhalen en vervolgen op teslaan in een DB.

Wat je zou kunnen doen:

PHP:

$hostnaam = gethostbyaddr($REMOTE_ADDR);

mysql_query(INSERT INTO tabel (hostnaam) VALUES ('$hostnaam') or die(mysql_error());

Het stukje
gethostbyaddr($REMOTE_ADDR);

haalt de hostname op bv. aa123456-a.amstr1.nh.nl.home.com

(dit is een @home account)

[ Voor 2% gewijzigd door Suepahfly op 19-01-2003 03:56 . Reden: typo ]

[gorgi_19]

Maar in dit geval weet je dan niet uit welk land een bezoeker komt..

[Verwijderd]

Suepahfly schreef op 19 januari 2003 @ 02:49:
Ik heb niet het hele topic door gelezen, maar voor zo ver ik het door heb is het de bedoeling om de het land aan de hand van de extentie te achterhalen en vervolgen op teslaan in een DB.

Wat je zou kunnen doen:

...

Je kunt ook in NL zitten met een buitenlandse provider, en andersom...

[martinvw]

Kees schreef op 19 januari 2003 @ 01:34:
Overigens is die DB niet echt geoptimaliseerd
1. er zitten dubbele "ipto's" in
2. er zitten achtereenvolgende ranges in die naar hetzelfde land verwijzen

Als iemand belangstelling heeft heb ik een geoptimaliseerde database, welke niet over de hierbovengenoemde fouten beschikt

Geinteresseerd is

Zijn de twee andere database's al door iemand gevonden en omgezet?

[marty]

M4rt1nvW schreef op 19 January 2003 @ 20:35:
[...]
Zijn de twee andere database's al door iemand gevonden en omgezet?

Wordt op dit moment aan gewerkt door Kees!

[Spider.007]

Suepahfly schreef op 19 January 2003 @ 02:49:
Ik heb niet het hele topic door gelezen, maar

rara waar zit de fout
Oftewel; lees eerst even het topic door

Kees schreef op 19 January 2003 @ 01:34:
Overigens is die DB niet echt geoptimaliseerd
1. er zitten dubbele "ipto's" in
2. er zitten achtereenvolgende ranges in die naar hetzelfde land verwijzen

Als iemand belangstelling heeft heb ik een geoptimaliseerde database, welke niet over de hierbovengenoemde fouten beschikt

/me meld zich

[ Voor 48% gewijzigd door Spider.007 op 19-01-2003 22:49 ]

[Anoniem: 75071]

Kees schreef op 19 januari 2003 @ 01:34:
Overigens is die DB niet echt geoptimaliseerd
1. er zitten dubbele "ipto's" in
2. er zitten achtereenvolgende ranges in die naar hetzelfde land verwijzen

Als iemand belangstelling heeft heb ik een geoptimaliseerde database, welke niet over de hierbovengenoemde fouten beschikt

Zou ik hem ook kunnen ontvangen. Ik heb namelijk ook een DB van ip2location.com. Dan zal ik eens gaan kijken welke het accuraatst(?) is.

[marty]

Die van RIPE lijkt inderdaad ca 2/5 te coveren, in mijn geval tenminste; van de 2500 records kon ie er 1062 omzetten naar een land.

Nou, liep ik alleen wel tegen een probleempje aan met die DB van Kees. Daar stonden al die ips 'geschaald' in (als decimale int), of hoe dat ook mag heten. Nou is het me wel gelukt om in 1 query ipv gewoon het ip, het ip als decimale int te selecteren uit de database, maar vervolgens mag ik die alias niet in m'n where gebruiken....dus zo kan ik 'm niet op m'n bestaande log-tabel tabel matchen. Hoe zou je dat op kunnen lossen?

met die alias bedoel ik dus:
SELECT [onwijs lang gedeelte] AS ip_converted
dat ip_converted

[yeadder]

Kees schreef op 19 January 2003 @ 01:34:
Overigens is die DB niet echt geoptimaliseerd
1. er zitten dubbele "ipto's" in
2. er zitten achtereenvolgende ranges in die naar hetzelfde land verwijzen

Als iemand belangstelling heeft heb ik een geoptimaliseerde database, welke niet over de hierbovengenoemde fouten beschikt

ik heb belangstelling.

bijvoorbaat dank

[Kees]

Data:
http://www.tweakers.net/~kees/ip_country.sql.gz

Create:
http://www.tweakers.net/~kees/ip_country.create.sql

Hierin zitten de db's van RIPE, ARIN en APNIC

[JoostBaksteen]

thx Kees ik kan hem ook wel gebruiken, vet

[marty]

KEES: superbedankt. echt errug gaaf!!
(RobIII trouwens ook bedankt voor je inspanningen)

Maar zou dan alleen iemand me nog uit kunnen leggen hoe ik een tabel met geschaalde ips kan koppelen aan eentje met gewone ips?

Overigens, dit is de query waarmee ik nu een ip omzet naar een decimale int, maar dat kan volgens mij ook wel makkelijker:

code:

SELECT
    (
        (POW(256,3))*
        (substring(ip,1,(LOCATE('.',ip)-1)))
        ) +
    (
        (POW(256,2))*
        (substring(ip,(LOCATE('.',ip)+1),(((LOCATE('.',ip,(LOCATE('.',ip)+1))) - (LOCATE('.',ip)) - 1))))
        ) +
    (
        (256)*
        (substring(ip,((LOCATE('.',ip,(LOCATE('.',ip)+1)))+1),(((LOCATE('.',ip,(LOCATE('.',ip,(LOCATE('.',ip)+1))+1))) - (LOCATE('.',ip,(LOCATE('.',ip)+1))) - 1))))
        ) +
    (substring(ip,((LOCATE('.',ip,(LOCATE('.',ip,(LOCATE('.',ip)+1))+1)))+1))) AS converted
FROM log WHERE id=50

Je wil nie weten hoe lang ik daar mee bezig ben geweest

maargoed...nu moet ik de ip die daar uit kom nog vinden in de ip range van Kees z'n tabel...en dat wil niet lukken omdat ik converted niet in m'n where mag gebruiken en subqueries ook niet fijn gevonden worden door mysql

[Verwijderd]

$ip - gewone ip
$ipnum - resultaat

PHP:

$ip_seg = explode(".", $ip);        
for ($cnt=0;$cnt<4;$cnt++) {
    $ipnum += $ip_seg[$cnt] * pow(256,(3-$cnt));
}

of ip2long gebruiken en negative integers voorkomen met sprintf:

PHP:

$ipnum = sprintf("%u",ip2long($ip));

En ik weet niet hoe de tabel van Kees er uit ziet maar je kan toch gewoon testen of jouw IP in een bepaalde range valt?

[martinvw]

LuCarD schreef op 06 January 2003 @ 14:37:
[...]


quote:
----------------------------------------------------------------
INET_NTOA(expr)
Given a numeric network address (4 or 8 byte), returns the dotted-quad representation of the address as a string:
mysql> SELECT INET_NTOA(3520061480);
-> "209.207.224.40"
INET_ATON(expr)
Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4 or 8 byte addresses:
mysql> SELECT INET_ATON("209.207.224.40");
-> 3520061480
The generated number is always in network byte order; for example the above number is calculated as 209*256^3 + 207*256^2 + 224*256 +40.


----------------------------------------------------------------


hint?

Hierbij sluit ik mij aan:
hint?

[Verwijderd]

M4rt1nvW schreef op 21 januari 2003 @ 12:55:
[...]

Hierbij sluit ik mij aan:
hint?

die kende ik nog niet.
Stoer.

[ACM]

* ACM is mbv postgresql de tabel nog een beetje aan het inkrimpen. Er zitten nogal wat overlappende domeinen in die ripe-db (vanwege de routering en eigendommen enzo) die vaak wel in hetzelfde land zitten.

Ik heb er nu iig nog 96943 over en dat moeten er nog wel wat minder worden (nogal kostbare operatie )

Daarna ga ik nog de opeenvolgende domeinen van dezelfde landen samenvoegen, dat zijn er ook nog iets van 30k

[RobIII]

En volgende week is er een update van de DB en kunnen we allemaal opnieuw beginnen

Toch misschien beter om dit "live" te checken tegen een of andere DB?

[martinvw]

of een script wat de afhandeling van update's automatisch doet

Gewoon db downloaden met een leuk algoritme alleen de zinnige records overhouden en combineren en dan in de database pompen moet te doen zijn toch

Maar je kan het toch live doen door een socket te openen naar een whois server en een verzoekje te sturen?

[ Voor 24% gewijzigd door martinvw op 22-01-2003 14:57 ]