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

SCCM - SQL Query_Update Deployment Status using AssignmentID

SCCM - SQL Query_Update Deployment Status using AssignmentID

select

vrs.name0 as machineName,

vrs.AD_site_Name0 as ADSiteName,

vrs.User_Name0 as UserName,

a.Assignment_UniqueID as DeploymentID,

a.AssignmentName as DeploymentName,

sn.StateName as LastEnforcementState,

assc.StateTime asStatusTime,

assc.LastErrorCode as LastErrorCode

from v_CIAssignment a

join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID

join v_StateNames sn on assc.Statetype = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

join v_R_System vrs on vrs.ResourceID=assc.ResourceID

where a.AssignmentID in (xxxxxxxx)


order by DeploymentName

Sysprep Error : Unable to sysprep the machine, hr=80004005

Sysprep Error : Unable to sysprep the machine, hr=80004005

Sysprep may fail without remove the below app without do SCCM capture

Powershell script to remove app

Command to list all available windows 10 apps

Get-AppxProvisionedPackage -Online | Select-Object PackageName

Command to remove the app

Remove-AppxProvisionedPackage -Online -PackageName <package name>

get-appxpackage *getstarted* | remove-appxpackage
get-appxpackage *photoshop* | remove-appxpackage
get-appxpackage *freshpaint* | remove-appxpackage
get-appxpackage *remotedesktop* | remove-appxpackage
get-appxpackage *skype* | remove-appxpackage
get-appxpackage *oneconnect* | remove-appxpackage
get-appxpackage *maps* | remove-appxpackage
get-appxpackage *eclipse* | remove-appxpackage
get-appxpackage *actipro* | remove-appxpackage
get-appxpackage *duolingo* | remove-appxpackage
get-appxpackage *bing* | remove-appxpackage
get-appxpackage *networkspeedtest* | remove-appxpackage
get-appxpackage *sway* | remove-appxpackage
get-appxpackage *autodesk* | remove-appxpackage
get-appxpackage *dolby* | remove-appxpackage
get-appxpackage *disney* | remove-appxpackage
get-appxpackage *candy* | remove-appxpackage
get-appxpackage *hiddencity* | remove-appxpackage

get-appxpackage *bubble* | remove-appxpackage

SCCM Account Permission and Ports

SCCM Account Permission and Ports

Software Account Permissions

Below Accounts used for AD discovery purpose

      Active Directory Discovery Account

Your Site server computer Account or User account must have read permission for below AD attributes

Active Directory group discovery account 

Active Directory system discovery account

Active Directory user discovery account

 Active Directory forest account

The Site Server Computer account must have full access required for System Management container and all its child objects

Note: Don’t grant interactive sign-in rights to this account and avoid account lockouts create service account

For extent AD schema, the user account must be either a member of the Schema Admins group or have been delegated sufficient permissions to modify the schema

Ex – SCCMAD@domain.com

      Client Push Installation Account

The Client Push user account must be a member of the local Administrators group on the target client computers. This account doesn't require Domain Admin rights (Using GPO we make our client push account as member of all domain machine local administrator. 

Note: Don’t grant interactive sign-in rights to this account and avoid account lockouts create service account

Ex – SCCMClientpush@domain.com

      Network Access Account

Client computers use the network access account when they can't use their local computer account to access content on distribution points. It mostly applies to workgroup clients and computers from untrusted domains. This account is also used during OS deployment, when the computer that's installing the OS doesn't yet have a computer account on the domain

Its only used for access content in distribution point where computer account unable to access it

This Account mush be in domain user and have access to Distribution point, It doesn’t need any special rights

Note: Don’t grant interactive sign-in rights to this account and avoid account lockouts create service account

Ex - SCCMNAA@domain.com

      Reporting Service Point Account

It’s a normal domain account, Configuration Manager automatically grants the specified user access to the site database. The user is displayed in the Accounts subfolder of the Security node in the Administration workspace with the ConfigMgr Reporting Services Point account name

Note: Don’t grant interactive sign-in rights to this account and avoid account lockouts create service account

Ex – SCCMSQLReporting@domaiin.com

      Task sequence domain join account

Windows Setup uses the Task sequence domain join account to join a newly imaged computer to a domain, the specific user account requires the Domain Join right in the target domain

Note: Don’t grant interactive sign-in rights or domain admin rights to this account and avoid account lockouts create service account

Ex – SCCMdomainjoin@domain.com

For other SCCM task like, site installation and role configuration, create an dedicated domain user account which doesn’t required any special permissions

Ex – SCCMAdmin@domain.com

Create separate account for SQL server service account, we use this for SQL server installation and configuration, this account doesn’t required any special permission.

Ex – SCCMSQL@domain.com

We can also create SCCM Admin group, which will help to troubleshoot SCCM server and clients

This group required local admin permission for all SCCM server and SCCM client computers

Ex – SCCMAdminGroup@domain.com

We can also create another SCCM AD group for having access in AD system container and all SCCM servers

