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"

SCCM Client Failed to install - Error 1606 (WMI Corruption)

While install SCCM client you may receive the below error due to WMI issue

·         "File C:\Windows\ccmsetup\MicrosoftPolicyPlatformSetup.msi installation failed. Error text: ExitCode: 1603"

·         InstallFromManifest failed 0x80070643

·         "CcmSetup failed with error code 0x80070643"

Sometimes recreate WMI repository will fix the issue, If you still get the error, use the below command and try the client installation again

 Copy and paste the text into a batch file.

@echo off

sc config winmgmt start= disabled

net stop winmgmt /y

%systemdrive%

cd %windir%\system32\wbem

For /f %%s in ('dir /b *.dll') do regsvr32 /s %%s

wmiprvse /regserver

winmgmt /regserver

net start winmgmt

for /f %%s in ('dir /b *.mof *.mfl') do mofcomp %%s

exit

SCCM Client Status - Specific Device Collection

SCCM Client Status - Specific Device Collection

Change the CollectionID,

select s.Name0,s.User_Domain0,
CASE Client0 WHEN '0' THEN 'No' WHEN '1' THEN 'Yes' ELSE 'Unknown' END AS [Client Status]
from v_r_system s

Join _RES_COLL_SMS00001 as coll on S.Name0=coll.name


--------------------------------------------------------------------------------------------------------------------------


select
sys.Name0 as 'Computer Name',
sys.User_Name0 as 'User Name',
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,
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_CH_ClientSummary summ
inner join v_R_System sys on summ.ResourceID = sys.ResourceID
Join _RES_COLL_SMS00001 as coll on SYS.Name0=coll.name

order by sys.Name0

SQL Query - All Software Installed on Specific Device Collection

SQL Query - All Software Installed on Specific Device Collection

Change the collection ID,

SELECT distinct    dbo.v_R_System.Netbios_Name0, dbo.v_R_System.AD_Site_Name0, dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, dbo.v_GS_OPERATING_SYSTEM.Caption0
FROM         dbo.v_R_System INNER JOIN
                      dbo.v_GS_ADD_REMOVE_PROGRAMS ON dbo.v_R_System.ResourceID = dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID INNER JOIN
                      dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID

Join _RES_COLL_collectionID as coll on S.Name0=coll.name

SCCM OSD - Join OU based on Gateway IP

SCCM OSD - Join OU based on Gateway IP

Using OSD Variable "OSDDomainOUName" we can join computer to different OU based on gateway IP

Add a new step Dynamic Variables right after Apply Windows Settings

Choose Add Rule - Location, then enterprise the IP of the Gateway at that location

Next Click Add Variable, like below,

And Add Apply Network settings and in the Domain OU mention as %OSDDomainOUName% then the value will taken from the Dynamic variable which we mentioned before

Try it...this will help you to reduce the TS count