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…

Creating collection to get computers that starts and end with particular string is used mostly using % .If this percentile used at the end of the variable name, you get all computer names that starts from particular string and if you use this in the beginning, gets all computers that ends with particular string. Examples…

  Full Details :http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/7b4d3810-a262-4c63-8dd6-7cc906b85941 select sys.name0,sys.AD_Site_Name0,FCM.SiteCode,ARP.DisplayName0,ARP.Version0,ARP.InstallDate0 from V_R_system sys inner join v_Add_Remove_Programs ARP on arp.resourceID=sys.ResourceID inner join v_FullCollectionMembership FCM on FCM.ResourceID=sys.ResourceID and arp.DisplayName0 like '%project reader%' and ARP.Version0 < '4%' For collection ,follow the post here on http://eskonr.com/2012/05/sccm-collection-computers-with-lower-version-application/

Full Details http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/7b4d3810-a262-4c63-8dd6-7cc906b85941 This collection is created using sub selected queries http://eskonr.com/2011/12/sccm-collection-sub-selected-quiries/ 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_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where (SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%project reader%"…

earlier I posted entry to list all virtual computers http://eskonr.com/2011/04/sccm-collection-for-virtual-or-physical-computers/ this collection gives you all computers that are Physical (running as Host) not Virtual… 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.ResourceId not in (select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Model like "%Virtual%") and SMS_R_System.Client is…

SCCM collection that returns all windows XP or Windows 7 machines which has 1 GB of RAM(1024*1024 KB) running with MS office products. select *  from  SMS_R_System inner join SMS_G_System_X86_PC_MEMORY on SMS_G_System_X86_PC_MEMORY.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory = 1048576 and (SMS_G_System_OPERATING_SYSTEM.Caption =…