SCCM SQL query for Application deployment status with Error code

 select distinct

s1.netbios_name0 as 'Computer Name',

GS.Caption0 as 'OS',

s1.User_Name0 as 'User Name',

aa.ApplicationName,

aa.CollectionName as 'Target Collection',

case

when ae.AppEnforcementState = 1000 then 'Success'

when ae.AppEnforcementState = 1001 then 'Already Compliant'

when ae.AppEnforcementState = 1002 then 'Simulate Success'

when ae.AppEnforcementState = 2000 then 'In Progress'

when ae.AppEnforcementState = 2001 then 'Waiting for Content'

when ae.AppEnforcementState = 2002 then 'Installing'

when ae.AppEnforcementState = 2003 then 'Restart to Continue'

when ae.AppEnforcementState = 2004 then 'Waiting for maintenance window'

when ae.AppEnforcementState = 2005 then 'Waiting for schedule'

when ae.AppEnforcementState = 2006 then 'Downloading dependent content'

when ae.AppEnforcementState = 2007 then 'Installing dependent content'

when ae.AppEnforcementState = 2008 then 'Restart to complete'

when ae.AppEnforcementState = 2009 then 'Content downloaded'

when ae.AppEnforcementState = 2010 then 'Waiting for update'

when ae.AppEnforcementState = 2011 then 'Waiting for user session reconnect'

when ae.AppEnforcementState = 2012 then 'Waiting for user logoff'

when ae.AppEnforcementState = 2013 then 'Waiting for user logon'

when ae.AppEnforcementState = 2014 then 'Waiting to install'

when ae.AppEnforcementState = 2015 then 'Waiting retry'

when ae.AppEnforcementState = 2016 then 'Waiting for presentation mode'

when ae.AppEnforcementState = 2017 then 'Waiting for Orchestration'

when ae.AppEnforcementState = 2018 then 'Waiting for network'

when ae.AppEnforcementState = 2019 then 'Pending App-V Virtual Environment'

when ae.AppEnforcementState = 2020 then 'Updating App-V Virtual Environment'

when ae.AppEnforcementState = 3000 then 'Requirements not met'

when ae.AppEnforcementState = 3001 then 'Host platform not applicable'

when ae.AppEnforcementState = 4000 then 'Unknown'

when ae.AppEnforcementState = 5000 then 'Deployment failed'

when ae.AppEnforcementState = 5001 then 'Evaluation failed'

when ae.AppEnforcementState = 5002 then 'Deployment failed'

when ae.AppEnforcementState = 5003 then 'Failed to locate content'

when ae.AppEnforcementState = 5004 then 'Dependency installation failed'

when ae.AppEnforcementState = 5005 then 'Failed to download dependent content'

when ae.AppEnforcementState = 5006 then 'Conflicts with another application deployment'

when ae.AppEnforcementState = 5007 then 'Waiting retry'

when ae.AppEnforcementState = 5008 then 'Failed to uninstall superseded deployment type'

when ae.AppEnforcementState = 5009 then 'Failed to download superseded deployment type'

when ae.AppEnforcementState = 5010 then 'Failed to updating App-V Virtual Environment'

End as 'Status',

apr.ErrorCode

from v_R_System s1

left join vAppDTDeploymentResultsPerClient ae on ae.ResourceID = s1.ResourceID

left join v_ApplicationAssignment aa on ae.AssignmentID = aa.AssignmentID

left join v_GS_OPERATING_SYSTEM GS on GS.ResourceID=S1.ResourceID

left join vAppDeploymentErrorAssetDetails apr on s1.ResourceID=apr.MachineID and aa.AssignmentID=apr.AssignmentID

where aa.AssignmentID = '16790885'

order by s1.Netbios_Name0

SCCM SQL Query to get list of applications from a specific folder in the console.

 select 

app.DisplayName,

app.Description,

fol.objectpath,

app.CreatedBy,

