Application deployment queries

Sometimes you may want to query the database for application deployment status.

Completion Statistics

This query gives results on Completion statistics. Replace DeploymentID with the actual deploymentID.

-- Completion Statistics
select  all SMS_DeploymentSummary.ApplicationName,
SMS_DeploymentSummary.AssignmentID,
SMS_DeploymentSummary.CI_ID,
SMS_DeploymentSummary.CollectionID,
SMS_DeploymentSummary.CollectionName,
SMS_DeploymentSummary.CollectionType,
SMS_DeploymentSummary.CreationTime,
SMS_DeploymentSummary.DeploymentID,
SMS_DeploymentSummary.DeploymentIntent,
SMS_DeploymentSummary.DeploymentTime,
SMS_DeploymentSummary.DesiredConfigType,
SMS_DeploymentSummary.EnforcementDeadline,SMS_DeploymentSummary.FeatureType,
SMS_DeploymentSummary.ModelName,
SMS_DeploymentSummary.ModificationTime,
SMS_DeploymentSummary.NumberErrors,
SMS_DeploymentSummary.NumberInProgress,SMS_DeploymentSummary.NumberOther,
SMS_DeploymentSummary.NumberSuccess,
SMS_DeploymentSummary.NumberTargeted,
SMS_DeploymentSummary.NumberUnknown,
SMS_DeploymentSummary.ObjectTypeID,
SMS_DeploymentSummary.PackageID,SMS_DeploymentSummary.PolicyModelID,SMS_DeploymentSummary.ProgramName,
SMS_DeploymentSummary.RequireApproval,
SMS_DeploymentSummary.SecuredObjectId,
SMS_DeploymentSummary.SoftwareName,
SMS_DeploymentSummary.SummarizationTime,
SMS_DeploymentSummary.SummaryType 
from fn_DeploymentSummary(1033) AS SMS_DeploymentSummary  
where SMS_DeploymentSummary.DeploymentID = N'{DD980965-E85B-40A9-AF6A-1725ABEE37A5}' 

It corresponds to Completion Statistics you see in SCCM console.

Deployment Status

Want to get deployment status of an application? Use those below. Make sure you replace AssignmentID and PolicyModelID and CollectionID with actual ones.

-- Success
SELECT        AppCI, AppName, AppStatusType, AssignmentID, AssignmentUniqueID, CollectionID, 
CollectionName, DeploymentIntent, DTCI, DTModelID, DTName, EnforcementState, PolicyModelID, 
StartTime, StatusType, Technology, 
                         Total
FROM            dbo.fn_AppDeploymentStatus(1033) AS SMS_AppDeploymentStatus
WHERE        (AssignmentID = N'16777366') AND (PolicyModelID = N'16838528') AND (StatusType = 1) AND (AppStatusType = 1)

-- In Progress
SELECT        AppCI, AppName, AppStatusType, AssignmentID,
AssignmentUniqueID, CollectionID, CollectionName, 
DeploymentIntent, DTCI, DTModelID, DTName, EnforcementState, PolicyModelID, 
StartTime, StatusType, Technology, 
                         Total
FROM            dbo.fn_AppDeploymentStatus(1033) AS SMS_AppDeploymentStatus
WHERE        (AssignmentID = N'16777366') AND (PolicyModelID = N'16838528') AND (StatusType = 2) AND (AppStatusType = 2)

-- Error
SELECT        AppCI, AppName, AppStatusType, AssignmentID, 
AssignmentUniqueID, CollectionID, CollectionName, DeploymentIntent, DTCI, DTModelID,
DTName, EnforcementState, ErrorCode, PolicyModelID, StartTime, StatusType, 
                         Technology, Total
FROM            dbo.fn_AppDeploymentErrorStatus(1033) AS SMS_AppDeploymentErrorStatus
WHERE        (AssignmentID = N'16777366') AND (PolicyModelID = N'16838528') AND (StatusType = 5) AND (AppStatusType = 5)

-- Requirement not met
SELECT        AppCI, AppName, AppStatusType, AssignmentID, AssignmentUniqueID, CollectionID, CollectionName, DeploymentIntent, 
DTCI, DTModelID, DTName, EnforcementState, PolicyModelID, RequirementName, RequirementType, 
                         RuleID, StartTime, StatusType, Technology, Total
FROM            dbo.fn_AppDeploymentRNMStatus(1033) AS __ntRequirementsNotMetStatus0
WHERE        (AssignmentID = N'16777366') AND (PolicyModelID = N'16838528') AND (StatusType = 3) AND (AppStatusType = 3)

-- Unknown
SELECT        AssignmentID, AssignmentUniqueID, Category, CI_ID, CollectionID, CollectionName, 
DeploymentIntent, PolicyModelID, SoftwareName, StartTime, Total
FROM            dbo.fn_CIDeploymentUnknownStatus(1033) AS SMS_CIDeploymentUnknownStatus
WHERE        (AssignmentID = N'16777366') AND (PolicyModelID = N'16838528') AND (CollectionID = N'PS10001F')

You can get the same information in SCCM console: \Monitoring\Overview\Deployments\

