case when (@TotalClient = 0) or (@TotalClient is null) Then ‘100’ Else (round(@ClientInstalled/ convert (float,@TotalClient)*100,2)) End as ‘ClientInstalled%’

All Active and Inactive Workstations Client Status

Declare @CollectionID as Varchar(8)

Declare @TotalClientInstalled as Numeric(8)

Declare @ClientActive as Numeric(8)

Declare @ClientInActive as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalClientInstalled = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1 or Ch.ClientActiveStatus = 0) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @ClientActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @ClientInActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 0) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select

@TotalClientInstalled as ‘TotalClientInstalled’,

@ClientActive as ‘ClientActive’,

@ClientInActive as ‘ClientInActive’,

case when (@TotalClientInstalled = 0) or (@TotalClientInstalled is null) Then ‘100’ Else (round(@ClientActive/ convert (float,@TotalClientInstalled)*100,2)) End as ‘ClientActive%’

All Active and Inactive Servers Client Status

Declare @CollectionID as Varchar(8)

Declare @TotalClientInstalled as Numeric(8)

Declare @ClientActive as Numeric(8)

Declare @ClientInActive as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalClientInstalled = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1 or Ch.ClientActiveStatus = 0) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

All Active Workstations Client Health Evaluation Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveEvalPass as Numeric(8)

Declare @ActiveEvalFail as Numeric(8)

Declare @ActiveEvalUnknown as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientStateDescription = ‘Active/Pass’ or Ch.ClientStateDescription = ‘Active/Fail’

or Ch.ClientStateDescription = ‘Active/Unknown’) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @ActiveEvalPass = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientStateDescription = ‘Active/Pass’) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @ActiveEvalFail = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientStateDescription = ‘Active/Fail’) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @ActiveEvalUnknown = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientStateDescription = ‘Active/Unknown’) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveEvalPass as ‘ActiveEvalPass’,

@ActiveEvalFail as ‘ActiveEvalFail’,

@ActiveEvalUnknown as ‘ActiveEvalUnknown’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveEvalPass/ convert (float,@TotalActive)*100,2)) End as ‘ActiveEvalPass%’

All Active Servers Client Health Evaluation Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveEvalPass as Numeric(8)

Declare @ActiveEvalFail as Numeric(8)

Declare @ActiveEvalUnknown as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientStateDescription = ‘Active/Pass’ or Ch.ClientStateDescription = ‘Active/Fail’

or Ch.ClientStateDescription = ‘Active/Unknown’) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @ActiveEvalPass = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientStateDescription = ‘Active/Pass’) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @ActiveEvalFail = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientStateDescription = ‘Active/Fail’) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @ActiveEvalUnknown = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientStateDescription = ‘Active/Unknown’) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveEvalPass as ‘ActiveEvalPass’,

@ActiveEvalFail as ‘ActiveEvalFail’,

@ActiveEvalUnknown as ‘ActiveEvalUnknown’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveEvalPass/ convert (float,@TotalActive)*100,2)) End as ‘ActiveEvalPass%’

All Active Workstations Client Heartbeat (DDR) Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveHeartBeatDDR as Numeric(8)

Declare @InActiveHeartBeatDDR as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @ActiveHeartBeatDDR = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveDDR = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @InActiveHeartBeatDDR = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveDDR = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveHeartBeatDDR as ‘ActiveHeartBeatDDR’,

@InActiveHeartBeatDDR as ‘InActiveHeartBeatDDR’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveHeartBeatDDR/ convert (float,@TotalActive)*100,2)) End as ‘ActiveHeartBeatDDR%’

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveHeartBeatDDR as Numeric(8)

Declare @InActiveHeartBeatDDR as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @ActiveHeartBeatDDR = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveDDR = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @InActiveHeartBeatDDR = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveDDR = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveHeartBeatDDR as ‘ActiveHeartBeatDDR’,

@InActiveHeartBeatDDR as ‘InActiveHeartBeatDDR’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveHeartBeatDDR/ convert (float,@TotalActive)*100,2)) End as ‘ActiveHeartBeatDDR%’

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveHWInv as Numeric(8)

Declare @InActiveHWInv as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @ActiveHWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveHW = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @InActiveHWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveHW = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveHWInv as ‘ActiveHWInv’,

@InActiveHWInv as ‘InActiveHWInv’,

case when (@TotalActive =0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveHWInv/ convert (float,@TotalActive)*100,2)) End as ‘ActiveHWInv%’

All Active Servers Client Hardware Inventory Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveHWInv as Numeric(8)

