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
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%”
Upgrade Configuration Manager client
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
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
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:
- 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%'
- 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.
- 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
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
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.