SQL for IP subnets
Has anyone out there done any SQL with IP subnets that span large CIDR blocks? Here's my table structure:
CREATE TABLE "SUBNETS"
( "ID" NUMBER,
"OCTET1" NUMBER,
"OCTET2" NUMBER,
"OCTET3" NUMBER,
"OCTET4" NUMBER,
"CIDR" VARCHAR2(30),
"MASK" VARCHAR2(30),
"DESCRIPTION" VARCHAR2(255),
CONSTRAINT "SUBNETS_PK" PRIMARY KEY ("ID") ENABLE
)
This is used to keep track of networks under our management. In the table the 4 octet fields contain relevant entries for these networks. The CIDR column defines the size of the network, i.e a '/23' entry indicates that the network is 2 class C networks (2 x 256 hosts, 512 adresses), so the first host lives on one subnet and the last host lives on another.
An example would be a network entry of 192.168.12.0 /23. This network spans a range of addresses from 192.168.12.0 to 192.168.13.255. Notice that the third octet jumps one from the base address?
Anyway, we want to query these network records with a single IP address (my workstation for example) and identify which network it lives in, without having to store all the intervening addresses in our database.
Anyone done this and got some tricks you'd like to share?? Pleeeease!
Earl