All check SCCM client _ SQL queries

All Client Settings Status 2

All Workstations Client Health Summary Status 2

All Servers Client Health Summary Status 2

All Active and Inactive Workstations Client Status 2

All Active and Inactive Servers Client Status 3

All Active Workstations Client Health Evaluation Status 3

All Active Servers Client Health Evaluation Status 4

All Active Workstations Client Heartbeat (DDR) Status 4

All Active Servers Client Hardware Inventory Status 6

All Active Workstations Client Software Inventory Status 7

All Active Servers Client Software Inventory Status 8

All Active Workstations Client Policy Request Status 8

All Active Servers Client Policy Request Status 9

All PCs with No Clients based on OS Category status 12

All Workstations Client version status 13

All Workstations Client Installation Failure status 14

All Workstations Inactive Clients detailed status 14

All Obsolete Clients detailed status 14

All Workstations Client Agent Detailed Report 15

All Client and Inventory Health Report 15

Check Client Versions with Percentage 17

Client Version with Percentage 17

 

All Client Settings Status

SELECT *

FROM v_CH_Settings

where SettingsID = 1

 

 

All Servers Client Health Summary Status

Declare @CollectionID as Varchar(8)

Declare @TotalClient as Numeric(8)

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 ‘%Server%’) )

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 ‘%Server%’) )

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 ‘%Server%’) )

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

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

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

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

@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’,

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

error

Enjoy this blog? Please spread the word :)

RSS
Follow by Email
Facebook
Facebook
Twitter
Visit Us
LinkedIn