One common question regarding the deployment of a new operating system especially on refresh or replacement scenarios is how the user will get his previous applications back. All standard applications are probably already part of your thick image or will be defined as standard applications for all users or a specific location etc. However you will always have a bunch of applications which only a certain amount of users will use. And normally you don't want to spend time after the deployment to get this specific applications back onto the computer. An automated process like MDT shall save time and automate as much as possible.

There are a couple of ways to achieve this. As this posts mainly concentrate on the integration into SCCM I'm going to show a way to do this with the help of a SMS/SCCM Server. The idea behind comes from Microsoft published under Microsoft Deployment Customization Desktop Samples.

Basically we just take the MAC/UUID to get the RessourceID of a particular computer from the SMS/SCCM server, query the SMS/SCCM Database to get the installed software for this RessourceID and then map the previous installed applications against a table of the currently available/preferred applications. So this process gives us a fully dynamic list of applications we can use during the Deployment.

Great stuff and we could use this as it is. However as explained in the last post, I prefer to use roles to assign software which makes it more flexible for us. So we just take the original content and change it to give us a list of roles to be used. OK, let's start. First thing we need is a table in the MDT Database to store the mapping between the previous applications and roles. Using the SQL Management Studio execute the following sql statement to create the necessary table.

CREATE TABLE [dbo].[RoleMapping](
    [ARPName] [nvarchar](255) NULL,
    [RoleID] [int] NULL)

Then we need to create a stored procedure, which will do the whole processing

CREATE PROCEDURE [dbo].[RetrieveRoles]
    @MacAddress CHAR(17)
AS

SET NOCOUNT ON

/* Select and return all the appropriate records based on current inventory */
SELECT DISTINCT i.Role FROM dbo.RoleMapping m INNER JOIN dbo.RoleIdentity i ON m.RoleID = i.ID
WHERE ARPName IN
(
  SELECT ProdID0 FROM SMS_XXX.dbo.v_GS_ADD_REMOVE_PROGRAMS a, SMS_XXX.dbo.v_GS_NETWORK_ADAPTER n
  WHERE a.ResourceID = n.ResourceID AND
  MACAddress0 = @MacAddress

)

Just replace the SMS_XXX with the name of your SMS Database. It will take the MAC of a given computer and return a distinct list of Roles. It assumes, that the MDT database and SMS Database are on the same server. If you have these Databases on different servers you will need to make some modifications to it. To execute this Stored Procedure we simply add two new sections to our customsettings.ini.

[DynamicPackages]
SQLDefault= DB_DynamicRoles

[DB_DynamicRoles]
SQLServer=YOURMDTSERVER
Database=YourMDTDatabase
StoredProcedure=RetrieveRoles
Parameters=MacAddress
SQLShare=SMSClient
Netlib=DBNMPNTW

As a careful reader you probably recognized, that we have called this section already in the last post :-). But again a sample how to call this new section:

[Settings]
Priority=...,ComputerSpecificRoles, ...,MakeModelSpecificRoles, ...,LocationSpecificRoles, ...,DynamicRoles, ...,RoleSpecificApplications,RoleSpecificPackages, ...

So far so good. We can execute the new stored procedure as part of our Gather step. However it will not give any results as the Mapping table is still empty. We need to add a couple of ARPName - RoleID Value-Pairs to the RoleMapping table. The RoleID will be easy as you can get the value directly from the Deployment Workbench. Though how do we get a list of valid ARPNames? Easiest is probably to just query the same table we used in our Stored Procedure. Just take the following SQL query to search for appropriate ARPName Values on your SMS Server.

SELECT DISTINCT DisplayName0, ProdID0, Publisher0
FROM dbo.v_GS_ADD_REMOVE_PROGRAMS
WHERE (DisplayName0 IS NOT NULL) AND 
      (ProdID0 NOT IN (SELECT DISTINCT ARPName FROM dbo.RoleMapping)) AND
      (DisplayName0 LIKE '%MySuperDuperProgram%')

Change "MySuperDuperProgram" to a software you would like to support with this process like Office 2007, Project, Acrobat or whatever you need. It will produce a distinct list of all available Programs in your environment and exclude automatically all applications which have been used already. The Value you need to use in the RoleMapping Table can be found in the column ProdID0. This will be mainly GUIDs like {91120000-003A-0000-0000-0000000FF1CE} for Microsoft Office Project Standard 2007. Adding the value pairs is probably easiest done by just typing them in by hand from the SQL Server Management Studio. On some sparetime we can also add a Stored Procedure and a Method for our webservice to be used instead. ;-)

After adding the values to the database we are ready to go to use the new capabilities on refresh/reimages. See the next Post how we can extend this to even include the applications from an old computer during a replacement scenario.

Download all files used in this post