Declare @InActiveHWInv as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in ( Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @ActiveHWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveHW = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @InActiveHWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveHW = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveHWInv as ‘ActiveHWInv’,

@InActiveHWInv as ‘InActiveHWInv’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveHWInv/ convert (float,@TotalActive)*100,2)) End as ‘ActiveHWInv%’

All Active Workstations Client Software Inventory Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveSWInv as Numeric(8)

Declare @InActiveSWInv as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @ActiveSWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveSW = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @InActiveSWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveSW = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveSWInv as ‘ActiveSWInv’,

@InActiveSWInv as ‘InActiveSWInv’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveSWInv/ convert (float,@TotalActive)*100,2)) End as ‘ActiveSWInv%’

All Active Servers Client Software Inventory Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveSWInv as Numeric(8)

Declare @InActiveSWInv as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @ActiveSWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveSW = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @InActiveSWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveSW = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveSWInv as ‘ActiveSWInv’,

@InActiveSWInv as ‘InActiveSWInv’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveSWInv/ convert (float,@TotalActive)*100,2)) End as ‘ActiveSWInv%’

All Active Workstations Client Policy Request Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActivePolicyRequest as Numeric(8)

Declare @InActivePolicyRequest as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @ActivePolicyRequest = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActivePolicyRequest = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @InActivePolicyRequest = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActivePolicyRequest = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select

@TotalActive as ‘TotalActive’,

@ActivePolicyRequest as ‘ActivePolicyRequest’,

@InActivePolicyRequest as ‘InActivePolicyRequest’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActivePolicyRequest/ convert (float,@TotalActive)*100,2)) End as ‘ActivePolicyRequest%’

All Active Servers Client Policy Request Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActivePolicyRequest as Numeric(8)

Declare @InActivePolicyRequest as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @ActivePolicyRequest = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActivePolicyRequest = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @InActivePolicyRequest = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActivePolicyRequest = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select

@TotalActive as ‘TotalActive’,

@ActivePolicyRequest as ‘ActivePolicyRequest’,

@InActivePolicyRequest as ‘InActivePolicyRequest’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActivePolicyRequest/ convert (float,@TotalActive)*100,2)) End as ‘ActivePolicyRequest%’

select @ClientActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @ClientInActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 0) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select

@TotalClientInstalled as ‘TotalClientInstalled’,

@ClientActive as ‘ClientActive’,

@ClientInActive as ‘ClientInActive’,

case when (@TotalClientInstalled = 0) or (@TotalClientInstalled is null) Then ‘100’ Else (round(@ClientActive/ convert (float,@TotalClientInstalled)*100,2)) End as ‘ClientActive%’

Declare @ClientInstalled as Numeric(8)

Declare @ClientNotInstalled as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalClient = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID in (

select ResourceID from v_R_System where (Client0 = 1 or Client0 = 0 or Client0 is null) and Unknown0 is null

and Operating_System_Name_and0 like ‘%Workstation%’) )

select @ClientInstalled = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID in (

select ResourceID from v_R_System where Client0 = 1 and Operating_System_Name_and0 like ‘%Workstation%’) )

select @ClientNotInstalled = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID and ResourceID in (

select ResourceID from v_R_System where (Client0 = 0 or Client0 is null) and Unknown0 is null

and Operating_System_Name_and0 like ‘%Workstation%’) )

select

@TotalClient as ‘TotalClient’,

@ClientInstalled as ‘ClientInstalled’,

@ClientNotInstalled as ‘ClientNotInstalled’,

case when (@TotalClient = 0) or (@TotalClient is null) Then ‘100’ Else (round(@ClientInstalled/ convert (float,@TotalClient)*100,2)) End as ‘ClientInstalled%’

All PCs with No Clients based on OS Category status

Declare @CollectionID as Varchar(8)

Declare @TotalNoClientAgent as Numeric(8)

Declare @NoClientAgentWindowsOS as Numeric(8)

Declare @NoClientAgentWindowsOSLastLogonwithin7Days as Numeric(8)

Declare @NoClientAgentWindowsOSNOtLastLogonwithin7Days as Numeric(8)

Declare @NoClientAgentNonWindowsOS as Numeric(8)

Set @CollectionID = ‘SMS00001’ — specify scope collection ID

select @TotalNoClientAgent = (

Select count(Vrs.ResourceID) as ‘Count’ from v_R_System Vrs

inner join v_FullCollectionMembership Vf on Vrs.ResourceID = Vf.ResourceID

where (Vrs.Client0 = 0 or Vrs.Client0 is null)

and Vf.CollectionID = @CollectionID )

