Re: JOINing based on whether an IP address is contained within a CIDR range? - Mailing list pgsql-sql

From Harald Fuchs
Subject Re: JOINing based on whether an IP address is contained within a CIDR range?
Date
Msg-id pud4v2xfqp.fsf@srv.protecting.net
Whole thread Raw
In response to JOINing based on whether an IP address is contained within a CIDR range?  ("Jamie Tufnell" <diesql@googlemail.com>)
Responses Re: JOINing based on whether an IP address is contained within a CIDR range?
List pgsql-sql
In article <b0a4f3350710251122y10648d4id322f12a81001c07@mail.gmail.com>,
"Jamie Tufnell" <diesql@googlemail.com> writes:

> Hi,
> I am storing a log of HTTP requests in a database table (including IP address):

> http_log: id(PK), path, time, ip

> I have another table that contains CIDR ranges and names for them:

> network_names: id(PK), cidr, name

> Some example data for both tables:

> network_names:
> 1, 192.168.0.0/24, 'Engineering'
> 2, 192.168.1.0/24, 'Media'
> 3, 192.168.2.0/24, 'Engineering'
> 4, 192.168.3.0/24, 'Accounting'
> 5, 192.168.4.0/24, 'Engineering'
> 6, 10.0.0.0/8, 'Engineering'

> http_log:
> 1, '/index.html', 110000001, 192.168.0.47/32
> 2, '/index.html', 110000023, 200.1.2.3/32
> 3, '/index.html', 110000059, 1.2.3.4/32
> 4, '/index.html', 110000232, 192.168.2.1/32
> 5, '/index.html', 113919102, 192.168.1.39/32
> 6, '/index.html', 129101293, 10.2.2.4/32
> 7, '/index.html', 132828282, 192.168.4.2/32

> Now, in trying to produce a report on this data, I've come up against an
> interesting (to me at least!) problem..

> I basically want the same output as in http_log, but substituting the IP with
> the network name where available, i.e:

> 1, '/index.html', 110000001, Engineering
> 2, '/index.html', 110000023, 200.1.2.3/32
> 3, '/index.html', 110000059, 1.2.3.4/32
> 4, '/index.html', 110000232, Engineering
> 5, '/index.html', 113919102, Media
> 6, '/index.html', 129101293, Engineering
> 7, '/index.html', 132828282, Engineering

> I'm wondering what the best way of doing this is (considering that http_log
> could have >100000 rows)  Is it possible to somehow JOIN using the <<= and >>=
> network operators?

There are PostgreSQL builtin functions for that, but I think they are
unable to use indexes. I use http://pgfoundry.org/projects/ip4r/
and I think it's the best thing since the invention of sliced bread ;-)



pgsql-sql by date:

Previous
From: Sébastien Meudec
Date:
Subject: Re: get only rows for latest version of contents
Next
From: "Jonah H. Harris"
Date:
Subject: Re: JOINing based on whether an IP address is contained within a CIDR range?