How to create a collection of all approved clients

This collection will allow you to find machines that need approval.


For Is Approved
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'

Replace this section for not approved and unknown:
For not approved

SMS_CM_RES_COLL_SMS00001.IsApproved= '0'

For Unknown
SMS_CM_RES_COLL_SMS00001.IsApproved= '2'

I did not write this collection, I will be glade to give credit to whom ever it was :-)

How to create a collection of all non client systems

Here is a query that helps create a collection to list all the non SMS/SCCM clients.


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.Client is null OR SMS_R_System.Client = 0