The Realm of the Verbal Processor

Application Replacement (3 of 5)

This is the third 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 first post in this series, we set up the MDT database in our already functional Configuration Manager environment. The second post showed how to populate the MDT database in order to make our OSD process much more dynamic. This post will show the Application Replacement functionality.

Background:

For a while now MDT has included the ability to dynamically replace applications during a computer refresh scenario. (i.e. if the computer being reimaged has Visio installed, dynamically install Visio as part of the reimage) In a MDT only scenario, this is done with the UDI wizard via the Application Discovery pre-flight check. This can also be done using the integration with Configuration Manager…basing the software reinstall on the Configuration Manager inventory. While this functionality has been there for a while, I have found two pieces of it lacking:

1. How to do this is buried in documentation that makes it a bit challenging to implement. (The MDT document that talks about this is a 497 page Word doc…good luck.)
2. The process only works for a computer refresh scenario. It does not work in a computer replace situation…which is fairly common with my clients. They are not upgrading older Windows XP systems to Windows 7…they are replacing the computer. But they still need the user to have access to their current applications after the upgrade.

That is the reason for this blog post. First I want to show how to do this without having to dig through a huge doc. Second, I want to show how to modify this feature to allow for doing application replacement in both the Computer Refresh and the Computer Replace OSD scenarios.

Application Replacement – Behind the Scenes

This functionality is provided by a SQL query of the PackageMapping table in the MDT database during deployment. This table must be populated with information on installed applications and the corresponding Configuration Manager PackageID:ProgramName. In addition to being able to simply perform a 1:1 replacement (i.e. if Visio 2010 is installed, reinstall it.) it can also be used for replacing a dated or non-standard application with a updated or standard application (e.g. if Visio 2007 or Visio 2010 is installed, install Visio 2010. OR if EditPlus is installed, install Notepad++ instead.) Populating this table will be addressed in the fourth post in this series.

The three components that are necessary for this functionality to work are:

• MDT database with the PackageMapping table populated correctly
• SQL Stored Procedure in the MDT database named RetrievePackages
• CustomSettings.ini file configured to connect to the MDT database and execute the RetrievePackages Stored Procedure

Modify the RetrievePackages Stored Procedure

Out of the box, the RetrievePackages stored procedure in the MDT database works for a refresh scenario only. This is because it references the MAC address of the currently running system to query inventory for applications that are currently installed. The original RetrievePackage stored procedure looks something like this:

USE [MDTdb]
GO
/****** Object:  StoredProcedure [dbo].[RetrievePackages]    ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[RetrievePackages] @MacAddress CHAR(17) AS    SET NOCOUNT ON
/* Select and return all the appropriate records based on current inventory */
SELECT * FROM PackageMapping   WHERE ARPName IN
(     SELECT ProdID0 FROM SMS_PRI.dbo.v_GS_ADD_REMOVE_PROGRAMS a, SMS_PRI.dbo.v_GS_NETWORK_ADAPTER n
WHERE a.ResourceID = n.ResourceID AND     MACAddress0 = @MacAddress   )

This Stored Procedure is a subselect query that does the following:

1. Query the Configuration Manager database (SMS_PRI.db0.v_GS_ADD_REMOVE_PROGRAMS and SMS_PRI.dbo.v_GS_NETWORK_ADAPTER) to obtain the Configuration Manager inventory of installed programs for the computer (using MACAddress and ResourceID).
2. Compare the inventoried programs from #1 against the information in the ARPName column of the PackageMapping table.
3. Return all that match both queries (the subselect is essentially a double query…two “Select” statements).

This makes query works fine for a computer Refresh scenario, but we need to modify that to take into account a computer replace scenario. We will do this by injecting a simple If/Then statement into the stored procedure and have it look at different criteria.

First of all, think through what is necessary in a computer replace scenario in Configuration Manager. Three things need to happen:

1. A Computer Association must be set up between the Source (old) computer and the Destination (new) computer. This association is based on MAC address.
2. A State Capture task sequence must be run on the Source computer.
3. The new computer has a task sequence executed that includes a Restore user state section.

The key here is that the first step in setting up a Computer Replace scenario is that a Computer Association MUST exist first. We can use that information to dynamically determine if this is a Refresh or Replace scenario. If a Computer Association exists in the Configuration Manager database, then this is a Replace scenario. If it does not, then we will treat it as a Computer Refresh scenario. Simple enough right? So…let’s modify the Stored Procedure.

