Finding if there are subnets and/or subnet ranges to cover all your devices

 

with nac (DefaultIPGateway0, IPSubnet0)
as (
select distinct
  [CM_Supplemental].[dbo].[TruncateCommaDeliminatedIPData] (nac.DefaultIPGateway0)
, [CM_Supplemental].[dbo].[TruncateCommaDeliminatedIPData] (nac.IPSubnet0) from v_GS_NETWORK_ADAPTER_CONFIGUR nac where DefaultIPGateway0 is not null
)
select distinct
nac.DefaultIPGateway0, b1.[Value] Subnet, b2.[Value] [Subnet Range]
from nac
left join vSMS_Boundary b1 on
CM_Supplemental.dbo.ipstringtonumber (b1.Value) = CM_Supplemental.dbo.ipstringtonumber (nac.DefaultIPGateway0) & CM_Supplemental.dbo.ipstringtonumber (nac.IPSubnet0) and b1.BoundaryType = 0
left join BoundaryEx b2 on
CM_Supplemental.dbo.ipstringtonumber (nac.DefaultIPGateway0) between b2.NumericValueLow and b2.NumericValueHigh and b2.BoundaryType = 3
order by 1

Results look like:

results

 

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