Collection

Collection with client Computers without ConfigMgr client

~ Vitalie Ciobanu

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

~ Vitalie Ciobanu

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%”

Upgrade Configuration Manager client

~ Vitalie Ciobanu

Recently I reinstalled ConfigMgr server in my company and was looking for an easy method to upgrade all Configuration Manager clients to 4.00.6487.2000 version.
Put some notes on the table and finally made the following plan:

  • Create a report that counts all client versions. (This is optional, just for informational purposes).
    Report query is:SELECT TOP (100) PERCENT Client_Version0 AS [ConfigMgr client version], COUNT(Client_Version0) AS Total
    FROM dbo.v_R_System GROUP BY Client_Version0, Client0 HAVING (Client0 = 1)
    ORDER BY Total DESC, [ConfigMgr client version]
  • Create a collection (“Older Clients” for example) with all system resources with a client version not 4.00.6487.2000.
    Collection query is: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.ClientVersion != "4.00.6487.2000"

    This way, system resources with older client version will be members of this collection.

  • Created a package and program with ConfigMgr client upgrade with custom command line parameters.
    Program command line is:CCMSETUP.EXE /noservice SMSSITECODE=CFM SMSCACHESIZE=1024 SMSSLP=BLAH.DOMAIN.COM SMSMP=BLAH.DOMAIN.COM RESETKEYINFORMATION=TRUE
  • Advertised it to “Older Clients” collection.

Now, as system resources with older client version are members of this collection they will receive the advertisement and will silently install the latest ConfigMgr client.

When the collection will have no system resources, I will know that all clients are upgraded. Also, I can check this by opening the same report from any browser on any computer.

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

~ Vitalie Ciobanu

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"

Create an advertisement only for Windows 7 systems

~ Vitalie Ciobanu

Let’s suppose you create a package with one program and want to advertise it only to a few selected platforms, including x86 and x64 Windows 7 systems. Because Windows 7 is not supported yet with Configuration Manager 2007 SP1, you cannot choose it from the supplied list of platforms. As a result of this, after receiving and downloading the package, the system will give you the following message:
Program rejected (wrong platform). Advertisement "XYZ12345" from site "XYZ" was rejected because the client's platform is not supported.

It is expected that SP2 will add support for Windows 7, but until then we can use the following workaround:

  1. Create a collection with all Windows 7 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 where OperatingSystemNameandVersion like '%Workstation 6.1%'
  2. Create a new program (for example Program for Win7) for the same package and select “This program can run on any platform” as a requirement.
  3. Create a new advertisement and use “Program for Win7” program. Advertise it to Windows 7 collection created earlier.

That’s it. This may not be the best workaround, but it worked very well for me, at least until SP2 will RTM and I can install it in production.

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

~ Vitalie Ciobanu

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 with chassis types are bellow (taken from http://www.microsoft.com/technet/scriptcenter/guide/sas_cpm_btnz.mspx?mfr=true):

Value Description
1 Other
2 Unknown
3 Desktop
4 Low Profile Desktop
5 Pizza Box
6 Mini Tower
7 Tower
8 Portable
9 Laptop
10 Notebook
11 Hand Held
12 Docking Station
13 All in One
14 Sub Notebook
15 Space-Saving
16 Lunch Box
17 Main System Chassis
18 Expansion Chassis
19 Sub Chassis
20 Bus Expansion Chassis
21 Peripheral Chassis
22 Storage Chassis
23 Rack Mount Chassis
24 Sealed-Case PC

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.

Create a collection with systems without Adobe Reader 9

~ Vitalie Ciobanu

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.