Now…I will freely admit that writing SQL queries from scratch is not something that I am extremely strong in, so I figured SOMEONE had to have written this before and posted it. Well…I searched for a good long time and finally came across one (and only one) person that I could find who had rewritten this stored procedure to reference Computer Association data. (Thanks to John Quirk for posting that!) Others may have done so, but they either haven’t posted it or I couldn’t find the post.

This is a good start, but what John’s post did was to make the RetrievePackage SP work for a Replace scenario only…but not for a Refresh. I need it to work for both scenarios. So…let’s combine the original stored procedure with the one from John’s post to accomplish this. (Note: I used the SQL query from John’s blog post when I creating the “Refresh AND Replace” version of the stored procedure below.)

USE [MDTdb]
GO
/****** Object:  StoredProcedure [dbo].[RetrievePackages]     ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RetrievePackages] @MacAddress CHAR(17) AS SET NOCOUNT ON
/* Check to see if a computer association has been created for this MAC address */
IF (select sourceMACAddresses from SMS_PRI.dbo.v_StateMigration where RestoreMACAddresses=@MacAddress) is null
/* Computer association does not exist…must be a computer refresh scenario */
BEGIN
/* Select and return all the appropriate records based on current inventory */
SELECT * FROM PackageMapping   WHERE ARPName IN   ( SELECT ProdID0 FROM SMS_PRI.dbo.v_GS_ADD_REMOVE_PROGRAMS a, SMS_PRI.dbo.v_GS_NETWORK_ADAPTER n     WHERE a.ResourceID = n.ResourceID AND     MACAddress0 = @MacAddress   )
END
else
/* Computer association DOES exist…must be a computer replace scenario */
BEGIN
/* Select and return all the appropriate records based on OLDCOMPUTER inventory */
SELECT * FROM PackageMapping WHERE ARPName IN ( SELECT ProdID0 FROM SMS_PRI.dbo.v_GS_ADD_REMOVE_PROGRAMS a, SMS_PRI.dbo.v_GS_NETWORK_ADAPTER n WHERE a.ResourceID = n.ResourceID AND MACAddress0 = (select sourceMACAddresses from SMS_PRI.dbo.v_statemigration where restoreMACAddresses=@MacAddress) AND n.ResourceID IN (Select ResourceID from SMS_PRI.dbo.v_R_System_Valid))
END

This one is a bit more complex. Here is what it is doing:

1. Grab the MAC address of the current computer(@MacAddress). Use it to query the SMS_PRI.dbo.v_StateMigration to see if a Computer Association has been configured (“is null”).
2. If a Computer Association has not been created (i.e. “is null” is true), then assume this is a Computer Refresh scenario and use the original Stored Procedure query.
3. If a Computer Association HAS been created (i.e. “is null” is false), then this must be a Computer Replace scenario. In this instance, I don’t want to use the MAC address of the current computer (which would be the new computer in a Computer Replace scenario) to query inventory…I want to use the MAC address of the OLD (source) computer. (Remember…we are running this task sequence on the Destination (new) computer.)
4. To accomplish this, we are now doing what I guess you would call a sub-subselect query (three select statements). We are doing the original subselect query, but we are adding an additional select statement to grab the MAC address of the old/source computer (sourceMACAddresses) that has been tied to the new/destination computer (restoreMACAddresses) via the Computer Association(SMS_PRI.dbo.v_statemigration).

This effectively enables us to dynamically add software that was installed on the Source computer to the Destination computer. VERY slick!

In order to make this change to the Stored Procedure in your MDT database, do the following:

1. Download the modified stored procedure from my SkyDrive and open it in a text editor.
2. Replace all instances of “MDTdb” with the name of your MDT database. (Ctrl+H in Notepad)
3. Replace all instances of “SMS_ABC” with the name of your Configuration Manager database.
4. Save this file locally so that the query can be copied/pasted in the next steps.
5. Open SQL Management Studio on the SQL server housing the MDT database.
6. Expand Databases | MDTdb | Programmability | Stored Procedures. Right click “dbo.RetrievePackages” and choose Modify. The Stored Procedure will open in a query window to the right.
7. Select all text in the Query window and delete it.
8. Paste the SQL query customized above into the query window.
9. Click “Execute” to modify the Stored Procedure.

Summary:

This has now modified the SQL Stored Procedure in the MDT database to work in both a Refresh and Replace scenario. However in order for this to work we still need to populate the PackageMapping table in the MDT database. That is the subject of the next post in the series…

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