Calculate IP Range From Subnet and Mask

[mrgulic]

I have a table in MS Access that contains a table with 8000+ Subnets and masks.

I also have another table of devices and their IP's

I need to find a way determine the subnets that those IP's belong to.

I need code that returns the IP range from the subnet and mask.

I have looked around the internet and am having problem finding code for this. All I can seem to find is exe's that do single ips at a time.

I am not a network person, but i do somewhat understand what needs to happen, i just dont know how to do it.

Thanks in advance for help anyone can offer.

 

[bboffin]

I don't know how the addresses are held on your database but the first thing is all IP addresses and the masks are actually unsigned 32 bit integers and can be treated as such to help you work out the answer. Thus 255.255.0.0 represents the value &HFFFF0000.

The subnet mask itself when logically ANDed with an IP Address will return a value that represents the minimum IP address in the subnet.

Thus assuming we have the mask in a variable called lMask declared as a Long and the IP Address in a variable called lIP the minimum IP address is:

lMinIP = lIP AND lMask

The maximum IP address can be determined by setting all the zero bits at the right hand end of the minimum IP address to 1.


lMaxIP = lMinIP + (lMask xor &HFFFFFFFF)

The xor obtains all the 1 bits required and we just add it to the Minimum IP Address.

The only problem you have is that VB doesn't support unsigned 32 bit integers so values above 127.255.255.255 will be negative and you will need to resolve this when converting back to the normal notation for IP addresses.

All the logic above will work fine but one trick you can use when converting back is to use the Hex function to convert the result to a string and then convert the hex byte pairs back to the four decimal numbers.

Hope this helps

 

[mrgulic]

the IP's, subnets and masks are stored as xxx.xxx.xxx.xxx

i have code that converts the IP address to an integer but i didnt write it.

i can do basic vb coding within Access, but thats about it.

i found some code written in python that will do the calculation but i dont know how to convert it to vb.
can you do anythng with it. i need it to work in access.
thanks


#! /usr/local/bin/python
#
# this prog was loosely ported from a similar javascript
# implementation written by me earlier. In javascript
# variables are 32 bits and signed, so I opted to use arrays
# of 4 integers to represent each IP address.
#
# Python allows unlimited precision, so a different approach
# might have been indicated. -- Next time...
#
# (c)2000 Manfred Bartz <md-NetCalc@logi.cc>
# You may use this source code as per the
# &quot;Gnu General Public License (GPL)&quot; <

http://www.gnu.org/>

# V1.1 Added ability to calculate the best fit subnet based on
# an IP address range.
#
# V1.1a Made HTML tag generation a command line option (--html)
# rather than assuming it from sys.stdout.isatty()
#

import getopt, sys, string, re

# netaddr/netmask format
PattAddrMask = re.compile( '^(\d+)\.(\d+)\.(\d+)\.(\d+)\s{0,2}/\s{0,2}(\d+)\.(\d+)\.(\d+)\.(\d+).*$')

PattAddrRange = re.compile( '^(\d+)\.(\d+)\.(\d+)\.(\d+)\s{0,2}[-:]\s{0,2}(\d+)\.(\d+)\.(\d+)\.(\d+).*$')

PattPartialNetmask = re.compile( '^\d+\.\d+\.\d+\.\d+/(\d+\.[\.0-9]+)[^\.\d]*$')

# netaddr/masklen format
PattAddrMLen = re.compile( '^(\d+)\.(\d+)\.(\d+)\.(\d+)/(\d+).*$')

# netaddr/masklen format
PattAddrOnly = re.compile( '^(\d+)\.(\d+)\.(\d+)\.(\d+)\D*$')

NetAddr = [0,0,0,0]
HostAddr = [0,0,0,0]
HostAddr2 = [0,0,0,0]
NetMask = [0,0,0,0]
Broadcast = [0,0,0,0]
FirstHost = [0,0,0,0]
LastHost = [0,0,0,0]


#----------------------------------------------------------------------
def RangeOk(n, min, max):

if (n >= min) and (n <= max):
return 1
return 0

#----------------------------------------------------------------------
def allBits0:
r = 0
for i in range(len):
r = r | n
return r == 0

#----------------------------------------------------------------------
def allBits1:
r = 0xFF
for i in range(len):
r = r & n
return (r & 0xFF) == 0xFF

