Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Re: Perl+PostgreSQL+GeoIP = Awesome!

by Tux (Abbot)
on Nov 21, 2018 at 12:19 UTC ( #1226124=note: print w/replies, xml ) Need Help??

in reply to Perl+PostgreSQL+GeoIP = Awesome!

NICE! I will use this, but with a twist:

use 5.14.2; use warnings; use Text::CSV_XS qw( csv ); use DBI; use Socket; use Net::CIDR; my $tbl = "geoip"; my $dbh = DBI->connect ("dbi:Pg:dbname=cidr", undef, undef, { AutoCommit => 0, RaiseError => 1, PrintError => 1, ShowErrorStatement => 1, }); if (grep m/\b $tbl $/ix => $dbh->tables (undef, undef, undef, undef)) +{ say "Clear table $tbl"; $dbh->do ("truncate table $tbl"); } else { say "Create table $tbl"; $dbh->do (qq; create table $tbl ( netblock cidr not null primary key, type smallint not null, ip_from text not null, ip_to text not null, ip_from_n bigint, ip_to_n bigint, country_code text not null, country_name text not null); ); } my $sth = $dbh->prepare ("insert into $tbl values (?, ?, ?, ?, ?, ?, ? +, ?)"); foreach my $file (qw( GeoIPCountryWhois.csv GeoIPv6.csv )) { print "Inserting from $file...\n"; csv (in => $file, out => undef, allow_whitespace => 1, headers => [qw( firstip lastip x1 x2 iso name )], on_in => sub { foreach my $cidr (Net::CIDR::range2cidr ("$_{firstip}-$_{l +astip}")) { my @rng = Net::CIDR::cidr2range ($cidr); my ($f, $t) = split m/\s*-\s*/ => $rng[0]; my ($type, $F, $T) = $f =~ m/:/ ? (6, undef, undef) : (4, map { unpack "L>", inet_aton $_ } $f, $t); $sth->execute ($cidr, $type, $f, $t, $F, $T, $_{iso}, +$_{name}); } }, ); } $dbh->commit;

because I already have some scripts that use "L>" representations of IPv4 addresses as those are easy to check.

I did not know that this dataset was publicly available. I regularly check$ip in tools that analyze break-in attempts.

Enjoy, Have FUN! H.Merijn

Replies are listed 'Best First'.
Re^2: Perl+PostgreSQL+GeoIP = Awesome!
by cavac (Curate) on Nov 23, 2018 at 09:49 UTC

    Oooops, i just saw that this version of the databases isn't updated anymore: Deprecation notice: "Updated versions of the GeoLite Legacy databases are now only available to redistribution license customers, although anyone can continue to download the March 2018 GeoLite Legacy builds. Starting January 2, 2019, the last build will be removed from our website. GeoLite Legacy database users will need to switch to the GeoLite2 or commercial GeoIP databases and update their integrations by January 2, 2019."

    Working on an updated version of this post right now.

    perl -e 'use MIME::Base64; print decode_base64("4pmsIE5ldmVyIGdvbm5hIGdpdmUgeW91IHVwCiAgTmV2ZXIgZ29ubmEgbGV0IHlvdSBkb3duLi4uIOKZqwo=");'

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1226124]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (4)
As of 2020-09-24 02:02 GMT
Find Nodes?
    Voting Booth?
    If at first I donít succeed, I Ö

    Results (132 votes). Check out past polls.