Check if all the deployments [ Packages / Applications / Software updates] that are targeted to a particular device in SCCM have got executed successfully.

Question

Hi Team,

I am working on creating an automated report to check if all the deployments [ Packages / Applications / Software updates] that are targeted to a particular device in SCCM have got executed successfully.

Could anyone please help on letting if there is any SQL query / Powershell script avaliable to get this required information. It should be fine if we would need to use different scripts to generate the output for each of the deployment types[ Packages / Applications / Software updates]

The sample output might look like below.

Server Deployment Status ( Success / Failed / Pending to install)

Regards

Sathish

Answer 1

Have you looked at the builtin report for this? What are they not providing?

Answer 2

Hi, @Sathish Raj S

Thank you for posting in Microsoft Q&A forum.

We have to use different sql query for Packages / Applications / Software updates.

Here is an example for software updates, replace 'ComputerName' with your device name:

DECLARE @ComputerName nvarchar(30) = 'ComputerName'

Select

Case

when ds.FeatureType = 1 then 'Application'

when ds.FeatureType = 2 then 'Program'

when ds.FeatureType = 5 then  'Software Update'

end as 'FeatureTypeText',

ca.AssignmentName as DeploymentName,

sn.StateName as LastEnforcementState

from v_DeploymentSummary ds

inner join v_CIAssignment ca on ca.AssignmentID=ds.AssignmentID

inner join v_AssignmentState_Combined assc on ca.AssignmentID=assc.AssignmentID

inner 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 ds.CollectionID in

(Select CollectionID from v_fullCollectionMembership fcm where fcm.name = @ComputerName)

and vrs.Name0 = @ComputerName

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.