app.LastModifiedBy,

app.DateCreated,

app.DateLastModified,

assgn.CollectionName [Deployed Collection]

from fn_ListLatestApplicationCIs(1033) app

left join v_ConfigurationItems conf on app.CI_UniqueID = conf.cI_uniqueID 

full join v_ApplicationAssignment assgn on conf.CI_UniqueID=assgn.AssignedCI_UniqueID

JOIN vFolderMembers fol ON fol.InstanceKey = conf.modelName

JOIN vSMS_Folders sms on fol.ContainerNodeID = sms.ContainerNodeID

where fol.objectpath like '/PROD/Application  Deployment/Test' or fol.objectpath like '/DEV/Test' or fol.objectpath like '/CAS/Application Deployment/Test'

order by fol.objectpath

Replace the folder name where its highlighted.

SCCM SQL query for OS build numbers

If we try to get OS and Build number information from V_GS_OperatingSystem, it may not uptodate if Hardware inventory is not updated.

If we need OS and build number information, even if the Client is not updated with recent Hardware inventory then we can use V_R_Sytem.

select sys.Name0 as 'Hostname',

sys.User_Name0 as 'Username',

case 

when sys.Operating_System_Name_and0 like '%server%' then 'Server' 

when sys.Operating_System_Name_and0 like '%workstation 6%' then 'Windows 7'

when sys.Operating_System_Name_and0 like '%workstation 5%' then 'Windows XP'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%19045%'  then 'Windows 10 Build 22H2'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%22621%'  then 'Windows 11 Build 22H2'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%17134%'  then 'Windows 10 Build 1803'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%22000%'  then 'Windows 11 Build 21H2'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%19044%'  then 'Windows 10 Build 21H2'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%19043%'  then 'Windows 10 Build 21H1'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%19042%'  then 'Windows 10 Build 20H2'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%19041%'  then 'Windows 10 Build 2004'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%18363%'  then 'Windows 10 Build 1909'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%18362%'  then 'Windows 10 Build 1903'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%17763%'  then 'Windows 10 Build 1809'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%16299%'  then 'Windows 10 Build 1709'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%15063%'  then 'Windows 10 Build 1703'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%14393%'  then 'Windows 10 Build 1607'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%10240%'  then 'Windows 10 Build 1507'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 like '%10586%'  then 'Windows 10 Build 1511'

when sys.Operating_System_Name_and0 like '%workstation 10%' and sys.Build01 is NULL then 'Windows 10 Build not specified'

else sys.Operating_System_Name_and0

end as 'O/S',

cs.Manufacturer0 [Make],

cs.Model0 [Model],

bios.SMBIOSBIOSVersion0

from V_R_system sys

left join v_gs_operating_system os on sys.ResourceID=os.ResourceID

left join v_FullCollectionMembership col on sys.ResourceID = col.ResourceID

LEFT JOIN v_GS_COMPUTER_SYSTEM cs on sys.ResourceID=cs.ResourceID

left JOIN v_GS_PC_BIOS BIOS ON sys.ResourceID=BIOS.ResourceID

where col.CollectionID = 'DEV01F7F'

WQL query to create SCCM collection based on patch deployment status

select 

SMS_R_SYSTEM.ResourceID,

SMS_R_SYSTEM.ResourceType,

SMS_R_SYSTEM.Name,

SMS_R_SYSTEM.SMSUniqueIdentifier,

SMS_R_SYSTEM.ResourceDomainORWorkgroup,

SMS_R_SYSTEM.Client

from SMS_R_System

inner join   SMS_SUMDeploymentAssetDetails on   SMS_SUMDeploymentAssetDetails.ResourceID = SMS_R_System.ResourceId 

where SMS_SUMDeploymentAssetDetails.AssignmentID = "10700151"   and SMS_SUMDeploymentAssetDetails.StatusType = "1"

Status type.

Value Status type

