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…

collection to get all 32 Bit and 64 Bit servers.   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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where (SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC" or SMS_G_System_COMPUTER_SYSTEM.SystemType = "X86-based PC") and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server%" Reference Via http://social.technet.microsoft.com/Forums/en-US/configmgradminconsole/thread/ee47ec21-b113-4aed-956d-36205b619091

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 =…

The collection listed below might help you to identify if any computers in organization that do not follow the standard naming convention . Below collection is created to list servers and do not contain specific words in computer name: select SMS_R_System.Name, SMS_R_System.OperatingSystemNameandVersion from  SMS_R_System where (SMS_R_System.OperatingSystemNameandVersion like "%Server 6.%" or SMS_R_System.OperatingSystemNameandVersion like "%Server 5.%") and…
SCCM collection Subselected Queries

Have seen lot of questions on how to get list of computers that do not have xxxxxx .This xxxx could be of anything like softwares,file names anything that do not have. In this post,I will go through step by step procedure how to make it simple. Step 1: To get list of computers that do…