How to check workstations and servers client health details in sccm?

Pull the SCCM Servers list from SCCM Central Database or refer the SCCM Design documents to get the Site servers list.

Open SQL Server Management Studio.

Execute the below query then you will get the Workstations Client Health Details.

How to check workstations and servers client health details in sccm
How to check workstations and servers client health details in sccm

 

SQL Query:
————————————
Select
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’)
)as ‘Total’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1
)as ‘Assigned’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned != 1
)as ‘NotAssigned’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1
)as ‘Installed’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient != 1
)as ‘NotInstalled’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1 and IsObsolete = 0
)as ‘NonObsolete’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1 and IsObsolete != 0
)as ‘Obsolete’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete = 0
)as ‘Active’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 0 and IsObsolete = 0
)as ‘InActive’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete = 0
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where DATEDIFF (day,LastHWScan,GetDate())<23)
) as ‘HW<30Days’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’)and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID Not in (select ResourceID from v_GS_WORKSTATION_STATUS where DATEDIFF (day,LastHWScan,GetDate())<23)
) as ‘HW>30Days’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23)
) as ‘SW<30Days’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID Not in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23)
) as ‘SW>30Days’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and DATEDIFF (day,LastScanTime,GetDate())<23)
) as ‘WSUS<30Days’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1 and IsObsolete != 1 and IsActive = 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID Not in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and DATEDIFF (day,LastScanTime,GetDate())<23)
) as ‘WSUS>30Days’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) 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())<23)
and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23))
) as ‘Healthy’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’)
and (ResourceID Not in (select ResourceID from v_GS_WORKSTATION_STATUS where DATEDIFF (day,LastHWScan,GetDate())<23)
or ResourceID Not in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23))
) as ‘UnHealthy’,
cast( (Select((
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) 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())<23)
and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23))
)/(Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1
))*100) as decimal(5,2))as ‘Healthy%’,
cast( (Select((
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and DATEDIFF (day,LastScanTime,GetDate())<23)
)/(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’) 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())<23)
and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23))
))*100) as decimal(5,2))as ‘WSUS%’

Execute the below query then you will get the Servers Client Health Details:

check workstations and servers client health details 1
check workstations and servers client health details 1

SQL Query:
————————–
Select
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’)
)as ‘Total’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1
)as ‘Assigned’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned != 1
)as ‘NotAssigned’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1
)as ‘Installed’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient != 1
)as ‘NotInstalled’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1 and IsObsolete = 0
)as ‘NonObsolete’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1 and IsObsolete != 0
)as ‘Obsolete’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete = 0
)as ‘Active’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 0 and IsObsolete = 0
)as ‘InActive’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete = 0
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where DATEDIFF (day,LastHWScan,GetDate())<23)
) as ‘HW<30Days’, (
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’)and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID Not in (select ResourceID from v_GS_WORKSTATION_STATUS where DATEDIFF (day,LastHWScan,GetDate())<23)
) as ‘HW>30Days’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23)
) as ‘SW<30Days’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID Not in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23)
) as ‘SW>30Days’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and DATEDIFF (day,LastScanTime,GetDate())<23)
) as ‘WSUS<30Days’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1 and IsObsolete != 1 and IsActive = 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID Not in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and DATEDIFF (day,LastScanTime,GetDate())<23)
) as ‘WSUS>30Days’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) 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())<23)
and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23))
) as ‘Healthy’,
(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’)
and (ResourceID Not in (select ResourceID from v_GS_WORKSTATION_STATUS whereDATEDIFF (day,LastHWScan,GetDate())<23)
or ResourceID Not in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23))
) as ‘UnHealthy’,
cast( (Select((
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) 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())<23)
and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23))
)/(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1
))*100) as decimal(5,2))as ‘Healthy%’,
cast( (Select((
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1
and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<23)
and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and DATEDIFF (day,LastScanTime,GetDate())<23)
)/(
Select COUNT(distinct(Name)) from v_FullCollectionMembership where ResourceID in (
select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’) 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())<23)
and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<23))
))*100) as decimal(5,2))as ‘WSUS%’

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

>