This group required local admin permission for all SCCM server and full permission on System Management container in AD

Ex – SCCMSiteServer@domain.com

SCCM Required Ports

Ports required between clients to site server

From	To	UDP	TCP	Description	Direction
Client	App Catalog Website Point		80/443	http/https	Unidirection
Client	Client (wol)		9/25536	WOL/WUP	Unidirection
Client	NDES		80/443	http/https	Unidirection
Client	Cloud DP		443	https	Unidirection
Client	DP		80/443	http/https	Unidirection
Client	DP with Multi Cast	63000-64000	445	Multi Cast/SMB	Unidirection
Client	DP with PXE	67/68/69/4011		DHCP/TFTP/BINL	Unidirection
Client	FSP		80	http	Unidirection
Client	Domain		3268/3269	LDAP/LDAP SSL	Unidirection
Client	MP		10123/80/443	Client Notification/http/https	Unidirection
Client	SUP		80/8530/443/8531	http/https	Unidirection
Client	SMP		80/443/445	http/https/SMB	Unidirection
Client	PXE DP	67/68/69/4011		PXE Enable DP	Unidirection
Client	DP		445	Windows File Share	Unidirection

Ports Required between SCCM Site Server to Clients

From	To	UDP	TCP	Description	Direction
Console	Client		2701/3389	RC/RDP/RTC	Unidirection
Console	Client		135/445	WMI and Windows File Share	Unidirection
Console	Client		2701/3389	Remote Control	Unidirection
Console	Client			ICMP Echo Request	Unidirection
Console	Client	135		RPC Endpoint Mapper	Unidirection
Console	Client	49152-65535		RPC Ports	Unidirection

Windows Firewall Ports and Inbound / Outbound GPO Rule

It’s recommended to enable windows firewall in on each desktop & server, some certain things we can’t install without enable windows firewall.

If windows firewall is enable, we need to create few inbound and outbound rule in GPO to allow SCCM traffic. Below are the details

Name	Group	Profile	Enabled	Action
ICMP Wake-up proxy communication		All	Yes	Allow
RPC End Point Mapper		All	Yes	Allow
Configuration Manager remote control		All	Yes	Allow
Windows Management Instrumentation (ASync-In)	Windows Management Instrumentation (WMI)	Private, Public	Yes	Allow
Windows Management Instrumentation (WMI-In)	Windows Management Instrumentation (WMI)	Private, Public	Yes	Allow
Windows Management Instrumentation (DCOM-In)	Windows Management Instrumentation (WMI)	Private, Public	Yes	Allow
Windows Management Instrumentation (ASync-In)	Windows Management Instrumentation (WMI)	Domain	Yes	Allow
Windows Management Instrumentation (WMI-In)	Windows Management Instrumentation (WMI)	Domain	Yes	Allow
Windows Management Instrumentation (DCOM-In)	Windows Management Instrumentation (WMI)	Domain	Yes	Allow
File and Printer Sharing (LLMNR-UDP-In)	File and Printer Sharing	All	Yes	Allow
File and Printer Sharing (Echo Request – ICMPv6-In)	File and Printer Sharing	Private, Public	Yes	Allow
File and Printer Sharing (Echo Request – ICMPv4-In)	File and Printer Sharing	Private, Public	Yes	Allow
File and Printer Sharing (Spooler Service – RPC-EPMAP)	File and Printer Sharing	Private, Public	Yes	Allow
File and Printer Sharing (Spooler Service – RPC)	File and Printer Sharing	Private, Public	Yes	Allow
File and Printer Sharing (NB-Datagram-In)	File and Printer Sharing	Private, Public	Yes	Allow
File and Printer Sharing (NB-Name-In)	File and Printer Sharing	Private, Public	Yes	Allow
File and Printer Sharing (SMB-In)	File and Printer Sharing	Private, Public	Yes	Allow
File and Printer Sharing (NB-Session-In)	File and Printer Sharing	Private, Public	Yes	Allow
File and Printer Sharing (Echo Request – ICMPv6-In)	File and Printer Sharing	Domain	Yes	Allow
File and Printer Sharing (Echo Request – ICMPv4-In)	File and Printer Sharing	Domain	Yes	Allow
File and Printer Sharing (Spooler Service – RPC-EPMAP)	File and Printer Sharing	Domain	Yes	Allow
File and Printer Sharing (Spooler Service – RPC)	File and Printer Sharing	Domain	Yes	Allow
File and Printer Sharing (NB-Datagram-In)	File and Printer Sharing	Domain	Yes	Allow
File and Printer Sharing (NB-Name-In)	File and Printer Sharing	Domain	Yes	Allow
File and Printer Sharing (SMB-In)	File and Printer Sharing	Domain	Yes	Allow
File and Printer Sharing (NB-Session-In)	File and Printer Sharing	Domain	Yes	Allow
SQL Ports for SCCM	TCP 1433	Domain,Private, Public	Yes	Allow
SQL Ports for SCCM	TCP 4022	Domain,Private, Public	Yes	Allow