1         Success

2         InProgress

4         Unknown

5         Error

With Specific Deployment status 

select 

SYS.ResourceID,

SYS.ResourceType,

SYS.Name,

SYS.SMSUniqueIdentifier,

SYS.ResourceDomainORWorkgroup,

SYS.Client

from sms_r_system as sys inner join SMS_SUMDeploymentAssetDetails as offer on sys.ResourceID=offer.ResourceID 

WHERE offer.AssignmentID IN ('16794400') AND offer.StatusDescription = 'Pending system restart'

If you like to test it via Powershell to find out the column name to filter, try like below.

#Patches 

$SCCMPrimaryServername = 'server-105'

$SiteCode = 'AOL'

Get-WmiObject -ComputerName $SCCMPrimaryServername -Namespace root\sms\site_$SiteCode -Class SMS_SUMDeploymentAssetDetails  -Filter 'AssignmentID = "19700191"' | select DeviceName

WQL query to create SCCM collection based on Package deployment status

select 

SMS_R_SYSTEM.ResourceID,

SMS_R_SYSTEM.ResourceType,

SMS_R_SYSTEM.Name,

SMS_R_SYSTEM.SMSUniqueIdentifier,

SMS_R_SYSTEM.ResourceDomainORWorkgroup,

SMS_R_SYSTEM.Client

 from SMS_R_System

 inner join SMS_ClassicDeploymentAssetDetails on SMS_ClassicDeploymentAssetDetails.DeviceID = SMS_R_System.ResourceId 

where SMS_ClassicDeploymentAssetDetails.DeploymentID = 'ABC20140' and SMS_ClassicDeploymentAssetDetails.StatusType = '1'

Status type.

Value Status type

1 Success

2 InProgress

4 Unknown

5 Error

If you like to test it via powershell,

#Package 

$SCCMPrimaryServername = 'Server-25'

$SiteCode = 'AOL'

Get-WmiObject -ComputerName $SCCMPrimaryServername -Namespace root\sms\site_$SiteCode -Class SMS_ClassicDeploymentAssetDetails  -Filter 'DeploymentID = "AOL20140"' | select Devicename,StatusDescription

WQL query to create SCCM collection based on Application deployment status

 select 

SMS_R_SYSTEM.ResourceID,

SMS_R_SYSTEM.ResourceType,

SMS_R_SYSTEM.Name,

SMS_R_SYSTEM.SMSUniqueIdentifier,

SMS_R_SYSTEM.ResourceDomainORWorkgroup,

SMS_R_SYSTEM.Client

 from SMS_R_System

  inner join   SMS_AppDeploymentAssetDetails   on   SMS_AppDeploymentAssetDetails.MachineID = SMS_R_System.ResourceId 

where SMS_AppDeploymentAssetDetails.AssignmentID = "100000003"   and SMS_AppDeploymentAssetDetails.StatusType = "1"

Application status type. Possible values are:

Value Application status

1 Success

2 InProgress

3 RequirementsNotMet

4 Unknown

5 Error

If you like to test it via Powershell,

#Application 

$SCCMPrimaryServername = 'Server-25'

$SiteCode = 'AOL'

Get-WmiObject -ComputerName $SCCMPrimaryServername -Namespace root\sms\site_$SiteCode -Class SMS_AppDeploymentAssetDetails -Filter 'AssignmentID = "29725319"' | select MachineName 

SCCM SCAN error Same as HTTP status 401 - the requested resource requires user authentication.

Same as HTTP status 401 - the requested resource requires user authentication.

reset proxy to fix the issue.

netsh winhttp reset proxy

SCCM Client Installation error 1638 for VC_redist.x64.exe

SCCM Client Installation error 1638 for VC_redist.x64.exe

1638 meaning:

Another version of this product is already installed. Installation of this version cannot continue. To configure or remove the existing version of this product, use Add/Remove Programs on the Control Panel.

