Collection with client Computers without ConfigMgr client

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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.Client = 0 and SMS_G_System_OPERATING_SYSTEM.Caption not like "%Server%" or SMS_R_System.Client is null  and SMS_G_System_OPERATING_SYSTEM.Caption not like "%Server%"

Collection based on computer model

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 where SMS_G_System_COMPUTER_SYSTEM.Model like "%HP ProBook 4710s%"

Create collection with all Windows XP SP 1-2-3 systems

To create a collection with all Windows XP SP1 systems, use the query bellow: 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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 1" Similarly, to create a collection with all Windows XP SP2 systems, just change the Service Pack version number: select … Continue reading Create collection with all Windows XP SP 1-2-3 systems

Create a collection with all mobile (laptops, notebooks, portable) systems

Someone just asked how to create a collection with all laptop computers? Well, if there is a good naming convention on site, this query will be easy to make. But, otherwise, we need to use chassis type value because, for example, laptops and notebooks are not the same things according to Microsoft. The full list … Continue reading Create a collection with all mobile (laptops, notebooks, portable) systems

Create a collection with systems without Adobe Reader 9

Recently, I needed a collection with all computers that do not have Adobe Reader 9 installed. I created a collection with this query: SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "%Adobe Reader 9%". Everything was fine except the fact that my computer was also listed, despite the fact that I have Adobe Reader 9 installed. Well, I’m not an … Continue reading Create a collection with systems without Adobe Reader 9