This is nothing new; I’m typically not one to repost information that can be found elsewhere online. This is just so that I have an easy place to find it in the future!
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
inner join SMS_CM_RES_COLL_SMS00001
on SMS_CM_RES_COLL_SMS00001.ResourceId = SMS_R_System.ResourceId
where SMS_CM_RES_COLL_SMS00001.IsBlocked = ‘1’
Replace IsBlocked with IsApproved to query for approved clients.