select @NoClientAgentWindowsOS = (

Select count(Vrs.ResourceID) as ‘Count’ from v_R_System Vrs

inner join v_FullCollectionMembership Vf on Vrs.ResourceID = Vf.ResourceID

where (Vrs.Client0 = 0 or Vrs.Client0 is null)

and Vrs.Unknown0 is null

and Vrs.Operating_System_Name_and0 like ‘%windows%’

and Vf.CollectionID = @CollectionID )

select @NoClientAgentWindowsOSLastLogonwithin7Days = (

Select count(Vrs.ResourceID) as ‘Count’ from v_R_System Vrs

inner join v_FullCollectionMembership Vf on Vrs.ResourceID = Vf.ResourceID

where (Vrs.Client0 = 0 or Vrs.Client0 is null)

and Vrs.Unknown0 is null

and Vrs.Operating_System_Name_and0 like ‘%windows%’

and (DATEDIFF(day,Last_Logon_Timestamp0, GetDate())) < 7

and Vf.CollectionID = @CollectionID )

select @NoClientAgentWindowsOSNOtLastLogonwithin7Days = (

Select count(Vrs.ResourceID) as ‘Count’ from v_R_System Vrs

inner join v_FullCollectionMembership Vf on Vrs.ResourceID = Vf.ResourceID

where (Vrs.Client0 = 0 or Vrs.Client0 is null)

and Vrs.Unknown0 is null

and Vrs.Operating_System_Name_and0 like ‘%windows%’

and (DATEDIFF(day,Last_Logon_Timestamp0, GetDate())) >= 7

and Vf.CollectionID = @CollectionID )

select @NoClientAgentNonWindowsOS = (

Select count(Vrs.ResourceID) as ‘Count’ from v_R_System Vrs

inner join v_FullCollectionMembership Vf on Vrs.ResourceID = Vf.ResourceID

where (Vrs.Client0 = 0 or Vrs.Client0 is null)

and Vrs.Unknown0 is null

and Vrs.Operating_System_Name_and0 Not like ‘%windows%’

and Vf.CollectionID = @CollectionID )

select

@TotalNoClientAgent as ‘TotalNoClientAgent’,

@NoClientAgentWindowsOS as ‘NoClientAgentWindowsOS’,

@NoClientAgentNonWindowsOS as ‘NoClientAgentNonWindowsOS’,

@NoClientAgentWindowsOSLastLogonwithin7Days as’NoClientAgentWindowsOSLastLogonwithin7Days’,

@NoClientAgentWindowsOSNOtLastLogonwithin7Days as ‘NoClientAgentWindowsOSNOtLastLogonwithin7Days’,

case when (@NoClientAgentWindowsOS = 0) or (@NoClientAgentWindowsOS is null) Then ‘100’ Else (round(@NoClientAgentWindowsOSLastLogonwithin7Days/ convert (float,@NoClientAgentWindowsOS)*100,2)) End as ‘NoClientAgentWindowsOSLastLogonwithin7Days%

All Workstations Client version status

Declare @CollectionID as Varchar(8)

Set @CollectionID = ‘SMS00001’ — specify scope collection ID

Select sys.Client_Version0 as ‘Client Agent Version’, count (sys.ResourceID) as ‘Count’ from v_R_System sys

inner join v_CH_ClientSummary ch on sys.ResourceID = ch.ResourceID

inner join v_FullCollectionMembership Vf on sys.ResourceID = Vf.ResourceID

where (Ch.ClientActiveStatus = 1 and Sys.Operating_System_Name_and0 like ‘%Workstation%’)

and Vf.CollectionID = @CollectionID

Group by sys.Client_Version0

Order by sys.Client_Version0 desc

All Workstations Client Installation Failure status

select count(cdr.MachineID) as ‘Count’,

cdr.CP_LastInstallationError as ‘Error Code’

from v_CombinedDeviceResources cdr

where

cdr.IsClient = 0

and cdr.DeviceOS like ‘%Windows%’

group by cdr.CP_LastInstallationError

All Workstations Inactive Clients detailed status

Declare @SCCMManagedWorkstationsScopeCollectionID as varchar(8)

Set @SCCMManagedWorkstationsScopeCollectionID = ‘SMS00001’ — specify scope collection ID

Select

Distinct (VRS.Netbios_Name0) as ‘Name’,

Case when VRS.Client0 = 1 Then ‘Yes’ Else ‘No’ End ‘Client’,

Case when VRS.Active0 = 1 Then ‘Yes’ Else ‘No’ End ‘Active’,

Case when v_CH_ClientSummary.ClientActiveStatus = 1 Then ‘Yes’ Else ‘No’ End ‘ClientHealthActive’,

v_CH_ClientSummary.ClientStateDescription as ‘ClientHealthDescription’,

