Collection to check if any of Primary or Secondary site not installed with SUP Role or any other Role?
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemRoles like "SMS Site System" and SMS_R_System.ResourceId not in (select ResourceID from SMS_R_System where SMS_R_System.SystemRoles like "SMS Software Update Point")
Collection can be modified to check if any of the Roles is missing on sites.