There is a little known component of the MDT database that, when used with Configuration Manager, can automatically re-install applications that were previously installed on a device. It is not well known, simply because it isn’t readily visible from the database view within the Deployment Workbench.
I decided to write this entry, to explain the MDT PackageMapping table and RetrievePackages stored procedure.
This process is sometimes, unofficially, referred to as Zero Touch Applications. ZTA refers to the unattended application installation process integrated into Zero Touch Installation. It utilizes the Add/Remove Programs (ARP) data from the ConfigMgr database and maps that to a manually maintained “PackageMapping” table that is hosted on the MDT database server. When the ZTIGather is run, it queries the ConfigMgr database for all application names associated to the MAC address that is passed from the machine. It then attempts to match these to entries in the PackageMapping table in the MDT database. If there are matches, they are then used to populate the PACKAGES variable for use in the Install Software step of the task sequence.
The PackageMapping table maintains a list of mappings. The column ARPName refers to the legacy application and the column Packages refers to the package ID and program name of the new package to be installed. Many of my customers will use this to map a new, Windows 7 remediated application to an old application name, so that when the image is deployed, it will automatically install the new version via a dynamic upgrade.
I should point out that the following uses ConfigMgr 2012, but the process is the same on ConfigMgr 2007, even though the console look and feel may be different.
The configuration of the process is as follows…
First, make sure your package and program exist in ConfigMgr. You will notice I have a package called Project Professional 2010 with a package ID of CO100014. It also has a program named ‘Silent Install Project 2K10.’
Second, identify the name that ConfigMgr recognizes for the application to which you want to map. You can do this by launching SQL Management Studio and running the following query against the ConfigMgr database. Notice the DisplayName0 is ‘Microsoft Office Project Professional 2010’. Also, notice the ProdID0 is a GUID. This will be explained momentarily.
Now you need to modify the PackageMapping table. Notice I have added the previously queried DisplayName0, ‘Microsoft Office Project Professional 2010’ in the ARPName column. I have also added the ConfigMgr package CO100014:Silent Install Project 2K10. The format for the Packages column must be PACKAGEID:Program Name.
Now we need to modify the RetrievePackages stored procedure. This can be found in the Programmability node under the MDT database. Right-click this stored procedure and select Modify.
CREATE PROCEDURE [dbo].[RetrievePackages]
SET NOCOUNT ON
/* Select and return all the appropriate records based on current inventory */
SELECT * FROM PackageMapping
WHERE ARPName IN
SELECT DisplayName0 FROM [HYD-SRV1].CM_CO1.dbo.v_GS_ADD_REMOVE_PROGRAMS1 a,
WHERE a.ResourceID = n.ResourceID AND
MACAddress0 = @MacAddress
Notice I have added [HYD-SRV1].CM_CO1. in front of the two ConfigMgr views that are being referenced in the query. This represents my [SCCM_Server].SCCM_Database. There is also another change I like to make in order to make it easier to identify the applications. The ‘SELECT DisplayName0…’ in the original stored procedure is really ‘SELECT ProdID0…’. Remember when we ran our query earlier, I had you look at the ProdID0. It is a GUID. The DisplayName0 is what shows in the Add/Remove section of the operating system, so it is easier to reference. Once all changes are made, click Execute.
Now that we have done all that, we have to modify the customsettings.ini file so that it will run the stored procedure during the ZTIGather. Here is a sample customsettings.ini file with the changes needed in yellow…
Priority= RetrievePackages, DynamicPackages, Default
When I run my task sequence, the ZTIGather.log now looks like the following…
Notice that the SQL query has returned CO100014:Silent Install Project 2010 and assigned it the PACKAGES variable as PACKAGES001.
My lab environment is Windows 2008 R2 with Configuration Manager 2012 RC1, MDT 2012 Beta 2 and SQL 2008 SP2 CU6 . This process, however, is the same on MDT 2010 Update 1 with SCCM 2007 .
My lab has the MDT and ConfigMgr databases on the same server. If this is not your environment, you will need to setup the ConfigMgr server as a linked server in the SQL Management Studio.
Manipulating the PackageMapping table is manual out of the box. You could also script to pull the ARP data from ConfigMgr and then manually map, or you could produce a front-end application that helps users map the applications.
This post was contributed by Brad Tucker, a Senior Consultant with Microsoft Services, East Region, United States