How to check clients hardware and WSUS scam 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.

How to check clients hardware and WSUS scam health details in SCCM?

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


Execute the below query then you will get the Workstations Client Health Details:
SQL Query:
————————————-
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 @WSUSScanSuccess as Numeric(5)
Declare @WSUSScanNotRun as Numeric(5)
Set @CollectionID = ‘SMS00001’
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 @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 ‘Total’,
@Healthy as ‘Healthy’,
@UnHealthy as ‘UnHealthy’,
cast( (Select(@Healthy/@TotalMachines)*100) as decimal(5,2))as ‘Healthy%’,
@HWInventorySuccess as ‘HW<30Days’,
@HWInventoryNotRun as ‘HW>30Days’,
cast( (Select(@HWInventorySuccess/@Healthy)*100) as decimal(5,2))as ‘HWInv%’,
@WSUSScanSuccess as ‘WSUS<30Days’,
@WSUSScanNotRun as ‘WSUS>30Days’,
cast( (Select(@WSUSScanSuccess/@Healthy)*100) as decimal(5,2))as ‘WSUS%’

Hope this will be helpful.


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 →