find available IP

  • Thread starter Thread starter Song Su
  • Start date Start date
S

Song Su

I have tblIP which is already assigned like below:

198.188.10.1
198.188.10.2
198.188.10.4

first 8 digit (198.188.10) never change. I want some procedure to loop my
table to find available (un-assigned) ip, in this case
198.188.10.3
198.188.10.5
198.188.10.11
etc.

The last octet can range from 1 to 200. Can anyone help? Thanks.
 
Substitute you field name for IPAddress and try this --
SELECT Left(Q.[IPAddress],11) & Right(Q.[IPAddress],Len(Q.[IPAddress])-11)+1
AS [Next IP]
FROM tblIP AS Q
WHERE ((((SELECT COUNT(*) FROM tblIP Q1
WHERE Left(Q.[IPAddress],11) = Left(Q1.[IPAddress],11) AND
Right(Q1.[IPAddress],Len(Q1.[IPAddress])-11) >=
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)))=1))
ORDER BY Left(Q.[IPAddress],11) &
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)+1,
Right(Q.[IPAddress],Len(Q.[IPAddress])-11) DESC;
 
Thanks for quick reply. However, I need a list of available #, not just next
ONE ip. For example, my assigned IP list is:

tblIP IP
198.188.10.122
198.188.10.154
198.188.10.203
198.188.10.25
198.188.10.31
198.188.10.33
198.188.10.34
198.188.10.35
198.188.10.36
198.188.10.52
198.188.10.77
198.188.10.92
198.188.10.97


and I want list that 4th octet is not in this list. I need 26, 27, 28 etc
which are not in this list. My 4th octet can rang from to 255 and your code
only gave me 98.

thanks.
KARL DEWEY said:
Substitute you field name for IPAddress and try this --
SELECT Left(Q.[IPAddress],11) &
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)+1
AS [Next IP]
FROM tblIP AS Q
WHERE ((((SELECT COUNT(*) FROM tblIP Q1
WHERE Left(Q.[IPAddress],11) = Left(Q1.[IPAddress],11) AND
Right(Q1.[IPAddress],Len(Q1.[IPAddress])-11) >=
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)))=1))
ORDER BY Left(Q.[IPAddress],11) &
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)+1,
Right(Q.[IPAddress],Len(Q.[IPAddress])-11) DESC;

--
KARL DEWEY
Build a little - Test a little


Song Su said:
I have tblIP which is already assigned like below:

198.188.10.1
198.188.10.2
198.188.10.4

first 8 digit (198.188.10) never change. I want some procedure to loop my
table to find available (un-assigned) ip, in this case
198.188.10.3
198.188.10.5
198.188.10.11
etc.

The last octet can range from 1 to 200. Can anyone help? Thanks.
Click to expand...
Click to expand...
 
Hi Song Su,

In addition to Karl's sage advice, I might just
create another table (say "tblOctet") with one
field (say "Octet" type Text or Long) with values
from "1" to "255" (or 1 to 255).

There are many tangents from here depending on
if you want "one-time" solution to your specific problem
or a more general solution, i.e., for specific solution,
one might create a query...

qryCurrentOctets
(if tblOctet.Octet is type Text)

SELECT
Mid(tblIP.IP, 12) As strCurOctet
FROM tblIP;

or (if tblOctet.Octet is type Long)

SELECT
CLng(Nz(Mid(tblIP.IP, 12),0)) As lngCurOctet
FROM tblIP;

then left join tblOctet to qryCurrentOctets
(if tblOctet.Octet is type Text)

SELECT
T.strCurOctet As MissingOctet,
"198.188.10." & T.strCurOctet As UnAssignedIP
FROM
tblOctet AS T
LEFT JOIN
qryCurrentOctets AS Q
ON
T.Octet = Q.strCurOctet
WHERE
Q.strCurOctet IS NULL;

if you go the "type Long" route,
it will be "easier" to pull "Min" MissingOctet
since Text does not necessarily sort properly.

Like I said many tangents, but hopefully
this will get you started.

good luck,

gary



Song Su said:
Thanks for quick reply. However, I need a list of available #, not just next
ONE ip. For example, my assigned IP list is:

tblIP IP
198.188.10.122
198.188.10.154
198.188.10.203
198.188.10.25
198.188.10.31
198.188.10.33
198.188.10.34
198.188.10.35
198.188.10.36
198.188.10.52
198.188.10.77
198.188.10.92
198.188.10.97


and I want list that 4th octet is not in this list. I need 26, 27, 28 etc
which are not in this list. My 4th octet can rang from to 255 and your code
only gave me 98.

thanks.
KARL DEWEY said:
Substitute you field name for IPAddress and try this --
SELECT Left(Q.[IPAddress],11) &
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)+1
AS [Next IP]
FROM tblIP AS Q
WHERE ((((SELECT COUNT(*) FROM tblIP Q1
WHERE Left(Q.[IPAddress],11) = Left(Q1.[IPAddress],11) AND
Right(Q1.[IPAddress],Len(Q1.[IPAddress])-11) >=
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)))=1))
ORDER BY Left(Q.[IPAddress],11) &
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)+1,
Right(Q.[IPAddress],Len(Q.[IPAddress])-11) DESC;

--
KARL DEWEY
Build a little - Test a little


Song Su said:
I have tblIP which is already assigned like below:

198.188.10.1
198.188.10.2
198.188.10.4

first 8 digit (198.188.10) never change. I want some procedure to loop my
table to find available (un-assigned) ip, in this case
198.188.10.3
198.188.10.5
198.188.10.11
etc.

The last octet can range from 1 to 200. Can anyone help? Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Sorry...meant last query to be...


