Today I was looking for a way to group my laptops and computers based on Operating System in SCCM. This can be done through query based collections. After some searches I came to the following final query’s which did the job for me:

Computers – Windows XP 

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_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like “%Workstation 5.1%” and SMS_R_System.SystemOUName like “%DOMAIN/OU’s%” and SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes not in (“8″,”9″,”10″,”14”)

Computers – Windows 7

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_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like “%Workstation 6.1%” and SMS_R_System.SystemOUName like “%DOMAIN/OU’s%” and SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes not in (“8″,”9″,”10″,”14”)

Laptops – Windows XP

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_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like “%Workstation 5.1%” and SMS_R_System.SystemOUName like “%DOMAIN/OU’s%” and SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in (“8″,”9″,”10″,”14”)

Laptops – Windows 7

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_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like “%Workstation 6.1%” and SMS_R_System.SystemOUName like “%DOMAIN/OU’s%” and SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in (“8″,”9″,”10″,”14”)