Counts of Clients by Distribution Point

 

-- Counts of Clients by Distribution Point

-- Create temp table
CREATE TABLE #ResourceInfo
(
[ResourceID] int
,[NumericIPAddress] bigint
,[NumericIPSubnet] bigint
);

INSERT INTO
#ResourceInfo
(
[ResourceID]
,[NumericIPAddress]
,[NumericIPSubnet]
)

select
nac.ResourceID
, CM_Supplemental.dbo.ipstringtonumber (CM_Supplemental.dbo.TruncateCommaDeliminatedIPData (nac.IPAddress0)) as NumericIPAddress
, CM_Supplemental.dbo.ipstringtonumber (CM_Supplemental.dbo.TruncateCommaDeliminatedIPData (nac.IPAddress0)) & CM_Supplemental.dbo.ipstringtonumber (CM_Supplemental.dbo.TruncateCommaDeliminatedIPData (nac.IPSubnet0)) as NumericIPSubnet
from v_GS_NETWORK_ADAPTER_CONFIGUR nac
where IPAddress0 like '1%' and DefaultIPGateway0 is not null

select ServerName, count (*) as Clients, sum (TSRun) as CountofTaskSequences
from
(
select distinct ri.*, dp.ServerName, case when ts.ResourceID is null then 0 else 1 end as TSRun from #ResourceInfo ri
inner join
(
select
b.BoundaryID
, CM_Supplemental.dbo.ipstringtonumber (b.Value) as NumericIPSubnetBoundary
, BoundaryType
, NumericValueLow
, NumericValueHigh
from BoundaryEx b where BoundaryType = 0
union all
select
b.BoundaryID
, -1
, BoundaryType
, NumericValueLow
, NumericValueHigh
from BoundaryEx b where BoundaryType = 3
) as b on (b.NumericIPSubnetBoundary = ri.NumericIPSubnet or ri.NumericIPAddress between b.NumericValueLow and b.NumericValueHigh)
inner join vSMS_BoundaryGroupMembers bgm on bgm.BoundaryID = b.BoundaryID
inner join vSMS_BoundaryGroupSiteSystems bgss on bgss.GroupID = bgm.GroupID and bgss.Flags = 0
inner join vDistributionPoints dp on dp.NALPath = bgss.ServerNALPath
left join
(
select ts.ResourceID from v_TaskExecutionStatus ts
inner join v_Advertisement a on a.AdvertisementID = ts.AdvertisementID and ts.LastStatusMessageID = 11143 and ts.Step = 0
inner join v_TaskSequencePackage tsp on a.PackageID = tsp.PackageID
inner join v_TaskSequenceReferencesInfo tsri on tsp.packageid = tsri.PackageID and tsri.ReferencePackageType = 257
) ts on ts.ResourceID = ri.ResourceID
) as data
group by ServerName
order by ServerName
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