System_Disc.AD_Site_Name0 as ‘ADSiteName’,

Vrs.Operating_System_Name_and0 as ‘OSType’,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 as ‘AssignedSite’,

v_CH_ClientSummary.LastActiveTime as ‘LastCommunicated’,

DateDiff(D, v_CH_ClientSummary.LastActiveTime, GetDate()) ‘LastCommunicatedDays’,

Vrs.Creation_Date0 as ‘ClientCreation’,

DateDiff(D, Vrs.Creation_Date0, GetDate()) ‘ClientCreationDays’,

System_Disc.Last_Logon_Timestamp0 as ‘LastLogon’,

DateDiff(D, System_Disc.Last_Logon_Timestamp0, GetDate()) ‘LastLogonDays’,

System_Disc.Distinguished_Name0 as ‘OUName’

from V_R_System Vrs

Left Outer join v_RA_System_SMSAssignedSites on v_RA_System_SMSAssignedSites.ResourceID = VRS.ResourceId

Left Outer join v_Gs_Operating_System on v_Gs_Operating_System .ResourceID = VRS.ResourceId

left outer join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId

left outer join v_CH_ClientSummary on v_CH_ClientSummary.ResourceID = VRS.ResourceId

left outer join System_Disc on System_Disc.ItemKey = VRS.ResourceId

where (Vrs.Client0 = 1 and v_CH_ClientSummary.ClientActiveStatus <> 1 ) and

(VRS.Obsolete0 = 0 or VRS.Obsolete0 is null) and

v_FullCollectionMembership.CollectionID = @SCCMManagedWorkstationsScopeCollectionID and

Vrs.Operating_System_Name_and0 like ‘%Workstation%’

order by System_Disc.Last_Logon_Timestamp0 desc

All Obsolete Clients detailed status

Select

ResourceID as ‘ResourceID’,

Name0 as ‘MachineName’,

User_Name0 as ‘LogonUserName’,

Case When Client0 = 1 Then ‘Yes’ Else ‘No’ End as ‘Client’,

Case When Obsolete0 = 1 Then ‘Yes’ Else ‘No’ End as ‘Obsolete’,

Case When Active0 = 1 Then ‘Yes’ Else ‘No’ End as ‘Active’,

AD_Site_Name0 as ‘ADSiteName’,

Operating_System_Name_and0 as ‘OSType’,

Client_Version0 as ‘ClientVersion’,

Creation_Date0 as ‘CreationDateinSCCM’

from v_R_system

where

(Obsolete0 =1 and Active0 = 0)

and Name0 not like ‘%Unknown%’

All Workstations Client Agent Detailed Report

Declare @CollectionID as Varchar(8)

Set @CollectionID = ‘SMS00001’ — specify scope collection ID

select distinct(Name),Case when IsClient= 1 then ‘Healthy’ else ‘UnHealthy’ end as ‘AgentStatus’,

(select case when count (v_GS_WORKSTATION_STATUS.ResourceID)=1 then ‘Healthy’ else ‘UnHealthy’ end

from v_GS_WORKSTATION_STATUS where DATEDIFF (day,LastHWScan,GetDate())<30 and ResourceID=v_FullCollectionMembership.ResourceID)

as ‘HWScanStatus’,

(select case when count (v_GS_LastSoftwareScan.ResourceID)=1 then ‘Healthy’ else ‘UnHealthy’ end

from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<30 and ResourceID=v_FullCollectionMembership.ResourceID)

as ‘SWScanStatus’,

(select case when count (v_UpdateScanStatus.ResourceID)=1 then ‘Healthy’ else ‘UnHealthy’ end

from v_UpdateScanStatus where DATEDIFF (day,LastScanTime,GetDate())<30 and LastErrorCode = 0 and ResourceID=v_FullCollectionMembership.ResourceID)

as ‘WSUSScanStatus’,

(select DATEDIFF (day,LastHWScan,GetDate()) from v_GS_WORKSTATION_STATUS

where ResourceID=v_FullCollectionMembership.ResourceID)

All Client and Inventory Health Report

Declare @CollectionID as Varchar(8)

Declare @TotalMachines as Numeric(5)

Declare @Healthy as Numeric(5)

Declare @UnHealthy as Numeric(5)

Declare @HWInventorySuccess as Numeric(5)

Declare @HWInventoryNotRun as Numeric(5)

Declare @SWInventorySuccess as Numeric(5)

Declare @SWInventoryNotRun as Numeric(5)

Declare @WSUSScanSuccess as Numeric(5)

Declare @WSUSScanNotRun as Numeric(5)

Set @CollectionID = ‘SMS00001’ — Specify Scope Collection ID