#----------------------------------------------------------------------
def quitOutOfRange(s):
print '%s%s bad: each number in the dotted quad must be in the range 0 to 255!%s' % (TagRed, s, TagNormal)
sys.exit(1)

#----------------------------------------------------------------------
def tellBadMask(s):
print '%sNetMask bad: %s%s' % (TagRed, s, TagNormal)
print

#----------------------------------------------------------------------
def ParseInput(inpLine):
global NetMaskBad
global MaskLen
global isAddrRange
NetMaskBad = 0
MaskLen = 0
NxorAddr = [0,0,0,0]

MatchResult = PattAddrRange.match(inpLine)
if MatchResult:
# print 'netaddr1/netaddr2 format'
isAddrRange = 1
for i in range(4):
HostAddr = string.atoi(MatchResult.group(i+1))
if not RangeOk(HostAddr, 0, 255):
quitOutOfRange('HostAddr1')
for i in range(4):
HostAddr2 = string.atoi(MatchResult.group(i+5))
if not RangeOk(HostAddr2, 0, 255):
quitOutOfRange('HostAddr2')
NxorAddr = ~ (HostAddr ^ HostAddr2)
i = 0
done = 0
while i < 4:
bitmask = 0x80
for b in range(8):
if not done:
if NxorAddr & bitmask:
MaskLen = MaskLen + 1
else:
done = 1
bitmask = bitmask >> 1
i = i + 1

else:
MatchResult = PattAddrMask.match(inpLine)
if MatchResult:
# print 'netaddr/netmask format'
for i in range(4):
HostAddr = string.atoi(MatchResult.group(i+1))
if not RangeOk(HostAddr, 0, 255):
quitOutOfRange('HostAddr')
for i in range(4):
NetMask = string.atoi(MatchResult.group(i+5))
if not RangeOk(NetMask, 0, 255):
quitOutOfRange('NetMask')
i = 0
done = 0
while i < 4:
bitmask = 0x80
for b in range(8):
if not done:
if NetMask & bitmask:
MaskLen = MaskLen + 1
else:
done = 1
else:
if NetMask & bitmask:
NetMaskBad = 1
bitmask = bitmask >> 1
i = i + 1

else:
MatchResult = PattPartialNetmask.match(inpLine)
if MatchResult:
tellBadMask('&quot;%s&quot; is partial mask, use full mask or specify mask length' % MatchResult.group(1));
sys.exit(1)
else:
MatchResult = PattAddrMLen.match(inpLine)
if MatchResult:
# print 'netaddr/MaskLen format'
for i in range(4):
HostAddr = string.atoi(MatchResult.group(i+1))
if not RangeOk(HostAddr, 0, 255):
quitOutOfRange('HostAddr')
MaskLen = string.atoi(MatchResult.group(5))
if not RangeOk(MaskLen, 0, 32):
tellBadMask('must be in the range of 0 to 32 bits, set to 32');
MaskLen = 32
else:
MatchResult = PattAddrOnly.match(inpLine)
if MatchResult:
# print 'netaddr only format'
for i in range(4):
HostAddr = string.atoi(MatchResult.group(i+1))
if not RangeOk(HostAddr, 0, 255):
quitOutOfRange('HostAddr')
MaskLen = 8 # default is old class A
if (HostAddr[0] & 0xC0) == 0x80: # old class B
MaskLen = 16
print 'Assuming class B netmask\n'
elif (HostAddr[0] & 0xE0) == 0xC0: # old class C
MaskLen = 24
print 'Assuming class C netmask\n'
elif (HostAddr[0] & 0xF0) == 0xE0: # class D (multicast)
MaskLen = 4
# print 'Assuming class D netmask\n'
elif (HostAddr[0] & 0xF0) == 0xF0: # class E (experimental)
MaskLen = 4
# print 'Assuming class E netmask\n'
else:
if not (allBits0(HostAddr) or allBits1(HostAddr)):
print 'Assuming class A netmask\n'
else:
# print 'sys.exc_type=', sys.exc_type
print inpLine
print 'beats me...'
sys.exit(1)

i = 0
n = MaskLen
while n > 0:
bitmask = 0x80
for b in range(8):
if n > 0:
NetMask = NetMask | bitmask
n = n - 1
bitmask = bitmask >> 1
i = i + 1

#----------------------------------------------------------------------
def CalcHosts():
global NumHosts
NumHosts = 0L

