Counts of Clients by Distribution Point

The original version of this (see below) also counted the number of task sequences that ran… here’s one that just counts clients.

 

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

from

(

select distinct ri.*, dp.ServerName 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

) as data

group by ServerName

order by ServerName

 

-- 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