SCCM Reporting
Question
Is there any inbuit report in SCCM which displays list of all devices along with client status (InstalledNot installed) against each in our environment.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Answers ( 8 )
Hi,
Use below SQL query to get the result.
————————————————————————————————————-
Declare @CollectionID Varchar(8)
Set @CollectionID = ‘SMS00001’
select
Vrs.Name0 as ‘Computer Name’,
Vrs.User_Name0 as ‘User Name’,
case when Vrs.client0 = 1 then ‘Installed’
else ‘NotInstalled’ end as ‘ClientAgentStatus’,
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,
DATEDIFF ( DAY ,summ.LastPolicyRequest,getdate() ) ‘Last Communicated Day’,
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_R_System Vrs
Left join v_CH_ClientSummary summ on Vrs.ResourceID = summ.ResourceID
Where Vrs.ResourceID in (select ResourceID from v_FullCollectionMembership fcm where fcm.CollectionID = @CollectionID)
order by Vrs.Name0
———————————————————————————————————————————
Thanks
Karthikeyan
Hi Karthik,
SQL Query Works like a charm! Thanks.
It’s not very relevant report to understand the client health. I would rather use latest hardware inventory report with device name to understand whether the client is healthy or not
what is the hardware inventory report with device name to extract? and does that report will provide details of all computers with NO SCCM client installed as well..
if there is no sccm client on it there wont be any details on the machine. are you just looking for a report that gives you a list of systems that doesnt have the sccm client on it?
I am looking for a report that gives me details of all the machine name along with Client installation status i.e either installed or not installed..
There is inbuilt reports for “client status summary” to displays the client check results of active clients for a given collection.
Check Inside Monitoring > Reporting > Client Status
Check the more client status Inbuilt reports here – https://docs.microsoft.com/en-us/mem/configmgr/core/servers/manage/list-of-reports#client-status
None of the reports you mentioed above will display computer name along with client status.
I need computer name and client status details for all the devices in my environment.