for i in range(4):
NetAddr = HostAddr & NetMask
Broadcast = HostAddr | (~NetMask & 0xFF)

if (RangeOk(MaskLen, 0, 30)):
for i in range(4):
FirstHost = NetAddr
LastHost = Broadcast
FirstHost[3] = FirstHost[3] + 1
LastHost[3] = LastHost[3] - 1
NumHosts = (0x01L << (32 - MaskLen)) - 2L

#----------------------------------------------------------------------
def FormatDecBinHex:
global dottedQuad
global binString
global hexString

dottedQuad = '%d.%d.%d.%d' % (n[0], n[1], n[2], n[3])
hexString = '0x%02X%02X%02X%02X' % (n[0], n[1], n[2], n[3])

binString = ''
i = 0
m = 0
bitmask = 0x80
# set color for mask bits
binString = binString + TagDarkBlue
while i < 4:
while bitmask:
m = m + 1
if m > MaskLen:
break
if n & bitmask:
binString = binString + '1'
else:
binString = binString + '0'
bitmask = bitmask >> 1
if m > MaskLen:
break
if not bitmask:
bitmask = 0x80
i = i + 1
if (i < 4):
binString = binString + '.'

# set color for host bits
binString = binString + TagNormal + TagDarkRed

while i < 4:
while bitmask:
if n & bitmask:
binString = binString + '1'
else:
binString = binString + '0'
bitmask = bitmask >> 1
if not bitmask:
bitmask = 0x80
i = i + 1
if (i < 4):
binString = binString + '.'

# restore default color
binString = binString + TagNormal

#----------------------------------------------------------------------
def PrintInfo():
global NumHosts

if NetMaskBad:
tellBadMask('must be consecutive ones starting at MSB')

FormatDecBinHex(HostAddr)
if not isAddrRange:
print '%sHost%sAddr %-18.18s %s %s' % (TagDarkRed+TagBold, TagUnBold+TagNormal, dottedQuad, binString, hexString)
else:
print '%sAddress1%s %-18.18s %s %s' % (TagBold, TagUnBold, dottedQuad, binString, hexString)
if allBits1(HostAddr):
print '%sThis is the global broadcast address!%s' % (TagRed, TagNormal)
return
if isAddrRange:
FormatDecBinHex(HostAddr2)
print '%sAddress2%s %-18.18s %s %s' % (TagBold, TagUnBold, dottedQuad, binString, hexString)
FormatDecBinHex(NetMask)
print '%sNet%sMask %-18.18s %s %s' % (TagDarkBlue+TagBold, TagUnBold+TagNormal, dottedQuad, binString, hexString)
if allBits0(HostAddr) and allBits0(NetMask):
print '%sThis is the entire Internet!%s' % (TagRed, TagNormal)
print
FormatDecBinHex(NetAddr)
AddrAndMask = '%s/%s' % (dottedQuad, MaskLen)
print 'NetAddr %-18.18s %s %s' % (AddrAndMask, binString, hexString)

if not NetMaskBad:
if (RangeOk(MaskLen, 0, 30)):
FormatDecBinHex(FirstHost)
print 'First Host %-18.18s %s %s' % (dottedQuad, binString, hexString)
FormatDecBinHex(LastHost)
print 'Last Host %-18.18s %s %s' % (dottedQuad, binString, hexString)
if (RangeOk(MaskLen, 0, 31)):
FormatDecBinHex(Broadcast)
print 'Broadcast %-18.18s %s %s' % (dottedQuad, binString, hexString)
if (RangeOk(MaskLen, 0, 30)):
print 'N Hosts %s' % string.replace(str(NumHosts),&quot;L&quot;,&quot;&quot;,1)
print

HostPtr = '%d.%d.%d.%d.in-addr.arpa.' % (HostAddr[3], HostAddr[2], HostAddr[1], HostAddr[0])
print '%-30.30s IN PTR' % HostPtr

if not NetMaskBad:
NetPtr = '%d.%d.%d.%d.in-addr.arpa.' % (NetAddr[3], NetAddr[2], NetAddr[1], NetAddr[0])
print '%-30.30s IN PTR' % NetPtr
print

if (NetAddr[0] & 0xF0) == 0xE0:
print 'Note: Network address is class D (multicast)'
elif (NetAddr[0] & 0xF0) == 0xF0:
print 'Note: Network address is class E (experimental)'