Get updates deployment status from SCCM site database

Sometimes, you may need to retrieve updates deployment status from the site database. Here are some example queries that can helpful to you in some scenarios.

Get deployment status of updates in a deployment

-- Replace {873BFE1F-C688-4E61-9447-1E943ECFEF05} with the actual deployment ID
declare @DEPLOYMENTID nvarchar(max) = '{873BFE1F-C688-4E61-9447-1E943ECFEF05}'

select
vRS.name0 as ComputerName,
vUPI.Title as UpdateTitle,
vUPI.ArticleID,
vUPI.CI_UniqueID as UpdateID,
vCIA.CollectionID,
vCIA.AssignmentName as DeploymentName,
@DEPLOYMENTID as DeploymentID,
vCIA.AssignmentID,
vCIA.AssignmentName,
vCIA.CollectionName,
vSN.StateName as LastEnforcementState,
vASC.StateTime
from v_CIAssignment vCIA
join v_CIAssignmentToCI vCIA2CI ON vCIA2CI.AssignmentID = vCIA.AssignmentID
join v_UpdateInfo vUPI ON vUPI.CI_ID = vCIA2CI.CI_ID
join v_AssignmentState_Combined vASC on vCIA.AssignmentID=vASC.AssignmentID
join v_StateNames vSN on vASC.StateType = vSN.TopicType and vSN.StateID=isnull(vASC.StateID,0)
join v_R_System vrs on vRS.ResourceID=vASC.ResourceID
where vCIA.Assignment_UniqueID = @DEPLOYMENTID
order by ComputerName

Get deployment status of a specific update in a deployment

-- Replace 16777217 with an UserID from v_users that has full administrator rights
declare @UserSIDs nvarchar(max) = 16777217
-- Replace {873BFE1F-C688-4E61-9447-1E943ECFEF05} with the actual deployment ID
declare @DEPLOYMENTID nvarchar(max) = '{873BFE1F-C688-4E61-9447-1E943ECFEF05}'
-- Replace b3fb7000-1397-452f-81d3-4e9515450bfc with the actual deployment ID
declare @UPDATEID nvarchar(max) = 'b3fb7000-1397-452f-81d3-4e9515450bfc'
declare @UPDATETITLE nvarchar(max) select @UPDATETITLE=TITLE from v_UpdateInfo where CI_UniqueID = @UPDATEID
declare @ASSIGNID int = (select AssignmentID from fn_rbac_CIAssignment(@UserSIDs) where Assignment_UniqueID = @DEPLOYMENTID)
declare @LOCALUPDATEID int = (select CI_ID from fn_rbac_UpdateCIs(@UserSIDs) where CI_UniqueID=@UPDATEID)

select
uc.ResourceID,
m.Name0 as ComputerName0,
m.User_Domain0+'\'+m.User_Name0 as LastLoggedOnUser,
asite.SMS_Assigned_Sites0 as AssignedSite,
m.Client_Version0 as ClientVersion,
sn.StateName as Status,
isnull(uc.EnforcementSource, 0) as EnforcementState,
nullif(uc.LastEnforcementStatusMsgID&0x0000FFFF, 0) as ErrorStatusID,
nullif(uc.LastErrorCode, 0) as LastErrorCode,
@UPDATETITLE as UpdateTitle,
@UPDATEID as UniqueUpdateID,
uc.StateType*10000+uc.StateID as StateID,
statusinfo.MessageName as ErrorStatusName
from v_UpdateState_Combined uc
join v_CIAssignmentTargetedMachines ast on ast.ResourceID=uc.ResourceID and ast.AssignmentID=@ASSIGNID
join fn_rbac_CIAssignmentToCI(@UserSIDs) aci on aci.CI_ID = uc.CI_ID and aci.AssignmentID = @ASSIGNID
left join fn_rbac_StateNames(@UserSIDs) sn on sn.TopicType = uc.StateType and sn.StateID = uc.StateID
join fn_rbac_R_System(@UserSIDs) m on m.ResourceID=uc.ResourceID and isnull(m.Obsolete0,0)=0
left join v_RA_System_SMSAssignedSites asite on m.ResourceID = asite.ResourceID
left join fn_rbac_AdvertisementStatusInformation(@UserSIDs) statusinfo on statusinfo.MessageID=nullif(uc.LastEnforcementStatusMsgID&0x0000FFFF, 0)
where uc.CI_ID=@LOCALUPDATEID
order by m.Name0

Alternatively, use the following one. Pick the latest entry order by StateTime column in descending order.

-- Replace 16836015 with the actual CI_ID value
select * from v_UpdateState_Combined as vUPC join SR_StateNames as SRS
on vUPC.StateType = SRS.TopicType and vUPC.StateID = SRS.StateID
where CI_ID = 16836015;

CI_ID can be retrieved by  running the following in CM PowerShell (Connect via Windows PowerShell from sccm console)-

Get-CMSoftwareUpdate -Name "2020-01 Update for Windows 10 Version 1909 for x64-based Systems (KB4497165)" | Select-Object -Property CI_ID