Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 0B5A29F9268 for ; Thu, 25 Oct 2007 15:50:52 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 81517-02 for ; Thu, 25 Oct 2007 15:50:39 -0300 (ADT) X-Greylist: delayed 00:21:45.669092 by SQLgrey-1.7.5 Received: from rn-out-0102.google.com (rn-out-0910.google.com [64.233.170.186]) by postgresql.org (Postfix) with ESMTP id 646979F9A1F for ; Thu, 25 Oct 2007 15:50:46 -0300 (ADT) Received: by rn-out-0102.google.com with SMTP id s46so671438rnb for ; Thu, 25 Oct 2007 11:50:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=QEl/TsRrgJgSAejpdfObumpUN3t6SnODj16KIz1eaXs=; b=UI1+23Gwchbh1jhEnwIn0WFYZdi/i4o2D7QGR6P/EeZKb9+0HM8PNae8yiFxRSRHCzQWG6KHX2qtGz91/ZkvbmNKZCS9shWDceoj8obLOpF8hpQ9+gHlEpt7Zoqu5G5GvrhMkUmJ//8h1vhi9NqkyAB2U0UGgvcnr540HW24ELw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=hKv+e4WhqaaplDlSrJYiQ+eNiDNYGtcQ9k5jfvoEzBTJJU6ce0SYxPgmZ1R8U35fCr0Nba03UrN9crVtvePLooIREvA/OqZnhmH59SlmKd/dG87He41RUtagDovJPDlUFH8ies/uPG0H1mBhWful4b4JFuekcP3VunY+XbIMjYo= Received: by 10.150.153.19 with SMTP id a19mr396551ybe.1193336562043; Thu, 25 Oct 2007 11:22:42 -0700 (PDT) Received: by 10.35.14.6 with HTTP; Thu, 25 Oct 2007 11:22:41 -0700 (PDT) Message-ID: Date: Thu, 25 Oct 2007 19:22:41 +0100 From: "Jamie Tufnell" To: pgsql-sql@postgresql.org Subject: JOINing based on whether an IP address is contained within a CIDR range? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2544_16281925.1193336562026" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/122 X-Sequence-Number: 29494 ------=_Part_2544_16281925.1193336562026 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_2544_16281925.1193336562026 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
------=_Part_2544_16281925.1193336562026--