Finding computers that don’t fall into a boundary (AD Site, IP Subnet, Subnet Range)

/* This will find machines that don't match a boundary.

This uses a function I found on the internet years ago, that converts the IP Address to a numerical string.

Have to parse out the comma and the IPv6 addresses; our IP subnets all begin 10.* or 172.*, so modify as needed.

*/

with BoundaryInfo (AD_Site_Name0, DefaultIPGateway0, IPSubnetValue)

 as

(

select

 s.AD_Site_Name0

, left (nac.DefaultIPGateway0, case when charindex (',', nac.DefaultIPGateway0, 1) > 0 then charindex (',', nac.DefaultIPGateway0, 1) - 1 else len (nac.DefaultIPGateway0) end)

, CM_Supplemental.dbo.ipstringtonumber (left (nac.DefaultIPGateway0, case when charindex (',', nac.DefaultIPGateway0, 1) > 0 then charindex (',', nac.DefaultIPGateway0, 1) - 1 else len (nac.DefaultIPGateway0) end))

& CM_Supplemental.dbo.ipstringtonumber (left (nac.IPSubnet0, case when charindex (',', nac.IPSubnet0, 1) > 0 then charindex (',', nac.IPSubnet0, 1) - 1 else len (nac.IPSubnet0) end)) as IPSubnet

from v_R_System s

inner join v_GS_NETWORK_ADAPTER_CONFIGUR nac on nac.ResourceID = s.ResourceID

where nac.DefaultIPGateway0 like '1[0,7]%'

 )

 select

 bi.AD_Site_Name0

, bi.DefaultIPGateway0

, bi.IPSubnetValue

, count (*) as [Count]

from BoundaryInfo bi

left join BoundaryEx b0 on bi.IPSubnetValue = CM_Supplemental.dbo.ipstringtonumber (b0.Value) and b0.BoundaryType = 0

left join BoundaryEx b3 on (bi.IPSubnetValue between b3.NumericValueLow and b3.NumericValueHigh) and b3.BoundaryType = 3

left join BoundaryEx b1 on bi.AD_Site_Name0 = b1.Value and b1.BoundaryType = 1

where b0.Value is null

and b3.NumericValueLow is null

and b1.Value is null

group by

 bi.AD_Site_Name0

, bi.DefaultIPGateway0

, bi.IPSubnetValue
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s