SCCM SQL - Client Health Status for Specific Collection

SCCM SQL - Client Health Status for Specific Collection

Declare @CollectionID as Varchar(8) Set @CollectionID = 'SMS00001'     --Specify the collection ID


select distinct(Name),Case when IsClient= 1 then 'Healthy' else 'Unhealthy' end as 'HealthStatus',  (select  case when count (v_GS_WORKSTATION_STATUS.ResourceID)=1 then 'Healthy' else  'Unhealthy' end  from v_GS_WORKSTATION_STATUS where DATEDIFF (day,LastHWScan,GetDate())<31 and ResourceID=v_FullCollectionMembership.ResourceID) as 'HWScanStatus', (select case when count (v_GS_LastSoftwareScan.ResourceID)=1 then 'Healthy' else 'Unhealthy' end  from v_GS_LastSoftwareScan where DATEDIFF (day,LastScanDate,GetDate())<31 and ResourceID=v_FullCollectionMembership.ResourceID) as 'SWScanStatus', (select case when count (v_UpdateScanStatus.ResourceID)=1 then 'Healthy' else 'Unhealthy' end from v_UpdateScanStatus where DATEDIFF (day,LastScanTime,GetDate())<31 and LastErrorCode = 0 and ResourceID=v_FullCollectionMembership.ResourceID) as 'WSUSScanStatus', (select DATEDIFF (day,LastHWScan,GetDate()) from v_GS_WORKSTATION_STATUS where ResourceID=v_FullCollectionMembership.ResourceID) as 'LastHWScanDays', (select DATEDIFF (day,LastScanDate,GetDate()) from v_GS_LastSoftwareScan  where ResourceID=v_FullCollectionMembership.ResourceID) as 'LastSWScanDays', (select DATEDIFF (day,LastScanTime,GetDate()) from v_UpdateScanStatus  where LastErrorCode = 0 and ResourceID=v_FullCollectionMembership.ResourceID) as 'LastWSUSScanDays' from v_FullCollectionMembership where CollectionID = @CollectionID  and ResourceID in ( select ResourceID from v_R_System where Operating_System_Name_and0 like '%Server%') order by 2 desc 

SCCM SQL Query - Machine Collection Count

SCCM SQL Query - Machine Collection Count

Machine collection count more than 2 collection

Note : excluding the built-in All Systems and All Desktops and Servers collections

SELECT v_R_System_Valid.Netbios_Name0
, count(v_FullCollectionMembership.CollectionID) As CollectionCount
FROM v_FullCollectionMembership
INNER JOIN v_R_System_Valid
  ON v_R_System_Valid.ResourceID = v_FullCollectionMembership.ResourceID
WHERE v_FullCollectionMembership.CollectionID NOT IN ('SMSDM003','SMS00001')
GROUP BY v_R_System_Valid.Netbios_Name0
HAVING count(v_FullCollectionMembership.CollectionID) > 1

SCCM OSD - Set Time Zone Using tzutil.exe

SCCM OSD - Set Time Zone Using tzutil.exe

If you are using SCCM for your operating system deployments across different countries, then you may want to set the time zone within the task sequence.

To do this, you can take advantage of the built in Windows tool call tzutil.exe

Powershel script



powershell.exe -ExecutionPolicy Unrestricted -Command "tzutil.exe /s 'Eastern Standard Time



Create as a package with batch file

@echo off

REM Sets time zone for Central Australia Standard Time

TZUTIL.EXE /s "Cen. Australia Standard Time"