Uninstall the latest version from the machine and install the pre req as per MS.

Pre-req as per MS is old version than some of the machines in our network. 

"C:\ProgramData\Package Cache\{7f336035-fa39-4d06-bd17-fbf472a381e8}\VC_redist.x64.exe" /uninstall /quiet /norestart 

"C:\ProgramData\Package Cache\{9120a466-433b-4dd9-a5e0-3092abd2cc1d}\VC_redist.x86.exe" /uninstall /quiet /norestart 

"C:\ProgramData\Package Cache\{43d1ce82-6f55-4860-a938-20e5deb28b98}\VC_redist.x64.exe" /uninstall /quiet /norestart 

"C:\ProgramData\Package Cache\{fa7f6d52-f85e-48ef-8f56-a37268aa5772}\VC_redist.x64.exe" /uninstall /quiet /norestart 

"C:\ProgramData\Package Cache\{4b2f3795-f407-415e-88d5-8c8ab322909d}\VC_redist.x64.exe" /uninstall /quiet /norestart 

How to disable Metered network

Here is Network Card connection metered information:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DusmSvc\Profiles\{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}\* in REG_DWORD name UserCost indicate if metered or not

with {XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX} your Network Card GUID

and

UserCost = 0 when Metered is off

UserCost = 2 when Metered is on

You need to restart "Data Usage" Service DusmSvc before your settings take effect

Task sequence detailed failed steps with deployment ID

 select 

VRS.Name0,

vTS.Step,

vTS.GroupName,

vTS.ActionName,

vTS.ExecutionTime,

vTS.LastStatusMessageIDName,

vTS.ExitCode,

Vts.ActionOutput

from v_TaskExecutionStatus vTS

LEFT JOIN v_R_System VRS ON Vts.ResourceID = VRS.ResourceID

where vTS.AdvertisementID = 'ABC123456' --deployment ID

--and vrs.Name0 = 'machine1'

AND vTS.ExitCode !='0' and  vTS.ExitCode !='3010'

and vTS.LastStatusMessageIDName  Like '%Failed%'

order by vts.ExecutionTime

SCCM / MECM Status Message Queries for Client push from console.

 Go to \Monitoring\Overview\System Status\Status Message Queries in the MECM console

Click on "Create Status Message Query" and add the below query.

select stat.*, 

ins.*, 

att1.*, 

stat.Time 

from SMS_StatusMessage as stat 

left join SMS_StatMsgInsStrings as ins on stat.RecordID = ins.RecordID 

left join SMS_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID 

where stat.MessageType = 768 

and stat.MessageID >= 30106 

and stat.MessageID <= 30111 

and stat.Time >= ##PRM:SMS_StatusMessage.Time## order by stat.Time desc

Below status message ID can be used to create queries like above.

Query Title	Query Details
All Audit Status Messages for a Specific User	Message Type: 768 Message Attribute ID: 403
All Audit Status Messages from a Specific Site	Message Type: 768
Boundaries Created, Modified, or Deleted	Message IDs: 40600-40602
Client Component Configuration Changes	Message IDs: 30042-30047
Collections Created, Modified, or Deleted	Message IDs: 30015-30017
Collection Member Resources Manually Deleted	Message IDs: 30066-30067
Deployments Created, Modified, or Deleted	Message IDs: 30006-30008
Packages Created, Modified, or Deleted	Includes Package Conversion Status Message IDs: 30000-30002
Programs Created, Modified, or Deleted	Includes Package Conversion Status Message IDs: 30003-30005
Queries Created, Modified, or Deleted	Message IDs: 30063-30065
Remote Control Activity at a Specific Site, User, or System (4 Total)	Message IDs: 30069-30087
Security Scopes Created, Modified, Deleted, or Imported	Message IDs: 31200-31202 / 31220-31222 / 31207
Server Component Configuration Changes	Message IDs: 30033-30035 / 30039-30041 Site Control Changes
Site Addresses Created, Modified, or Deleted	Message IDs: 30018-30020

