OVERVIEW
I was recently pointed to Jeff Gilbert's blog where he has an article on determining a client's subnet mask and using them in subnet boundaries.
http://myitforum.com/cs2/blogs/jgilbert/archive/2007/08/21/how-to-determine-a-client-s-subnet-mask-when-defining-ip-subnet-boundaries.aspx
Now, in that article is a VBScript script to calculate the subnet. It got me thinking...I usually just lay down some M@d SQL to do the same thing. Perhaps someone would want to see that. So here it is:
DETAILS
Let's say you have an IP address on a client 10.109.238.142, with subnet mask of 255.255.255.192. What subnet is that address on?
Well, in order to calculate that, the "book" says you take that first octet of the address and convert it to binary, and convert the first octet of the mask and convert it to binary, and then "AND" the bits together...well I'm going to spare you the bitwise lesson and just show you how you can do that using SQL.
The ampersand (&) character is the BITWISE AND operator in SQL. When you have a number BITWISE ANDed with another number, it will use the bits of the first number and the bits of the second number and perform the operation on the bits themselves. So using the first octet of our IP address and subnet, we would do 10 & 255.
SNIPPET #1
SELECT
10 & 255
OUTPUT #1
10
OK, pretty easy right? So, the first octet of the subnet is 10. Now lets do that with the whole address (each octet is output to another column):
SNIPPET #2
SELECT
10 & 255,
109 & 255,
238 & 255,
142 & 192
OUTPUT #2
10 109 238 128
So, the subnet for 10.109.238.142 is 10.109.238.128.
M@d Skillz
OK, now let's get m@d and use some cr@zy SQL to determine computers' subnets based on the information in V_GS_Network_Adapter_Configur.
SNIPPET #3
SELECT
sys.netbios_name0 AS ComputerName,
cfg.IPAddress0 AS [IP Address],
cfg.IPSubnet0 AS [Subnet Mask],
CAST(CAST(PARSENAME(cfg.IPAddress0,4) AS INT) & CAST(PARSENAME(cfg.IPsubnet0,4) AS INT) AS VARCHAR(3)) + '.' +
CAST(CAST(PARSENAME(cfg.IPAddress0,3) AS INT) & CAST(PARSENAME(cfg.IPsubnet0,3) AS INT) AS VARCHAR(3)) + '.' +
CAST(CAST(PARSENAME(cfg.IPAddress0,2) AS INT) & CAST(PARSENAME(cfg.IPsubnet0,2) AS INT) AS VARCHAR(3)) + '.' +
CAST(CAST(PARSENAME(cfg.IPAddress0,1) AS INT) & CAST(PARSENAME(cfg.IPsubnet0,1) AS INT) AS VARCHAR(3)) AS Subnet
FROM
dbo.v_R_System AS sys
INNER JOIN dbo.v_GS_Network_Adapter_Configur AS cfg
ON sys.resourceID = cfg.resourceID
AND cfg.IPEnabled0 = 1
WHERE
sys.obsolete0 = 0
AND sys.decommissioned0 = 0
AND sys.client0 = 1
AND cfg.IPaddress0 NOT LIKE '%,%'
Have fun with that!
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
