SCCM Client Status - Specific Device Collection

SCCM Client Status - Specific Device Collection

Change the CollectionID,

select s.Name0,s.User_Domain0,
CASE Client0 WHEN '0' THEN 'No' WHEN '1' THEN 'Yes' ELSE 'Unknown' END AS [Client Status]
from v_r_system s

Join _RES_COLL_SMS00001 as coll on S.Name0=coll.name


--------------------------------------------------------------------------------------------------------------------------


select
sys.Name0 as 'Computer Name',
sys.User_Name0 as 'User Name',
summ.ClientStateDescription,
case when summ.ClientActiveStatus = 0 then 'Inactive'
when summ.ClientActiveStatus = 1 then 'Active'
end as 'ClientActiveStatus',
summ.LastActiveTime,
case when summ.IsActiveDDR = 0 then 'Inactive'
when summ.IsActiveDDR = 1 then 'Active'
end as 'IsActiveDDR',
case when summ.IsActiveHW = 0 then 'Inactive'
when summ.IsActiveHW = 1 then 'Active'
end as 'IsActiveHW',
case when summ.IsActiveSW = 0 then 'Inactive'
when summ.IsActiveSW = 1 then 'Active'
end as 'IsActiveSW',
case when summ.ISActivePolicyRequest = 0 then 'Inactive'
when summ.ISActivePolicyRequest = 1 then 'Active'
end as 'ISActivePolicyRequest',
case when summ.IsActiveStatusMessages = 0 then 'Inactive'
when summ.IsActiveStatusMessages = 1 then 'Active'
end as 'IsActiveStatusMessages',
summ.LastOnline,
summ.LastDDR,
summ.LastHW,
summ.LastSW,
summ.LastPolicyRequest,
summ.LastStatusMessage,
summ.LastHealthEvaluation,
case when LastHealthEvaluationResult = 1 then 'Not Yet Evaluated'
when LastHealthEvaluationResult = 2 then 'Not Applicable'
when LastHealthEvaluationResult = 3 then 'Evaluation Failed'
when LastHealthEvaluationResult = 4 then 'Evaluated Remediated Failed'
when LastHealthEvaluationResult = 5 then 'Not Evaluated Dependency Failed'
when LastHealthEvaluationResult = 6 then 'Evaluated Remediated Succeeded'
when LastHealthEvaluationResult = 7 then 'Evaluation Succeeded'
end as 'Last Health Evaluation Result',
case when LastEvaluationHealthy = 1 then 'Pass'
when LastEvaluationHealthy = 2 then 'Fail'
when LastEvaluationHealthy = 3 then 'Unknown'
end as 'Last Evaluation Healthy',
case when summ.ClientRemediationSuccess = 1 then 'Pass'
when summ.ClientRemediationSuccess = 2 then 'Fail'
else ''
end as 'ClientRemediationSuccess',
summ.ExpectedNextPolicyRequest
from v_CH_ClientSummary summ
inner join v_R_System sys on summ.ResourceID = sys.ResourceID
Join _RES_COLL_SMS00001 as coll on SYS.Name0=coll.name

order by sys.Name0