pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJOINing based on whether an IP address is contained within a CIDR range?
5+ messages / 5 participants
[nested] [flat]
* JOINing based on whether an IP address is contained within a CIDR range?
@ 2007-10-25 18:22 Jamie Tufnell <diesql@googlemail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Jamie Tufnell @ 2007-10-25 18:22 UTC (permalink / raw)
To: pgsql-sql
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!
Thanks,
JST
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: JOINing based on whether an IP address is contained within a CIDR range?
@ 2007-10-25 19:21 Erik Jones <erik@myemma.com>
parent: Jamie Tufnell <diesql@googlemail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Erik Jones @ 2007-10-25 19:21 UTC (permalink / raw)
To: Jamie Tufnell <diesql@googlemail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: JOINing based on whether an IP address is contained within a CIDR range?
@ 2007-10-25 19:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jamie Tufnell <diesql@googlemail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2007-10-25 19:32 UTC (permalink / raw)
To: Jamie Tufnell <diesql@googlemail.com>; +Cc: pgsql-sql
"Jamie Tufnell" <diesql@googlemail.com> writes:
> Is it possible to somehow JOIN using the <<= and
> = network operators?
Sure, why not?
You probably won't get a plan that's smarter than a nestloop, but I
imagine you'll have few enough rows in network_names that it won't
matter.
One potential problem is if there could be overlapping entries in
network_names --- the join would give you multiple rows for any
log entry that matches more than one network_names entry. You
could fix that using a subselect with LIMIT 1, as long as you
weren't too picky about which name got reported.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: JOINing based on whether an IP address is contained within a CIDR range?
@ 2007-10-26 11:44 Harald Fuchs <hf0217x@protecting.net>
parent: Jamie Tufnell <diesql@googlemail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Harald Fuchs @ 2007-10-26 11:44 UTC (permalink / raw)
To: 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 ;-)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: JOINing based on whether an IP address is contained within a CIDR range?
@ 2007-10-26 12:03 Jonah H. Harris <jonah.harris@gmail.com>
parent: Harald Fuchs <hf0217x@protecting.net>
0 siblings, 0 replies; 5+ messages in thread
From: Jonah H. Harris @ 2007-10-26 12:03 UTC (permalink / raw)
To: Harald Fuchs <hf0217x@protecting.net>; +Cc: pgsql-sql
On 10/26/07, Harald Fuchs <hf0217x@protecting.net> wrote:
> 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 ;-)
Yes:
ip4r('ip') <<= ip4r(cidr('cidr'))
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-10-26 12:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-25 18:22 JOINing based on whether an IP address is contained within a CIDR range? Jamie Tufnell <diesql@googlemail.com>
2007-10-25 19:21 ` Erik Jones <erik@myemma.com>
2007-10-25 19:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-26 11:44 ` Harald Fuchs <hf0217x@protecting.net>
2007-10-26 12:03 ` Jonah H. Harris <jonah.harris@gmail.com>
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