The Realm of the Verbal Processor

Application Replacement (4 of 5)

This is the fourth of a five part series on utilizing the MDT integration into Configuration Manager to improve your Operating System Deployment functionality. These processes will make your OSD setup much more dynamic. The series will be:

1. Assumptions and creating the MDT database
2. Dynamic OSD using the MDT Database
3. Application Replacement #1…this post is the reason I started the series. Modifying the RetrievePackages stored procedure.
4. Application Replacement #2. Populating the PackageMapping table.
5. OSD and the MDT Database…connecting all the dots from the previous four posts. Setting up a task sequence to use the MDT database.

In the previous post we modified the SQL stored procedure to make Package Mapping work for both a Refresh and Replace scenario. However neither scenario will work until we populate the PackageMapping table in the MDT database.

Populating the PackageMapping Table

The PackageMapping table has two columns: ARPName and Packages. Each entry in the table creates a correlation between a piece of installed software (ARPName) and a Configuration Manager Package/Program (Packages).

The values in the ARPName column come from the values in the Uninstall registry key. (HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall OR HKLM\Software\WOW6432Node\Microsoft\Windows\CurrentVersion\Uninstall). MSI installs will be GUIDs. Non-MSI installs will be other names…not necessarily the DisplayName from Add/Remove Programs. The value in this field corresponds with the SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID field in the Configuration Manager database which is gathered by Hardware Inventory.

The Packages column contains the PackageID and Program Name for a Configuration Manager Package. The proper format for this column is: “XYZ00000:Program Name” where XYZ00000 is the Package ID, and “Program Name” is the exact name of the Configuration Manager Program Name in the package. Of particular importance, the values in the Packages column are case sensitive.

We need to do four things in order to populate this table:

1. Obtain the ARPNames that we care about
2. Obtain the Package:Program combinations that we will correlate to the ARPNames
3. Correlate which ARPNames should install which Package:Program.
4. Add the ARPName = Package:Program to the table

Obtain the ARPNames

A simple query can be used to obtain the values necessary to populate the ARPNames column in the PackageMapping table. Create this query in the Configuration Manager console and then export the results and view it in Excel. The query will show the DisplayName and ProdID fields for inventoried software. The ProdID field is what will need to be injected into the ARPName column of the PackageMapping table. (Obviously only for the values you care about.)

Select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId order by SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName

Obtain the Package:Program

Another simple query can be used to obtain the Package:Program info. This time however we are going to run the query in SQL Management Studio. I know there must be a way to write this query to work as a query in the Configuration Manager console, but I still get confused converting SQL to WQL…so you get the SQL. Thanks to John Nelson for posting a similar query on his blog that pointed me in the right direction! The query to use is:

Select PackageID,ProgramName from v_Program Where (0x00000001 & ProgramFlags)/ 0x00000001 = 1

To do run the query…

1. Open SQL Management Studio and select the Configuration Manager database
2. Create a New Query
3. Paste the query below into the query pane
4. Click Execute

This will produce results that look something like the following. You can simply right click anywhere in the results to have the option to save the results as a CSV file.

Open the CSV file in Excel and you can easily create the Package/Program combination that you need to import with a Concatenate formula. That formula would look like this as long as the PackageID/ProgramName are in columns A and B in the spreadsheet:

=CONCATENATE(A1,”:”,B1)

Correlate ARPNames to Package:Program

Now that we have the data we need, we need to determine which ARPNames will install which Package:Program. This is a manual process that involves:

1. Looking through the list of ARPNames and determining which ones you actually care about. This will be the programs that you want to either install or replace. Guaranteed you will not care about the majority of what is in the list. But for example if you want to reinstall Visio 2010, you will note that one. If you want to replace Visio 2007 with Visio 2010, you will take note of that one as well.
2. Match each ARPName that you care about to a corresponding Package:Program. This will potentially result in multiple ARPNames matched to a single Package:Program combination. For example:

Add data to the PackageMapping Table

Now that we have the data we need, there are multiple methods for populating the the PackageMapping table. I will show you two ways…

Method 1 (non-preferred): Manual Entry via SQL Query

Entries can be added to the PackageMapping table via a query similar to the following executed in SQL Management Studio. This is an actual example for the application XML Notepad. Note that the GUID does not include the curly braces when injected into the PackageMapping table.

use [MDTdb]

go

INSERT INTO [PackageMapping] (ARPName, Packages) VALUES(‘FC7BACF0-1FFA-4605-B3B4-A66AB382752D’, ‘PRI00017:Install XML Notepad Silently’)

go

Method 2 (Preferred): Maik Koster’s Web Front End

Maik Koster (Microsoft MVP) has posted an easy to set up web front end for the MDT database. In addition to being able to enter the configuration items that are possible to configure via the MDT Deployment Workbench, the web front end also enables adding and removing entries to the PackageMapping table. This method provides the easiest ongoing maintenance of the PackageMapping table, although care should be taken to protect access to the website. Maik’s web front end and documentation on installation and configuration can be found on the MDT Web Front End page on Codeplex.com (http://mdtwebfrontend.codeplex.com/)

An advantage to using the web front end method is that only the ARPName must be entered manually. (!!!) The web front end queries the Configuration Manager database and enters the correctly syntaxed and spelled value into the Packages column.

Testing Package Mapping

Once you have the PackageMapping table populated, the following query can be executed against the MDT database to determine which applications would be mapped for a given MAC address. Replace “MDTdb” with the name of your MDT database. Replace 00:00:00:00:0:00 with the MAC address of a computer that has inventory data in the Configuration Manager database. Note that the query is executed against the MDT database. The RetrievePackages Stored Procedure in the MDT database queries the Configuration Manager database for the inventory data.

use [MDTdb]

go

EXECUTE RetrievePackages ’00:00:00:00:0:00′

go

Summary:

This post showed how to obtain the information needed to populate the PackageMapping table in the MDT database and gave two options for how to inject that information into the database. The next (and final) post in the series will show how to tie this all together so you can use it in your environment.

April 11, 2012 - Posted by Jarvis | ConfigMgr, MDT 2010