From: Erik Jones <erik@myemma.com>
To: Jamie Tufnell <diesql@googlemail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: JOINing based on whether an IP address is contained within a CIDR range?
Date: Thu, 25 Oct 2007 14:21:49 -0500
Message-ID: <CD887185-848D-4121-B96C-DC14F2CC61B2@myemma.com> (raw)
In-Reply-To: <b0a4f3350710251122y10648d4id322f12a81001c07@mail.gmail.com>
References: <b0a4f3350710251122y10648d4id322f12a81001c07@mail.gmail.com>
On Oct 25, 2007, at 1:22 PM, Jamie Tufnell wrote:
> 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? Or would I have to
> iterate the network_names table manually with LOOP (or something)
> on every row of the http_log?
>
> If anyone can share some advice, that would be great!
Check out: http://www.postgresql.org/docs/8.2/interactive/functions-
net.html
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com, diesql@googlemail.com
Subject: Re: JOINing based on whether an IP address is contained within a CIDR range?
In-Reply-To: <CD887185-848D-4121-B96C-DC14F2CC61B2@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox