Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 54B349F9450 for ; Thu, 25 Oct 2007 16:22:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 53142-07 for ; Thu, 25 Oct 2007 16:22:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtp136.iad.emailsrvr.com (smtp136.iad.emailsrvr.com [207.97.245.136]) by postgresql.org (Postfix) with ESMTP id B8B859F9F53 for ; Thu, 25 Oct 2007 16:22:09 -0300 (ADT) Received: from relay3.r3.iad.emailsrvr.com (localhost [127.0.0.1]) by relay3.r3.iad.emailsrvr.com (SMTP Server) with ESMTP id C397F44C273; Thu, 25 Oct 2007 15:22:07 -0400 (EDT) Received: by relay3.r3.iad.emailsrvr.com (Authenticated sender: erik-AT-myemma.com) with ESMTP id E93C744C102; Thu, 25 Oct 2007 15:22:03 -0400 (EDT) In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed Message-Id: Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable From: Erik Jones Subject: Re: JOINing based on whether an IP address is contained within a CIDR range? Date: Thu, 25 Oct 2007 14:21:49 -0500 To: Jamie Tufnell X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/124 X-Sequence-Number: 29496 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 =20 > 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 =20 > against an interesting (to me at least!) problem.. > > I basically want the same output as in http_log, but substituting =20 > 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 =20 > http_log could have >100000 rows) Is it possible to somehow JOIN =20 > using the <<=3D and >>=3D network operators? Or would I have to =20 > iterate the network_names table manually with LOOP (or something) =20 > 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-=20 net.html Erik Jones Software Developer | Emma=AE 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