SELECT
T.Octet As MissingOctet,
"198.188.10." & T.Octet As UnAssignedIP
FROM
tblOctet AS T
LEFT JOIN
qryCurrentOctets AS Q
ON
T.Octet = Q.strCurOctet
WHERE
Q.strCurOctet IS NULL;

Gary Walter said:
Hi Song Su,

In addition to Karl's sage advice, I might just
create another table (say "tblOctet") with one
field (say "Octet" type Text or Long) with values
from "1" to "255" (or 1 to 255).

There are many tangents from here depending on
if you want "one-time" solution to your specific problem
or a more general solution, i.e., for specific solution,
one might create a query...

qryCurrentOctets
(if tblOctet.Octet is type Text)

SELECT
Mid(tblIP.IP, 12) As strCurOctet
FROM tblIP;

or (if tblOctet.Octet is type Long)

SELECT
CLng(Nz(Mid(tblIP.IP, 12),0)) As lngCurOctet
FROM tblIP;

then left join tblOctet to qryCurrentOctets
(if tblOctet.Octet is type Text)

SELECT
T.strCurOctet As MissingOctet,
"198.188.10." & T.strCurOctet As UnAssignedIP
FROM
tblOctet AS T
LEFT JOIN
qryCurrentOctets AS Q
ON
T.Octet = Q.strCurOctet
WHERE
Q.strCurOctet IS NULL;

if you go the "type Long" route,
it will be "easier" to pull "Min" MissingOctet
since Text does not necessarily sort properly.

Like I said many tangents, but hopefully
this will get you started.

good luck,

gary



Song Su said:
Thanks for quick reply. However, I need a list of available #, not just next
ONE ip. For example, my assigned IP list is:

tblIP IP
198.188.10.122
198.188.10.154
198.188.10.203
198.188.10.25
198.188.10.31
198.188.10.33
198.188.10.34
198.188.10.35
198.188.10.36
198.188.10.52
198.188.10.77
198.188.10.92
198.188.10.97


and I want list that 4th octet is not in this list. I need 26, 27, 28 etc
which are not in this list. My 4th octet can rang from to 255 and your code
only gave me 98.

thanks.
KARL DEWEY said:
Substitute you field name for IPAddress and try this --
SELECT Left(Q.[IPAddress],11) &
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)+1
AS [Next IP]
FROM tblIP AS Q
WHERE ((((SELECT COUNT(*) FROM tblIP Q1
WHERE Left(Q.[IPAddress],11) = Left(Q1.[IPAddress],11) AND
Right(Q1.[IPAddress],Len(Q1.[IPAddress])-11) >=
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)))=1))
ORDER BY Left(Q.[IPAddress],11) &
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)+1,
Right(Q.[IPAddress],Len(Q.[IPAddress])-11) DESC;

--
KARL DEWEY
Build a little - Test a little


:

I have tblIP which is already assigned like below:

198.188.10.1
198.188.10.2
198.188.10.4

first 8 digit (198.188.10) never change. I want some procedure to
Click to expand...
Click to expand...
loop
my
Click to expand...
Click to expand...
 
Create a table named CountNumber with field named CountNUM containing numbers
from 0 (zero) through 200.
Use these two queries --
All_Avail_IP --
SELECT Left([IPAddress],11) & [CountNUM] AS All_IP
FROM tblIP, CountNumber
WHERE (((Left([IPAddress],11) & [CountNUM])<>[IPAddress]) AND
((CountNumber.CountNUM)<>Right([IPAddress],Len([IPAddress])-11) And
(CountNumber.CountNUM) Between 1 And 200))
GROUP BY Left([IPAddress],11) & [CountNUM], Left([IPAddress],11),
CountNumber.CountNUM
ORDER BY Left([IPAddress],11), CountNumber.CountNUM;


SELECT All_Avail_IP.All_IP
FROM All_Avail_IP LEFT JOIN tblIP ON All_Avail_IP.All_IP = tblIP.IPAddress
WHERE (((tblIP.IPAddress) Is Null));

--
KARL DEWEY
Build a little - Test a little


Song Su said:
Thanks for quick reply. However, I need a list of available #, not just next
ONE ip. For example, my assigned IP list is:

tblIP IP
198.188.10.122
198.188.10.154
198.188.10.203
198.188.10.25
198.188.10.31
198.188.10.33
198.188.10.34
198.188.10.35
198.188.10.36
198.188.10.52
198.188.10.77
198.188.10.92
198.188.10.97


and I want list that 4th octet is not in this list. I need 26, 27, 28 etc
which are not in this list. My 4th octet can rang from to 255 and your code
only gave me 98.

thanks.
KARL DEWEY said:
Substitute you field name for IPAddress and try this --
SELECT Left(Q.[IPAddress],11) &
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)+1
AS [Next IP]
FROM tblIP AS Q
WHERE ((((SELECT COUNT(*) FROM tblIP Q1
WHERE Left(Q.[IPAddress],11) = Left(Q1.[IPAddress],11) AND
Right(Q1.[IPAddress],Len(Q1.[IPAddress])-11) >=
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)))=1))
ORDER BY Left(Q.[IPAddress],11) &
Right(Q.[IPAddress],Len(Q.[IPAddress])-11)+1,
Right(Q.[IPAddress],Len(Q.[IPAddress])-11) DESC;

--
KARL DEWEY
Build a little - Test a little


Song Su said:
I have tblIP which is already assigned like below:

198.188.10.1
198.188.10.2
198.188.10.4

first 8 digit (198.188.10) never change. I want some procedure to loop my
table to find available (un-assigned) ip, in this case
198.188.10.3
198.188.10.5
198.188.10.11
etc.

The last octet can range from 1 to 200. Can anyone help? Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top