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…

Today I was looking at Packages to see how many packages are created without any distribution Points added. This might help you in identifying whether these packages are really needed to be in SCCM or archive them. This below report will help you in achieving this. select PackageID,Name,Version,SourceDate from v_Package where PackageID not in (select…

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 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%"…

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…