The following SCCM collection query will retrieve all systems where an advertisement has not completed successfully. In particular, the query identifies the systems where the last status message returned for a specific advertisement (in this case, the advertisement with ID LAB20001) matched a list of known failure codes.
select S.ResourceID,S.ResourceType,S.Name,S.SMSUniqueIdentifier,S.ResourceDomainORWorkgroup,S.Client from SMS_R_System AS s INNER JOIN SMS_ClientAdvertisementStatus AS o ON s.ResourceID = o.ResourceID WHERE AdvertisementID = 'COR21237' AND (LastStatusMessageID in (10001, 10003, 10004, 10006, 10007, 10021, 10025, 10030, 10050, 10051, 10053, 10054, 10057, 10058, 10070))You could modify the query to identify all systems where the last status message did not match a list of known success code, but this would almost certainly have unintended consequences, such as including systems where the installation has not yet completed (or even started).