SQL Query to report SCCM client version and install status

Put this together to generate a list of our SCCM clients and report on their install version and status:


select distinct SYS.Name0 as 'Machine Name', SYS.User_Name0 as 'Login ID', SYS.User_Domain0 as 'Domain', USR.Full_User_Name0 as 'Full Name', case when SYS.Client0 = 1 then 'Yes' when SYS.Client0 = 0 then 'No' else convert(varchar(2), SYS.Client0) end as 'Client Installed', SYS.Client_Version0 as 'Client Version' from v_R_System SYS join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID join v_Collection CN on FCM.CollectionID = CN.CollectionID join v_R_User USR on SYS.User_Name0 = USR.User_Name0 where CN.Name = 'All Systems'

WQL: SCCM client version and SCCM 2012 console

I had to put these two queries together to help me upgrade my client machines after the SP1 update. Here's one to help you with determining your client versions:

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

You can use this query to create multiple collections, just edit it to fit your needs (ie change the version number or substitute the desired operators "<, =, or >" to fit your needs).

Here's another for systems with the SCCM 2012 console installed (that required an update as well):

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%System Center 2012 Configuration Manager Console%"

Hope they are useful to someone!

Using PowerShell to build a GPO-based WMI filter for the SCCM Client install

We recently upgraded our SCCM 2012 server to SP1. I am now in the process of upgrading all the SCCM clients to the latest version (5.00.7804.1000). I use Group Policy to push the client and I wanted to add a WMI filter to the policy to filter out any systems that already have the correct client version. To do that I needed to know where to look for the client version in WMI on a local machine. PowerShell came to my rescue with this command:

Get-WmiObject -namespace root\ccm -class sms_client

Here's a screenshot of the output I got from an up-to-date computer (note the version number):

Using that I was able to construct the following query for my WMI filter:

select * from SMS_Client where ClientVersion < "5.00.7804.1000"

I applied the filter to our SCCM client install policy and now it only runs on machines running client versions older than the one specified. Nice!

BONUS TIP:
There are several ways to do the GPO-based install, I chose to go with running ccmsetup.exe as a startup script in Group Policy Management at the following location:

"Computer Configuration -> Policies -> Windows Settings -> Scripts (Startup/Shutdown)"

I set it up to run the following install string:

ccmsetup.exe /MP:<MySCCMServerName> SMSSITECODE=AUTO

SCCM WQL Query: Windows 8 and Server 2012 Systems

This weekend we'll be upgrading our SCCM 2012 server to SCCM 2012 SP1. In addition to a number of new features this update also adds support for Windows 8 and Server 2012 systems. To support these new OSes I've created new WQL queries to build collections for these operating systems. While I was doing that I took the opportunity to just clean up all my OS version collection queries. I came up with a system that (I feel) works for just about any OS version-specific deployment scenario you might face. It involves the use of 3 core queries that you just "tweak" to fit your needs. Here they are:

Basic "OS Type" Query (substitute "%Server%" for "%Workstation% for Server OSes):

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.OperatingSystemNameandVersion like "%Workstation%"

OS version "LIKE" query (Further refines the above query by OS version):

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.OperatingSystemNameandVersion like "%Server%" and SMS_R_System.OperatingSystemNameandVersion like "%6.2%"

OS version "NOT LIKE" query (Returns the opposite of the above query):

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.OperatingSystemNameandVersion like "%Server%" and SMS_R_System.OperatingSystemNameandVersion not like "%6.2%"

To to use these queries to create a collection for any OS version you need simply substitute the desired OS version number in the above code. For example:

• Use "%5.0%" for Windows 2000
• Use "%5.1%" for Windows XP
• Use "%5.2%" for Windows 2003
• Use "%6.0%" for Windows Vista-2008 Non-R2
• Use "%6.1%" for Windows 7-2008 R2
• Use "%6.2%" for Windows 8-2012

Using these three core queries I put together a fantastic set of OS Type and Version collections that (so far) have suited practically all my OS-specific deployment needs:

Cosmetic changes to code snippets on the site

I made some changes to the CSS code I use for code snippets on the site. Instead of the darker look with the blue dotted line I opted for the appearance of a simple text editor. I also added an effect that (in most browsers) should produce a soft highlight effect on mouse over. I think the new look is much cleaner and if I ever decided to change color schemes or templates the use of neutral colors should make the transition much easier. I also fixed a word wrap bug that would (in some cases) cause the snippet text to overflow outside the block container. I hope the new look is more intuitive.

Books: The Robot and Foundation Series by Isaac Asimov

I'm a big fan of the late Isaac Asimov's science fiction. I recently managed to cross off a "bucket list" item in that I read all the novels in his Foundation series. I found them all very very good and (in many ways) a bit eerily reminiscent of what we see happening today with the Internet, data mining and how search engines are getting really good at predicting what we will look for online. Who knows, maybe his concept of Psychohistory is something we'll see become a reality in our lifetimes. Anyway, I made one mistake (in my opinion) in that I read the books in the order they were written as opposed to their chronological order. I did not realize that two books were prequels and two books were epilogues that actually wrote much later in life to bookend the core trilogy. If I had to do it all over again I'd have read them in the chronological order of the storyline and (I think) events depicted would have been easier for me to follow.

Also its worth mentioning that (if you really want to get the most out of these books) you should probably start by reading several books from his Robot Series. The reason for this is that later in his life Asimov began to include characters and places from the Robot Novels in the Foundation Novels effectively placing the events of those books in the same fictional universe. So if I had to do it all over again these are the books I'd read and the order I would read them in:

The Robot Series (Reading Order):

1. The Complete Robot
2. The Caves of Steel
3. The Naked Sun
4. The Robots of Dawn
5. Robots and Empire

The Foundation Series (Reading Order):

1. Prelude to Foundation
2. Forward the Foundation
3. Foundation
4. Foundation and Empire
5. Second Foundation
6. Foundation's Edge
7. Foundation and Earth

Oh and if you are a completionist (like me) you'll also want to read his Galactic Empire Series. It consists of three standalone but loosely connected stories that take place in the time period between the Robot Series and the Foundation series. Not really required to follow the Robot and Foundation series, but if you enjoy Asimov's writing they are a great reads because they only serve to expand upon the intriguing universe authored by Asimov:   

Galactic Empire Series:

1. The Stars, Like Dust
2. The Currents of Space
3. Pebble in the Sky

Be aware that some of these books may be out of print but (at the time this article was proste) all were available via Asimov's author page at Amazon:

http://www.amazon.com/Isaac-Asimov/e/B003RY2ISS/

Good luck finding copies and I hope you enjoy them as much as I did!

Experimenting with Labels on the site

If you check below the search field you should see that I enabled the "label cloud" feature. I'm still toying with label categories, but (hopefully) this will make it easier for people to jump to the specific articles they are looking for on the site without having to search or scroll for days.

SCCM SQL Query: Multiple program versions in Add/Remove (with Publisher and full Username)

Management needed me to put together a SQL report for multiple versions of the same application. This particular case proved tricky because the logic involved including an "or" for the application versions and an "and" for the publisher in the "where" section. This query did the trick for me:

select distinct SYS.Name0 as 'Machine Name', SYS.User_Name0 as 'Login ID', SYS.User_Domain0 as 'Domain', USR.Full_User_Name0 as 'Full Name', ARP.DisplayName0 as 'Product Name', ARP.Publisher0 as 'Publisher', ARP.Version0 as 'Version' from v_R_System SYS join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID join v_Collection COL on FCM.CollectionID = COL.CollectionID join v_R_User USR on SYS.User_Name0 = USR.User_Name0 join v_Add_Remove_Programs ARP on SYS.ResourceID = ARP.ResourceID where COL.Name = 'All Systems' and ARP.Publisher0 = 'Solidworks Corporation' and ( ARP.DisplayName0 like 'SolidWorks 2010%' or ARP.DisplayName0 like 'SolidWorks 2011%' or ARP.DisplayName0 like 'SolidWorks 2012%' )
Hope you find it useful.

MDT: Disable Automatic Updates at initial deploy with Unattend.xml

Windows 7 has a tencancy to automatically install a few updates right after a fresh

install (regardless of its default settings). You can suppress this behavior by 

editing the value specified for the variable <ProtectYourPC> in the unattend.xml for 

Windows 7 task sequences for Windows 7 deplyoments. Here are the possible values:

1 - Specifies the recommended level of protection for your computer.

2 - Specifies that only updates are installed.

3 - Specifies that automatic protection is disabled.

NOTE: Option "3" is usually the best choice if you already have an internal WSUS.

MDT: Change the Windows product key quickly with Unattend.xml

Here's a quick and easy way to change the product key for an OS in MDT:

1. Use Notepad++ or some other advanced text editor to open the following:
   "<MDTSharePath>\control\<TaskSequenceId>\unattend.xml"
2. Locate your product key under the "<ProductKey></ProductKey>" entry. 
3. Replace the key under that entry with your updated product key and save the file.

MDT: Important FYI regarding the new GPO Pack feature

Its been covered at other sites, but I recently ran afoul of this so I'll cover it here. By default in MDT 2012 you may find Windows Firewall settings disabled by group policy after deploying certain OSes. Firewall settings will be greyed out and the following message is displayed in the Windows Firewall interface after deployment:

"For your security some settings are managed by your system administrator"

The computers will not have been domain-joined yet and you will not have set up any manual tasks to apply local policies. So what is the culprit? Actually  it’s a new feature in MDT 2012! You can add the following to Customsettings.ini to disable it:

ApplyGPOPack=NO

This should resolve the issue. Unfortunately Microsoft did not just add the new feature to apply local polices during a Task Sequence. They also added four GPO Pack templates which get applied *by default* during a deployment! You can find these templates in your Deployment Share folder in the subfolder "\Templates\GPOPacks". As of the day these notes were written the current templates are as follows:

<MDTShare>\Templates\GPOPacks\
|
|_\Win7SP1-MDTGPOPack
|
|_\WinVistaSP2-MDTGPOPack
|
|_\WS2008R2SP1-MDTGPOPack
|
|_\WS2008SP2-MDTGPOPack