Query Title	Query Details
Audit - All Alert Actions	Includes DRS Alerts Message IDs: 30240-30244
Audit - All Application Actions	Message IDs: 30226-30228 / 49003-49005 / 52300
Audit - All Application Catalog Actions	Message IDs: 30800-30805 / 50000-50004
Audit - All Asset Intelligence Actions	Message IDs: 30208-30209 / 31001
Audit - All Azure and Co-Management Actions	Message IDs: 53001-53005 / 53401-53403 / 53501-53503
Audit - All Boundary Group Actions	Message IDs: 40500-40505
Audit - All Client and Collection Miscellaneous Actions	Includes Update Membership, Device Imports, Clear PXE Deployments Message IDs: 30104 / 30213 / 42021
Audit - All Client Configuration Requests (CCRs)	Client Push actions. Message IDs: 30106-30111
Audit - All Client Operations Actions	Includes “Right Click” actions. Message IDs: 40800-40804
Audit - All Client Settings Actions	Includes Antimalware Policies. Message IDs: 40300-40305
Audit - All CMPivot and Script Actions	Message IDs: 40805-40806 / 52500-52505
Audit - All Conditional Access Actions	Includes Exchange Online, SharePoint Online, and On-Prem Exchange actions. Message IDs: 30340-30341
Audit - All ConfigMgr Actions in Console	Checks components: Microsoft.ConfigurationManagement.exe / AdminUI.PS.Provider.dll
Audit - All Configuration Baseline Actions	Message IDs: 30168 / 30193-30198
Audit - All Configuration Items	Includes Compliance Settings and Endpoint Protection policy actions. Message IDs: 30152-30167
Audit - All Content Library Actions	Includes Content Library changes Message IDs: 30080 / 30189-30191
Audit - All Distribution Point Actions	Message IDs: 30009-30011 / 30068 / 30109 / 30125 / 30500-30503 / 40409-40410
Audit - All Distribution Point Changes	Message IDs: 40400-40409 / 40506
Audit - All Folder Actions	Message IDs: 30113-30117
Audit - All Messages
Audit - All Messages (Specified Message ID)
Audit - All Messages (Specified Timeline)
Audit - All Migration Actions	Message IDs: 30900-30907
Audit - All Mobile Device Management Actions	Message IDs: 40200-40206 / 45000-45004 / 47000-47002 / 48000-48003 / 49003-49005 / 51000-51006 / 52000-52020
Audit - All Phased Deployment Actions	Message IDs: 53601-53603
Audit - All Query Actions	Message IDs: 30063-30065 / 30302-30303
Audit - All Report Actions	Message IDs: 30091-30093 / 31000-31002
Audit - All Search Folder Actions	Message IDs: 30700-30702
Audit - All Secondary Site Actions	Message IDs: 30012-30014 / 30021-30023
Audit - All Site Server Boundary Actions	Message IDs: 30054-30056
Audit - All Site Server Definition Actions	Message IDs: 30030-30032
Audit - All Site Server Property Actions	Message IDs: 30024-30029
Audit - All Site Server Role Actions	Message IDs: 30036-30038
Audit - All Site Server Security Actions	Message IDs: 30057-30062 / 30210-30212 / 31200-31242 / 31203-31249
Audit - All Site Server SQL Actions	Includes Site Maintenance Tasks Message IDs: 30048-30053
Audit - All Software Metering Rules Actions	Message IDs: 30094-30095 / 30105
Audit - All Software Update Actions	Message IDs: 30112 / 30118-30124 / 30135-30137 / 30172 / 30183-30188 / 30196-30198 / 30219-30221 / 30229-30231 / 30506-30507 / 42031-42033 / 4900-49002
Audit - All User Object Actions	Message IDs: 30600-30606