select @TotalMachines = (select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID)

select @Healthy = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsActive = 1 and IsObsolete != 1 and IsClient = 1 )

select @UnHealthy = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and ResourceID Not in (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsActive = 1 and IsObsolete != 1 and IsClient = 1 ) )

select @HWInventorySuccess = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where (DATEDIFF (day,LastHWScan,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) )

select @HWInventoryNotRun = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

select @SWInventorySuccess = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where (DATEDIFF (day,LastScanDate,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) )

select @SWInventoryNotRun = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

select @WSUSScanSuccess = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and (DATEDIFF (day,LastScanTime,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) )

select @WSUSScanNotRun = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

select

@TotalMachines as ‘TotalMachines’,

@Healthy as ‘Healthy’,

@UnHealthy as ‘UnHealthy’,

(Select(@Healthy/@TotalMachines)*100) as ‘Healthy%’,

@HWInventorySuccess as ‘HWInventorySuccess’,

@HWInventoryNotRun as ‘HWInventoryNotRun’,

(Select(@HWInventorySuccess/@Healthy)*100) as ‘HWInventorySuccess%’,

@SWInventorySuccess as ‘SWInventorySuccess’,

@SWInventoryNotRun as ‘SWInventoryNotRun’,

(Select(@SWInventorySuccess/@Healthy)*100) as ‘SWInventorySuccess%’,

@WSUSScanSuccess as ‘WSUSScanSuccess’,

@WSUSScanNotRun as ‘WSUSScanNotRun’,

(Select(@WSUSScanSuccess/@Healthy)*100) as ‘WSUSScanSuccess%’

as ‘LastHWScanDays’,

(select DATEDIFF (day,LastScanDate,GetDate()) from v_GS_LastSoftwareScan

where ResourceID=v_FullCollectionMembership.ResourceID)

as ‘LastSWScanDays’,

(select DATEDIFF (day,LastScanTime,GetDate()) from v_UpdateScanStatus

where LastErrorCode = 0 and ResourceID=v_FullCollectionMembership.ResourceID)

as ‘LastWSUSScanDays’

from v_FullCollectionMembership where CollectionID = @CollectionID

and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’)

order by 2 desc

Check Client Versions with Percentage

Declare @CollectionID Varchar(8)

Set @CollectionID = ‘SMS00001’

Select sys.Client_version0 as ‘Client Version’,

CASE

WHEN sys.client_version0 = ‘5.00.8325.1000’ THEN ‘ConfigMgr 1511’

WHEN sys.client_version0 = ‘5.00.8355.1000’ THEN ‘ConfigMgr 1602’

WHEN sys.client_version0 = ‘5.00.8355.1306’ THEN ‘ConfigMgr 1602 – Update Rollup 1’

WHEN sys.client_version0 = ‘5.00.8412.1007’ THEN ‘ConfigMgr 1606’

WHEN sys.client_version0 = ‘5.00.8355.1306’ THEN ‘ConfigMgr 1602 – Update Rollup 1’

WHEN sys.client_version0 = ‘5.00.8412.1307’ THEN ‘ConfigMgr 1606 – Update Rollup 1’

WHEN sys.client_version0 = ‘5.00.8458.1007’ THEN ‘ConfigMgr 1610’

WHEN sys.client_version0 = ‘5.00.8458.1520’ THEN ‘SCCM 1610 – Update rollup (KB4010155)’

WHEN sys.client_version0 = ‘5.00.8498.1008’ THEN ‘ConfigMgr 1702’

WHEN sys.client_version0 = ‘5.00.8498.1711’ THEN ‘ConfigMgr 1702 – Update rollup 1 (KB4019926)’

ELSE

sys.client_version0

END as ‘ConfigMgr Release’,

Count(DISTINCT sys.ResourceID) as ‘Client Count’,

(STR((COUNT(sys.ResourceID)*100.0/(

Select COUNT(SYS.ResourceID)

From v_FullCollectionMembership FCM INNER JOIN V_R_System sys on FCM.ResourceID = SYS.ResourceID

Where

FCM.CollectionID = @CollectionID

and

Sys.Client0= ‘1’)),5,2)) + ‘ %’ AS ‘Percent %’

From v_FullCollectionMembership FCM INNER JOIN V_R_System sys on FCM.ResourceID = SYS.ResourceID

Where SYS.Client0 = ‘1’ and FCM.CollectionID = @CollectionID

Group By sys.Client_version0

Order by sys.Client_version0 DESC

Client Version with Percentage

Declare @CollectionID Varchar(8)

Set @CollectionID = ‘SMS00001’

Select

sys.Client_version0 as ‘Client Version’,