I need to build a collection of machines that are struggling to get patches installed successfully. We would like for the collection to be as accurate as possible with the following logic:

1) failed patch status

2) failed patch is not a patch that has not been superseded

3) patch is actively deployed

4) exclude any machine that has a reboot pending status for any patch

We do have a collection built using the logic below but feel like this is not specific enough.

(select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from SMS_R_System as SYS Inner Join SMS_SUMDeploymentAssetDetails as SUM on SYS.ResourceID = SUM.ResourceID WHERE (sum.statustype = 5) and SUM.LastEnforcementErrorCode <> 0)

Any direction you can provide on either a sql query to gather this information or better yet actual collection logic code would greatly be appreciated.

Thanks,

Jason

​I need to build a collection of machines that are struggling to get patches installed successfully. We would like for the collection to be as accurate as possible with the following logic:1) failed patch status2) failed patch is not a patch that has not been superseded3) patch is actively deployed4) exclude any machine that has a reboot pending status for any patch We do have a collection built using the logic below but feel like this is not specific enough.(select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from SMS_R_System as SYS Inner Join SMS_SUMDeploymentAssetDetails as SUM on SYS.ResourceID = SUM.ResourceID WHERE (sum.statustype = 5) and SUM.LastEnforcementErrorCode <> 0) Any direction you can provide on either a sql query to gather this information or better yet actual collection logic code would greatly be appreciated. Thanks,Jason  Read More

​