Have you looked at the builtin report for this? What are they not providing?
Check if all the deployments [ Packages / Applications / Software updates] that are targeted to a particular device in SCCM have got executed successfully.
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)
Sign in to comment
Sort by: Most helpful
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.