Finding the boundaries and distribution points for a list of computers

with Boundaries (Name0, Value, BoundaryType, Name, ServerName) as
(
select
 s.Name0
, b.Value
, 'IP Subnet' as BoundaryType
, bg.Name
, dp.ServerName
from v_R_System s
inner join v_GS_NETWORK_ADAPTER_CONFIGUR nac on nac.ResourceID = s.ResourceID
inner join vSMS_Boundary b on
 CM_Supplemental.dbo.ipstringtonumber (b.Value) = CM_Supplemental.dbo.ipstringtonumber (left (nac.IPAddress0, case when charindex (',', nac.ipaddress0, 1) > 0 then charindex (',', nac.ipaddress0, 1) - 1 else len (nac.IPAddress0) 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)) and BoundaryType = 0
inner join vSMS_BoundaryGroupMembers bgm on bgm.BoundaryID = b.BoundaryID
inner join vSMS_BoundaryGroup bg on bg.GroupID = bgm.GroupID
inner join vSMS_BoundaryGroupSiteSystems bgss on bgss.GroupID = bgm.GroupID
inner join vDistributionPoints dp on dp.NALPath = bgss.ServerNALPath
where nac.IPAddress0 is not null
union all
select
 s.Name0
, b.Value
, 'Active Directory Site' as BoundaryType
, bg.Name
, dp.ServerName
from v_R_System s
inner join vSMS_Boundary b on s.AD_Site_Name0 = b.Value
inner join vSMS_BoundaryGroupMembers bgm on bgm.BoundaryID = b.BoundaryID
inner join vSMS_BoundaryGroup bg on bg.GroupID = bgm.GroupID
inner join vSMS_BoundaryGroupSiteSystems bgss on bgss.GroupID = bgm.GroupID
inner join vDistributionPoints dp on dp.NALPath = bgss.ServerNALPath
union all
select
 s.Name0
, b.Value
, 'IP Address Range' as BoundaryType
, bg.Name
, dp.ServerName
from v_R_System s
inner join v_GS_NETWORK_ADAPTER_CONFIGUR nac on nac.ResourceID = s.ResourceID
inner join BoundaryEx b on
(CM_Supplemental.dbo.ipstringtonumber (left (nac.IPAddress0, case when charindex (',', nac.ipaddress0, 1) > 0 then charindex (',', nac.ipaddress0, 1) - 1 else len (nac.IPAddress0) end)) between b.NumericValueLow and b.NumericValueHigh)
and BoundaryType = 3
inner join vSMS_BoundaryGroupMembers bgm on bgm.BoundaryID = b.BoundaryID
inner join vSMS_BoundaryGroup bg on bg.GroupID = bgm.GroupID
inner join vSMS_BoundaryGroupSiteSystems bgss on bgss.GroupID = bgm.GroupID
inner join vDistributionPoints dp on dp.NALPath = bgss.ServerNALPath
where nac.IPAddress0 is not null
)
select * from Boundaries where Name0 in
(
'W2UA1234567',
'W2UA2345678',
'W123456',
'W2345678',
'W1234567',
'W98765431'
)
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