SCCM, Collections and Applications

After you have deployed some apps to your computers, you would probably end up with having to upgrade some of your apps.

Maybe you use scripts to do a lot of the work, but I like to distribute an application only to those that needs it.

I will now show you how I build a collection in SCCM with computers that has an application with the wrong version or not the program installed at all.

I will create a collection for Adobe Flash Player ActiveX 11.5.502.146 so it contains both the computers with older versions and those that doesn’t have it installed at all. I will limit it to Windows 7 computers.

My collection will have two queries.

Some of you might think this is easy and have a collection with one query containing this question:

SELECT * FROM …. WHERE Add_Remove_Programs.DisplayName like “Adobe Flash Player%ActiveX” and Add_Remove_Programs.Version < “11.5.502.146”

That query will not be a correct query. A query will compare each number, for example. If you compare 12.x with 9.x, that means you compare number 2 and number 9 (2<9). In this case number 9 is bigger than number 2. This query will not have members that doesn’t have the application installed at all.

So to build a correct query for version 11.5.502.146 I will write this query first:

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.Version like “[0-9].%” or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like “10.%” or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like “11.[0-4].%” or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like “11.5.[0-4]%” or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like “11.5.50[0-1].%”or SMS_G_System_ADD_REMOVE_PROGRAMS.Version like “11.5.502.1[0-3]%”) and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Adobe Flash Player%ActiveX” and SMS_R_System.Active = 1 and SMS_R_SYSTEM.OperatingSystemNameandVersion = “Microsoft Windows NT Workstation 6.1″

This query will find all computers with older versions of the app, all version from:

  • 0.% to 10.%
  • 11.0% to 11.4&
  • 11.5.0% to 11.5.4%
  • 11.5.500.% to 11.5.501.%
  • 11.5.502.10% to 11.5.502.13%

The second query I will make is to find all Windows 7 computers that doesn’t have the Adobe Flash Player ActiveX installed at all.

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 inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.Name not in

(select distinct  SMS_R_SYSTEM.Name 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 “Adobe Flash Player%ActiveX” ) and SMS_R_SYSTEM.Active = “1” and  SMS_R_SYSTEM.OperatingSystemNameandVersion = “Microsoft Windows NT Workstation 6.1″

This query finds all computers with Adobe Flash Player ActiveX installed and because of the “Where SMS_R_System.Name not in”, you will get the computers not having it installed.

The queries I have here is WQL, so you can use them in SCCM.

I hope I made it a little bit clearer on how to build a correct collection in SCCM.

This entry was posted in SCCM, System Center and tagged , . Bookmark the permalink.
Add Comment Register



Leave a Reply

Your email address will not be published. Required fields are marked *

*


− six = 3

* Copy This Password *

* Type Or Paste Password Here *

8,630 Spam Comments Blocked so far by Spam Free Wordpress

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>