System Management

For the one who searched for these…

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 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 2"

And the same for all Windows XP SP3 systems:

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 3"

As I have some free time, I’m testing different things with SCCM, mainly in creating different collections that I might need. Bellow you can find the query that will show you all x64 systems, client and server operating systems.

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.SystemType = "x64-based PC"

You can further customize this so it will show you only x64 servers or only x64 Vista systems etc.

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 even for me But, otherwise, we need to use chassis type value because laptops and notebooks are not the same things.

The full list with chassis types are bellow (taken from http://www.microsoft.com/technet/scriptcenter/guide/sas_cpm_btnz.mspx?mfr=true):

So, to make a collection with all kind of mobile systems (Portable, Laptop, Notebook, Sub Notebook) we will use 8, 9, 10 and 14 chassis types. The final query looks like this:

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_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("8", "9", "10", "14")

If you receive the “This query has a syntax error.” error, change the quotation marks when pasting the query, as this one does not work with ConfigMgr.

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 SQL geek but I know that SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Adobe Reader 9%" should return all computers with Adobe Reader 9 installed. This one was ok – the collection listed only me. Something was wrong with my first query so I asked for help our SQL guys and in a few minutes I had the right query

Also, I didn’t want to install the Reader on server computers with SCCM client. So the final query looked like this:

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_R_System.OperatingSystemNameandVersion not like "%Server%" and SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Adobe Reader 9%")

I’m sure there are more ways to do this, but this is the query that worked for me.