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

check workstations and servers client health details 1

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

Check out Best Alternative to InfoPath -> Try Now

You May Also like the Following SharePoint Online Tutorials:

About Ramu Rao

I have been working as a Team Lead in IT last 6 years with a strong focus on Microsoft Server Technologies I have been working on Windows Server 2008, 2008 R2, 2012 & SCCM 2012 R2 etc. Interested in everything related to Configuration Manager & Windows Azure. Keep learning Keep sharing and keep growing !!

View all posts by Ramu Rao →