ConfigMgr–Creating A Collection For All Systems Not Approved
From the archive (2011). Written for the tools of the time, so some details may be out of date.
Saw this past by on the MSSMS list today and thought it was good info. Thanks to Brandon Linton for contributing it.
Collection query:
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 inner join SMS_CM_RES_COLL_SMS00001 on SMS_CM_RES_COLL_SMS00001.ResourceId = SMS_R_System.ResourceId
where SMS_CM_RES_COLL_SMS00001.IsApproved<>‘1’