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