## if (NetAddr[0] & 0xF0) >= 0xE0 and MaskLen != 4:
## print ' %sThe mask length should possibly be 4%s' ## % (TagRed, TagNormal)

#----------------------------------------------------------------------
# main prog

print 'NetCalc V1.1a'

OptionList, ArgList = getopt.getopt(sys.argv[1:], '', ['html', 'debug'])
# any args?
if len(ArgList) < 1:
print 'Need an argument...'
sys.exit(1)
# any options?
isHTML = 0
isDebug = 0
for Option, Value in OptionList:
if Option == '--html':
isHTML = 1
if Option == '--debug':
isDebug = 1

# init
if isHTML:
TagBlack = &quot;<FONT COLOR=\&quot;#000000\&quot;>&quot;
TagDarkBlue= &quot;<FONT COLOR=\&quot;#001860\&quot;>&quot;
TagDarkRed = &quot;<FONT COLOR=\&quot;#600000\&quot;>&quot;
TagRed = &quot;<FONT COLOR=\&quot;#A00000\&quot;>&quot;
TagNormal = &quot;</FONT>&quot;
TagBold = &quot;<B>&quot;
TagUnBold = &quot;</B>&quot;
else:
TagBlack = &quot;&quot;
TagDarkBlue= &quot;&quot;
TagDarkRed = &quot;&quot;
TagRed = &quot;&quot;
TagNormal = &quot;&quot;
TagBold = &quot;&quot;
TagUnBold = &quot;&quot;

isAddrRange = 0

# action
ParseInput(ArgList[0])
CalcHosts()
PrintInfo()

sys.exit(0)

 

[bboffin]

To convert from the standard IP address format to a long integer write a function:

Public Function IPtoLong(sIP as string, lIP as Long) as boolean
Dim vIP as Variant
Dim i as Integer
Dim wIP as Long
IPToLong = False  '  set default return value
' split the IP address
vIP = Split(sIP, ".")
' error if there are not exactly 4 elements
if UBound(vIP) <> 3 then Exit Function
' initialise work variable
wIP = 0
for i = 0 to 3
    ' make sure value is numeric
    if IsNumeric(vIP(i)) then
        ' add to work variable but never more than 255
        wIP = (wIp * 256) + (CInt(vIP(i)) mod 256)
    else
        Exit Function
    end if
next
lIP = wIP
IPToLong = True

End Function

Call this function as follows:

Dim MyIP as Long
If IPToLong(sIPAddress, MyIP) then
    ' the address is valid so you can now use MyIP 
    ' as the mask or IP address with the code I gave you
    ' earlier
.
.
.
End If

To convert back to IP format:

Public Function LongToIP(lIP) as String
Dim wIP As String
Dim hIP as String
Dim i As Integer
wIP= ""
' convert the IP Address to hex and add leading zeros as required
hIP = Right("00000000" & Hex(lIP), 8)
' process each pair of hex characters
for i = 1 to 8 step 2
    wIP = wIP & CInt("&H" & mid(hIP, i, 2)) & "."
Next
' return result removing last unwanted period
LongToIP = left(wIP, Len(wIP) - 1)
End Function

All the above should be valid VBA give or take the odd typo.
You'll have to figure out how you're going to apply this to the table but I guess you'll have to open it as a recordset and process each item in turn.

You'll need

 

[mrgulic]

I don't see where this calculates the IP range.

I know enough to loop through a table and update it accordingly, but i dont know how to do the calculation for IP range from subnet and mask.

 

[bboffin]

I presume by IP range that you mean the minimum and maximum addresses implied by a given IP address and its associated submask?

In that case in the code I first supplied lMinIP is the lowest address in the range for a given IP address and subnet and lMaxIP is the highest address in that range.

The second set of code is a couple of functions to help you implement the first set of code.

 

[mrgulic]

&quot;I presume by IP range that you mean the minimum and maximum addresses implied by a given IP address and its associated submask?&quot;


Not an IP address (as in host), but a SUBNET and the associated MASK. From this should come the minimum and maximum allowable IP range.

I hope that we are on the same page. I would hate to think that you wasted your time due to a small mis-understanding.

If what you wrote (code) is all I need (except for the looping through the tables recordset), then I thank you very much for your time and effort, and I will let you know how it goes.

 

[bboffin]

I think we are so try out the code and see if it gives you the right results

 

[mrgulic]

Color me stupid, but I need I little bit more help in setting this up.