SCCM SQL Query to get Last State computers in Software Update deployment

Question

Hello,

Computers list to be extracted for troubleshooting purpose for the below each Last state in the default report
Software Updates - C Deployment States > States 1 - Enforcement states for a deployment

Enforcement state unknown
Installing update(s)
Downloading update(s)
Downloaded update(s)
Pending system restart
Failed to install update(s)

We have deployment for PrePilot, Pilot, Production. I need to select each of the above Last State for each deployment. Then export the computers and collate in a excel file to start work on troubleshooting

Is there any SCCM SQL query to run and get the list of computers in all the last state for each deployment ?
Please share the query if it exits.

Answer 1

Hi, @Boopathi S
Thank you for posting in Microsoft Q&A forum.

Please try below query, replace the AssignmentID with your Deployment ID.

select  
sn.StateName as LastEnforcementState,  
vrs.name0 as ComputerName,  
a.AssignmentName as DeploymentName,  
assc.StateTime ,  
a.CollectionName  
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='xxxxxxxx'  
order by LastEnforcementState  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "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.

---